将数据从一个数据库复制到另一个 SQL 代码

发布于 2024-11-01 02:47:41 字数 835 浏览 3 评论 0原文

我正在寻求帮助；情况是我有 2 个数据库，它们的结构应该相同，除了新的 prod 数据库具有 ident 列、定义的 PK 和 fk 约束之外。我必须将 4 个略有不同的数据库合并到一个“TempDB”中，然后将数据复制到“NewProddb”中。

我管理了一个脚本 l 来关闭 FK 检查，然后打开 FK 检查，因此插入需要看起来像这样；

[禁用所有 FK 约束的脚本（nocheck）]

SET IDENTITY_INSERT [NewProdDB].[dbo].[Event] ON; 
INSERT INTO [dbo].[Event] 
(EventID, Name, StartDate, EndDate, PartnerRegStartDate, PartnerRegEndDate, HouseholdRegStartDate, HouseholdRegEndDate, ChannelId, HasTeam, MaxteamMembers) 

(Select 
EventID, Name, StartDate, EndDate, PartnerRegStartDate, PartnerRegEndDate, HouseholdRegStartDate, HouseholdRegEndDate, ChannelId, HasTeam, MaxteamMembers
From TempDB.dbo.Event);

SET IDENTITY_INSERT [NewProdDb].dbo.[Event] OFF;

[启用所有 FK 约束的脚本（检查）]

我想要做的是一次性编写此脚本，而不是单独为所有表编写脚本。所有列中的所有数据都将被复制。

我希望这一点很清楚。

提前致谢。

原文

I am looking for help;
Situation is that I have 2 databases which should be identical in structure with the exception that the NEW prod dbs has ident columns, PK and fk contraints defined. I have to consolidate 4 slightly different dbs in one 'TempDB' then copy the data into the 'NewProddb'.

I have managed a script lto turn off FK checks, and turn on FK checks, so the insert needs to look something like this;

[Script to disable all FK Constraints (nocheck)]

SET IDENTITY_INSERT [NewProdDB].[dbo].[Event] ON; 
INSERT INTO [dbo].[Event] 
(EventID, Name, StartDate, EndDate, PartnerRegStartDate, PartnerRegEndDate, HouseholdRegStartDate, HouseholdRegEndDate, ChannelId, HasTeam, MaxteamMembers) 

(Select 
EventID, Name, StartDate, EndDate, PartnerRegStartDate, PartnerRegEndDate, HouseholdRegStartDate, HouseholdRegEndDate, ChannelId, HasTeam, MaxteamMembers
From TempDB.dbo.Event);

SET IDENTITY_INSERT [NewProdDb].dbo.[Event] OFF;

[Script to enable all FK Constraints (check)]

What I want to do is to script this in one go rather than writing a script for ALL the tables separately. ALl Data in All Columns is to be copied.

I hope this is clear.

Thanks in Advance.

分享到QQ

分享到微博