尽量不要用select into 复制表

select into 复制表会带来灾难后果，因为只是复制了一个外壳，就像克隆人，有躯体没意识，像原表的主键外键约束触发器索引都不会被复制过来，

创建一个表：
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

SELECT * INTO TestUser FROM dbo.Users

之后会发现TestUser 表这些约束主键触发器等等都没有

巴特西

尽量不要用select into 复制表

最新文章

热门文章