May 1, 2008 at 9:20 am
Hi. I am sure this is a common issue. I have 2 Databases. Database A (test1) contains standing data I would like to migrate to Database B (test2). First I script the Parent Table 'To INSERT' , modify then run (example below):
INSERT INTO [test2].[dbo].[TableName]
([Value1]
,[Value2]
,[Value3])
SELECT
Value1,
Value2,
Value3
FROM [test1].[dbo].[TableName]
My issue is I do not know how to write a query that will migrate the data from the child table?
Example:
dbo.MyTable contains a field AccountType.
When I migrate the Parent Table 'dbo.AccountType' the PK for AccountType that is referenced as a FK in dbo.MyTable may be different....so I can not run the 'INSERT TO' query from the child table in database1 to the child table in database2 and ensure the integrity of the data (in fact in some instances the FK will not exist!
Am I making sense?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 1, 2008 at 9:40 am
Are you using an Identity Column as your primary key in each database?
If you are you can use Set Identity_Insert [table_name] On and keep the same key values. Then you can do the Insert Into for all your tables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2008 at 12:21 pm
Hi once again Jack. Yes the SQL Identity Key is the PK in Parent Table & the FK in the Child Table.
So....Database1
Parent Table
AccountType (PK)
1234
1235
Child Table
TCode (PK)...........Description..........AccountType(FK)
1........................Bibble.................1234
2........................Bobble................1235
So run INSERT query for the Parent table from Database1 into Database2. What would the query look like for the INSERT query for the Child table to keep the same Identity?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 1, 2008 at 12:30 pm
Phil,
Your code should look something like this:
[font="Courier New"]SET IDENTITY_INSERT db2.dbo.AccountTypes ON
INSERT INTO db2.AccountTypes
(
id, -- this would be the PK identity field
TYPE,
...
)
SELECT
id,
TYPE,
...
FROM
db1.dbo.AccountTypes
SET IDENTITY_INSERT db2.dbo.AccountTypes OFF
SET IDENTITY_INSERT db2.dbo.Accounts ON
INSERT INTO db2.Accounts
(
id, -- this would be the PK identity field
type_id, -- FK to AccountTypes
...
)
SELECT
id,
type_id,
...
FROM
db1.dbo.Accounts
SET IDENTITY_INSERT db2.dbo.Accounts OFF
[/font]
You would repeat for each table you need. You can also use SSIS and in the Destination you can set it to Fast Load and check the "Keep Identity" Box
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2008 at 3:24 pm
Thanks Jack, I will give it a whirl.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply