Database Design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Database Design

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-31 : 06:23:06

Hi

we have same table in four database but different user_id..like example

Database 1
Customer table Parent Table
Cust_id, Cust_name

child table
Cust_id, Order_id,order_date

In the above tables in four database..

I want to merge in single database for upcoming use

please help me out the best advise for the same....

thanks

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-31 : 06:38:44

Do you need to migrate the data into one database?
Or do you need to view the data together for reports? CREATE VIEW with a UNION from Database1.dbo.CustomerTable, Database2.dbo.CustomerTable

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-31 : 06:42:13

hi

I need to migrate the data into one database. In new database how to handle the master ID's and child table transaction ID's for upcoming use

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-31 : 11:21:40

Hi Woodhouse

do you mean that you have 4 database each with the same schema?

I.E : do you have identical table structure / keys etc?

and that you want to consolidate all the data into 1 database?

What are the keys on the two tables? I'm guessing Cust_Id is the key and the relationship?

If it's an identity column then you can do this:

-- In a database that only you are accessing -- NOT production.

1) Start with an empty table with all your keys defined.

2) Turn ID insert on for the 2 tables.

3) copy everything from database 1 into those tables

4) check the highest Id

5) Copy everything from database 2 into those tables but add the highest Id from (4) + 1 to the cust_Id columns. Example

Say the highest cust_Id from database 1 is 1024. Your INSERTS from database 2 will be [Parent Table].[Cust_Id] + 1025 That way they will be guaranteed unique

6) repeat steps 4 and 5 for database 3

7) repeat steps 4 and 5 for database 4

8) turn Id Insert off again.

----------------------------------
If this isn't what your schema is like please post the keys and relationships.

Some sample data would also be good.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-31 : 11:30:21

Hi

Thanks

If add the value +4 is it ok for Parent tables.

How to handle transaction table this foreign key is there right. might be will get key error.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-31 : 12:03:05

can you confirm that this key is an integer? is it also an identity column?

I'm not sure you understood what I was telling you (your posts are very brief with very little information in them).

quote:

If add the value +4 is it ok for Parent tables.

How to handle transaction table this foreign key is there right. might be will get key error.

For each step in this process you need to add *at least* the highest Id that currently exists. so step by step.

1) Clean database -- no info in those tables
2) INSERT data from your database1 as it is (same key values).
3) Find out what the highest key value is in the database we are writing to. store this as a variable
4) INSERT data from database2. While inserting add the variable to the key value. So for example:

After inserting the first time the highest key might be 231. If we INSERT data from database 2 but add 231 to the keys (both the child and the parent) then we won't get a conflict and because we are adding the same value to both the parent key and the child foreign keys then we know that the relationship will be maintained.

5) Check again for the new highest key value. Store it in a variable

Now the highest key value might be 1004. We use this in the same way as before to get data from database 3. Again because we are adding more than the highest key that currently exists there will be no conflict and the relationships will be maintained.

6) and again for database 4 (the key might be 20000 now or whatever)

============================

Now if there are any other tables that you need to move across and they too have a cust_Id then you will have to do the same for all of them.

============================

If you want more help please post more information -- it's impossible to help you accurately unless you do.

If you have a question -- please provide an example data set. It will make it so much easier to be sure we are talking about the same thing.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-09-01 : 03:36:00

HI

I got ur points..those keys are identity column.

"Can I off the identity seed then i will do the steps what u have mentioned.
then i will on the identity seed..." is it right...

Your advice....please

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-09-01 : 04:25:40

Don't know what you mean by "off the identity seed"

I mean issue the command


SET IDENTITY_INSERT ON

For the table you are going to write to, and when you are finished turn it back OFF.

Check your Help index for SET IDENTITY_INSERT or look up the syntax in books online.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-09-01 : 04:48:23

Hi

If the keys are identity column means
can i do below steps
Step 1
SET IDENTITY_INSERT ON
step 2
Transact Charlie solutions(what u have mentioned above OP)
step 3
SET IDENTITY_INSERT OFF

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-09-01 : 07:19:22

I'm not going to spoon feed you.

Please read about SET IDENTITY_INSERT ON in books online.

Here is a link. you should bookmark this one you will find it very useful,

http://msdn.microsoft.com/en-us/library/ms130214(SQL.90).aspx

The relevant documentation is here:

http://msdn.microsoft.com/en-us/library/ms188059(SQL.90).aspx

You could also find this by going to Help -> index from Management Studio

Good luck, if you have specific questions post them and I'll try and answer.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources