SQL Server同一服務(wù)器不同數(shù)據(jù)庫間表的復(fù)制

1、在源數(shù)據(jù)庫上生成創(chuàng)建表的腳本。

2、在目標(biāo)數(shù)據(jù)庫上執(zhí)行sql，創(chuàng)建表

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、在目標(biāo)數(shù)據(jù)庫上設(shè)置主鍵可以指定

SET IDENTITY_INSERT zs_backup.dbo.elite_sample ON;

4、往目標(biāo)數(shù)據(jù)庫表插入數(shù)據(jù)

insert into zs_backup.dbo.elite_sample(id,

members_id,

verify_status) select id,

members_id,

verify_status from zs.dbo.elite_sample

5、重新設(shè)置主鍵不可以指定

SET IDENTITY_INSERT zs_backup.dbo.elite_sample OFF;