信息服务平台怎么赚钱,seo深度优化服务,织梦dedecms导航网站源码,app定制很多时候我们习惯于用SELECTINTO 复制一个表或表结构#xff0c;因为它方便#xff0c;快捷#xff0c;而且在某些情况下效率比INSERT INTO 效率要高一些。但是要注意#xff1a; SELECT INTO 复制表或表结构的时候#xff0c;只是得到了一个“外壳”#xff0c;就像克隆…很多时候我们习惯于用SELECT INTO 复制一个表或表结构因为它方便快捷而且在某些情况下效率比INSERT INTO 效率要高一些。但是要注意 SELECT INTO 复制表或表结构的时候只是得到了一个“外壳”就像克隆人一样只是得到了一个躯体个人的意识、回忆都不会克隆的。像原表的主键、外键、约束、触发 器、索引都不会被复制过来。这点要注意哦在某些情况下没有注意的话会照成“灾难后果的”下面给个脚本例子给大家演示下SELECT INTO复制表或表结构时没有得到原表的主键、外键、约束....准备数据Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--USE [MyAssistant]
GOSET ANSI_NULLS ON
GO--创建
IF OBJECT_ID(NGroups) IS NOT NULLBEGINPRINT This table have been existed;DROP TABLE Groups;END
ELSEBEGINCREATE 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, 普通部门组);
GODROP 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)
)
GOALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_Sex] DEFAULT ((0)) FOR [Sex]
GOCREATE 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
GOINSERT 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