April 12, 2011 at 5:55 pm
Hi all,
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.
Roger
April 12, 2011 at 6:34 pm
Are you going to want reapply those constraints? Because you won't be able to, and your data will all kinds of mixed up. Unless you know there isn't a possibility of duplicating a key value.
You might want to look at SQL Compare and SQL Data Compare from RedGate.
just my 1.968 cents worth ...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 12, 2011 at 6:38 pm
Hi there,
Thanks for your input.
Yes i will be reapplying the the constraints, and there will be NO duplicating of primary keys, i am scripting the imports to ensure this doesnt happen.
Thanks for checking.
Roger
(I will have a look at these tools but currently dont have them, they sound useful).
April 12, 2011 at 6:44 pm
http://www.red-gate.com/products/
These tools are the best! They've saved me so much time over the years it's not even funny. And before you ask, I don't work for them. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply