TSQL新增欄位插入資料表

2014-04-24

5135
0
[SQL]小筆記
2014-05-13

摘要:TSQL新增欄位插入資料表

你知道我們在SSMS中用UI介面新增一個欄位到Table時，SQL做了哪一些事呢？

如下面Script可已瞭解，基本上SQL步驟如下：

（1）建立一個新資料表（新Schema）。

（2）將舊資料表資料Insert到新資料表內。

（3）刪除舊資料表。

（4）將新資料表更名成舊資料表。

（5）建立索引。

所以如果您只是要將新欄位加到資料表『後面』 ，那就千萬別用UI做，直接Alter Table來做。

如果您的資料欄位一定要介於某欄位跟某欄位之間，那就無法用Alter Table來做了。

Alter Table [tablename] Add [NewClo1] Char(1),[NewCol2] Char(10)

如果用UI做，整個流程的Script如下：

/* 為了避免任何可能發生資料遺失的問題，您應該先詳細檢視此指令碼，然後才能在資料庫設計工具環境以外的位置執行。*/
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
CREATE TABLE dbo.Tmp_indextest
	(
	id int NOT NULL IDENTITY (1, 1),
	id1 int NULL,
	id2 int NULL,
	aa nchar(10) NULL,
	name nchar(100) NULL,
	name2 nchar(100) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_indextest SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_indextest ON
GO
IF EXISTS(SELECT * FROM dbo.indextest)
	 EXEC('INSERT INTO dbo.Tmp_indextest (id, id1, id2, name, name2)
		SELECT id, id1, id2, name, name2 FROM dbo.indextest WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_indextest OFF
GO
DROP TABLE dbo.indextest
GO
EXECUTE sp_rename N'dbo.Tmp_indextest', N'indextest', 'OBJECT' 
GO
CREATE UNIQUE CLUSTERED INDEX Cluster_index ON dbo.indextest
	(
	id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX NonCluster_index ON dbo.indextest
	(
	id
	) INCLUDE (id1, id2) 
 WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT

但我想簡化一下流程，因此自己寫一個Script（如下），用Select Into來建立新資料表，基本上可以更快且不會產生

Transation Log，以下作法只適用單純的資料表，如果您的資料表有用Trigger或Replication等其他功能，就得小心一點。

SET XACT_ABORT ON;
BEGIN TRAN
BEGIN TRY
	SELECT 
		id,
		Cast('' as nchar(20)) AS NewCol1,--加入的新欄位1
		Cast('' as char(20)) AS NewCol2,--加入的新欄位2
		id1,id2,name,name2 
	INTO DesTable 
	FROM SourceTable WITH (HOLDLOCK TABLOCKX);
	
	EXEC sp_rename N'SourceTable',N'SourceTable_old';
	EXEC sp_rename N'DesTable',N'SourceTable';
END TRY
BEGIN CATCH
	ROLLBACK;
	PRINT 'PROCESS FAIL!';
END CATCH 
IF @@TRANCOUNT > 0 COMMIT;

--完成後！注意權限設定及索引建立，都測試OK後Drop掉舊Table