BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.shweituob
DROP CONSTRAINT DF__shweituob__price__7CDA51C0
GO
ALTER TABLE dbo.shweituob
DROP CONSTRAINT DF__shweituob__qty__7DCE75F9
GO
CREATE TABLE dbo.Tmp_shweituob
(
id numeric(8, 0) NOT NULL,
yhdm char(1) NOT NULL,
rec_num numeric(8, 0) NOT NULL,
[date] char(8) NOT NULL,
[time] char(8) NOT NULL,
reff char(10) NOT NULL,
acc char(10) NOT NULL,
stock char(6) NOT NULL,
bs char(1) NOT NULL,
price numeric(8, 3) NOT NULL,
qty numeric(8, 0) NOT NULL,
status char(1) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_shweituob ADD CONSTRAINT
DF__shweituob__price__7CDA51C0 DEFAULT (0) FOR price
GO
ALTER TABLE dbo.Tmp_shweituob ADD CONSTRAINT
DF__shweituob__qty__7DCE75F9 DEFAULT (0) FOR qty
GO
IF EXISTS(SELECT * FROM dbo.shweituob)
EXEC('INSERT INTO dbo.Tmp_shweituob (id, yhdm, rec_num, [date], [time], reff, acc, stock, bs, price, qty, status)
SELECT id, yhdm, rec_num, [date], [time], reff, acc, stock, bs, price, qty, status FROM dbo.shweituob TABLOCKX')
GO
DROP TABLE dbo.shweituob
GO
EXECUTE sp_rename N'dbo.Tmp_shweituob', N'shweituob', 'OBJECT'
GO
CREATE NONCLUSTERED INDEX shweituob_index ON dbo.shweituob
(
yhdm,
rec_num
) ON [PRIMARY]
GO
COMMIT
这个是sql server 自己修改identity的语法!你好好看看!也许有用
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.shweituob
DROP CONSTRAINT DF__shweituob__price__7CDA51C0
GO
ALTER TABLE dbo.shweituob
DROP CONSTRAINT DF__shweituob__qty__7DCE75F9
GO
CREATE TABLE dbo.Tmp_shweituob
(
id numeric(8, 0) NOT NULL,
yhdm char(1) NOT NULL,
rec_num numeric(8, 0) NOT NULL,
[date] char(8) NOT NULL,
[time] char(8) NOT NULL,
reff char(10) NOT NULL,
acc char(10) NOT NULL,
stock char(6) NOT NULL,
bs char(1) NOT NULL,
price numeric(8, 3) NOT NULL,
qty numeric(8, 0) NOT NULL,
status char(1) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_shweituob ADD CONSTRAINT
DF__shweituob__price__7CDA51C0 DEFAULT (0) FOR price
GO
ALTER TABLE dbo.Tmp_shweituob ADD CONSTRAINT
DF__shweituob__qty__7DCE75F9 DEFAULT (0) FOR qty
GO
IF EXISTS(SELECT * FROM dbo.shweituob)
EXEC('INSERT INTO dbo.Tmp_shweituob (id, yhdm, rec_num, [date], [time], reff, acc, stock, bs, price, qty, status)
SELECT id, yhdm, rec_num, [date], [time], reff, acc, stock, bs, price, qty, status FROM dbo.shweituob TABLOCKX')
GO
DROP TABLE dbo.shweituob
GO
EXECUTE sp_rename N'dbo.Tmp_shweituob', N'shweituob', 'OBJECT'
GO
CREATE NONCLUSTERED INDEX shweituob_index ON dbo.shweituob
(
yhdm,
rec_num
) ON [PRIMARY]
GO
COMMIT
这个是sql server 自己修改identity的语法!你好好看看!也许有用
解决方案 »
- 自定义函数
- 返回行
- about Time(续3)
- Java 通过 JDBC 分别在混合验证模式和 Windows 身份验证模式下连接 SQL Server 2008 或 2005 的连接实例
- 请哥哥们帮我看看一句简单的SQL语句,错在哪里
- sql server2000数据表某一字段含有相同值,请问怎样找出它们?
- 大规模的数据,如何快速的写进数据库??急!!
- 多个客户端能否同时用相同的用户名和密码登陆sql server2000(混合模式)?
- 小问题,解决马上给分!
- 求救 谁装过D版的 ms sql server7.0 ??急急急!
- WEBDB中如何实现文件上传,写入磁盘,不是数据库?
- 如何判断sql server一数据库已经存在?
一个例子:
-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO-- Create a gap in the identity values.
DELETE products
WHERE product = 'saw'
GOSELECT *
FROM products
GO-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GOSELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO
如果你想修改的是字段的属性,使用alter语句即可