原文:
慎用SELECT INTO复制表
准备数据
很多时候我们习惯于用SELECT
INTO复制一个表或表结构，因为它方便，快捷，而且在某些情况下效率比INSERT INTO 效率要高一些。但是要注意： SELECT INTO 复制表或表结构的时候，只是得到了一个“外壳”，就像克隆人一样，只是得到了一个躯体，个人的意识、回忆都不会克隆的。像原表的主键、外键、约束、触发器、索引都不会被复制过来。这点要注意哦，在某些情况下，没有注意的话，会照成“灾难后果的”，下面给个脚本例子，给大家演示下SELECT INTO复制表或表结构时，没有得到原表的主键、外键、约束....
准备数据
USE
[
MyAssistant
]
GO
SET ANSI_NULLS ON
GO
-- 创建
IF OBJECT_ID (N ' Groups ' ) IS NOT NULL
BEGIN
PRINT ' This table have been existed ' ;
DROP TABLE Groups;
END
ELSE
BEGIN
CREATE TABLE [ dbo ] . [ Groups ]
(
[ GroupID ] SMALLINT IDENTITY ( 1 , 1 ),
[ GroupName ] NVARCHAR ( 50 ),
[ Description ] NVARCHAR ( 100 ),
CONSTRAINT [ PK_Groups_GroupID ] PRIMARY KEY (GroupID)
)
END
GO
-- 添加数据
INSERT INTO dbo.Groups
VALUES ( ' SuperAdmin ' , ' 超级管理员 ' );
INSERT INTO dbo.Groups
VALUES ( ' CusServGroup ' , ' 客服部门组 ' );
INSERT INTO dbo.Groups
VALUES ( ' CommonGroup ' , ' 普通部门组 ' );
GO
DROP TABLE dbo.Users
CREATE TABLE [ dbo ] . [ Users ]
(
[ UserId ] BIGINT IDENTITY ( 1 , 1 ) NOT NULL ,
[ UserName ] NVARCHAR ( 25 ) NULL ,
[ PassWord ] NVARCHAR ( 50 ) NULL ,
[ Sex ] BIT NULL ,
[ GroupID ] SMALLINT ,
CONSTRAINT [ PK_Users_UserId ] PRIMARY KEY CLUSTERED ( [ UserId ] ASC ),
CONSTRAINT [ FK_Users_Groups_GroupID ] FOREIGN KEY (GroupID) REFERENCES Groups(GroupID)
)
GO
ALTER TABLE [ dbo ] . [ Users ] ADD CONSTRAINT [ DF_Users_Sex ] DEFAULT (( 0 )) FOR [ Sex ]
GO
CREATE TRIGGER TRG_Users ON dbo.Users
AFTER DELETE
AS
SET IDENTITY_INSERT dbo.Users ON ;
INSERT INTO UserHistory
(UserId, UserName, PassWord, Sex, GroupID)
SELECT * FROM deleted
GO
INSERT INTO dbo.Users( UserName, PassWord, Sex, GroupID )
VALUES ( ' Kerry ' , ' 312ddfjdf ' , 1 , 1 )
INSERT INTO dbo.Users( UserName, PassWord, Sex, GroupID )
VALUES ( ' test ' , ' 312ddfjdf ' , 0 , 3 )
GO
SET ANSI_NULLS ON
GO
-- 创建
IF OBJECT_ID (N ' Groups ' ) IS NOT NULL
BEGIN
PRINT ' This table have been existed ' ;
DROP TABLE Groups;
END
ELSE
BEGIN
CREATE TABLE [ dbo ] . [ Groups ]
(
[ GroupID ] SMALLINT IDENTITY ( 1 , 1 ),
[ GroupName ] NVARCHAR ( 50 ),
[ Description ] NVARCHAR ( 100 ),
CONSTRAINT [ PK_Groups_GroupID ] PRIMARY KEY (GroupID)
)
END
GO
-- 添加数据
INSERT INTO dbo.Groups
VALUES ( ' SuperAdmin ' , ' 超级管理员 ' );
INSERT INTO dbo.Groups
VALUES ( ' CusServGroup ' , ' 客服部门组 ' );
INSERT INTO dbo.Groups
VALUES ( ' CommonGroup ' , ' 普通部门组 ' );
GO
DROP TABLE dbo.Users
CREATE TABLE [ dbo ] . [ Users ]
(
[ UserId ] BIGINT IDENTITY ( 1 , 1 ) NOT NULL ,
[ UserName ] NVARCHAR ( 25 ) NULL ,
[ PassWord ] NVARCHAR ( 50 ) NULL ,
[ Sex ] BIT NULL ,
[ GroupID ] SMALLINT ,
CONSTRAINT [ PK_Users_UserId ] PRIMARY KEY CLUSTERED ( [ UserId ] ASC ),
CONSTRAINT [ FK_Users_Groups_GroupID ] FOREIGN KEY (GroupID) REFERENCES Groups(GroupID)
)
GO
ALTER TABLE [ dbo ] . [ Users ] ADD CONSTRAINT [ DF_Users_Sex ] DEFAULT (( 0 )) FOR [ Sex ]
GO
CREATE TRIGGER TRG_Users ON dbo.Users
AFTER DELETE
AS
SET IDENTITY_INSERT dbo.Users ON ;
INSERT INTO UserHistory
(UserId, UserName, PassWord, Sex, GroupID)
SELECT * FROM deleted
GO
INSERT INTO dbo.Users( UserName, PassWord, Sex, GroupID )
VALUES ( ' Kerry ' , ' 312ddfjdf ' , 1 , 1 )
INSERT INTO dbo.Users( UserName, PassWord, Sex, GroupID )
VALUES ( ' test ' , ' 312ddfjdf ' , 0 , 3 )
我们用下面的语句复制下表Users,我们具体可以从下图中看到表User与TestUser结构的不同了
SELECT
*
INTO
TestUser
FROM
dbo.Users
