SQL Server同一服务器不同数据库间表的复制

1、在源数据库上生成创建表的脚本。

2、在目标数据库上执行sql，创建表

CREATE TABLE [zs_backup]. [dbo].[elite_sample](

[id] [bigint] IDENTITY(10,1) NOT NULL,

[members_id] [bigint] NULL CONSTRAINT [DF__elite_sam__membe__483BA0F8] DEFAULT (NULL),

[verify_status] [int] NULL CONSTRAINT [DF__elite_sam__verif__492FC531] DEFAULT ((0)),

CONSTRAINT [PK_elite_sample_id] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

3、在目标数据库上设置主键可以指定

SET IDENTITY_INSERT zs_backup.dbo.elite_sample ON;

4、往目标数据库表插入数据

insert into zs_backup.dbo.elite_sample(id,

members_id,

verify_status) select id,

members_id,

verify_status from zs.dbo.elite_sample

5、重新设置主键不可以指定

SET IDENTITY_INSERT zs_backup.dbo.elite_sample OFF;