先 ALTER TABLE t1 ADD t1_100 int null 然后再update数据就行了
为了保险起见, 还是删除后创建比较好, 1.启动一个事务, 2.先创建一个临时表,该表中有你所有的栏位和约束, 3.然后将原来表的数据导入, 4.删除原来的表, 5.重命名临时表. 如下是2005产生的标准脚本, 在A表中添加栏位 tt. /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON 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 GO ALTER TABLE dbo.A DROP CONSTRAINT FK_A_B GO COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_A ( linId int NOT NULL, type varchar(10) NULL, port varchar(4) NULL, date datetime NULL, tt nchar(10) NULL, -- 新添加的, price money NULL ) ON [PRIMARY] GO IF EXISTS(SELECT * FROM dbo.A) EXEC('INSERT INTO dbo.Tmp_A (linId, type, port, date, price) SELECT linId, type, port, date, price FROM dbo.A WITH (HOLDLOCK TABLOCKX)') GO DROP TABLE dbo.A GO EXECUTE sp_rename N'dbo.Tmp_A', N'A', 'OBJECT' GO ALTER TABLE dbo.A ADD CONSTRAINT PK_A PRIMARY KEY CLUSTERED ( linId ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO ALTER TABLE dbo.A ADD CONSTRAINT FK_A_B FOREIGN KEY ( linId ) REFERENCES dbo.B ( id ) ON UPDATE NO ACTION ON DELETE NO ACTION
GO COMMIT
alter table T add newCOlumn int --新增列 go update t set newColumn=1--更新新列
然后 不就可以 update ?
ALTER TABLE t1 ADD t1_100 int null
然后再update数据就行了
还是删除后创建比较好,
1.启动一个事务,
2.先创建一个临时表,该表中有你所有的栏位和约束,
3.然后将原来表的数据导入,
4.删除原来的表,
5.重命名临时表.
如下是2005产生的标准脚本,
在A表中添加栏位 tt.
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
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
GO
ALTER TABLE dbo.A
DROP CONSTRAINT FK_A_B
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_A
(
linId int NOT NULL,
type varchar(10) NULL,
port varchar(4) NULL,
date datetime NULL,
tt nchar(10) NULL, -- 新添加的,
price money NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.A)
EXEC('INSERT INTO dbo.Tmp_A (linId, type, port, date, price)
SELECT linId, type, port, date, price FROM dbo.A WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.A
GO
EXECUTE sp_rename N'dbo.Tmp_A', N'A', 'OBJECT'
GO
ALTER TABLE dbo.A ADD CONSTRAINT
PK_A PRIMARY KEY CLUSTERED
(
linId
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO
ALTER TABLE dbo.A ADD CONSTRAINT
FK_A_B FOREIGN KEY
(
linId
) REFERENCES dbo.B
(
id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
COMMIT
go
update t
set newColumn=1--更新新列