migrate tables from multiple database into one table

Question

migrate tables from multiple database into one table

shwetha004

SSC Enthusiast

Points: 153
More actions
February 4, 2008 at 1:38 am

#202995

hi all,
i am very new to SQL Server, and i am using both SQL2000 and 2005.i am attempting to integrate redundant tables from multiple databases into one table in another database.so,i have tableA in databaseA,which has say 5 fields.and then i have tableB in databaseB,which has around 8 columns.some of these columns are the same.So i want to merge these two into tableC in databaseC..DO i need to painstakingly recreate the tables in databaseC?or is there a way to do it in SQL2000?
i have searched the internet about this,but i cant seem to get any sensible solutions.maybe im looking at the wrong place.if someone could point me in the right direction,i would be very happy..
thanks alot!:)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

The long answer is: it depends.

Let's assume that the 8 column table is supposed to be retained in it's entirety and that you're supposed to create that, load it, and then load the 5 column table.

SELECT Col1

,Col2

,Col3

,Col4

,Col5

,Col6

,Col7

,Col8

INTO MyNewTable

FROM db1.dbo.OldTable1

INSERT MyNewTable (

Col1

,Col3

,Col4

,Col6

,Col8)

SELECT Col1

,Col2

,Col3

,Col4

,Col5

FROM db2.dbo.OldTable2

That doesn't create primary keys and foreign keys, indexes, all the rest, but it is a way to avoid typing out the column definitions.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Hi

You can script the 8 column table and then insert the data from the two tables. By scripting the table atleast you get the primary key, indexes of the 8 column table. You will have to create other indexes if required.

Can u tell us why you want to do this especially since the 2 redundant tables in lie in 2 diff databases ?

"Keep Trying"

shwetha004 SSC Enthusiast Points: 153 More actions · Answer 3

hi people!!

thank you so much for your replies..

i will try out the methods u suggested.Actually,i am suuposed to be integrating a few tools into one.All the tools have a general table,which are more or less the same structure.Since i am integrating the tool, it makes sense to combine all the general tables also, and place them in one database.In that way, i dont have to connect to so many databases.

thank you!

shwetha004 SSC Enthusiast Points: 153 More actions · Answer 4

hi ppl!!

i tried using the select into statement.however, i cant see how i can actually merge two columns from two tables into one.

maybe i shud explain further.

Table1 has (userid,username,password,email,status)

Table2 has (userid,name,enable,comment)

the userid and username data the tables are different.and the status and enable columns actually mean the same thing.so i want to create a new table with this structure:

Table3(userid,name,password,email,status,comment).

but i also want to retain the existing data.so some of the entries in Table3 might have null values,but thats okay.

any suggestions?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

shwetha004 (2/13/2008)
hi ppl!!
i tried using the select into statement.however, i cant see how i can actually merge two columns from two tables into one.
maybe i shud explain further.
Table1 has (userid,username,password,email,status)
Table2 has (userid,name,enable,comment)
the userid and username data the tables are different.and the status and enable columns actually mean the same thing.so i want to create a new table with this structure:
Table3(userid,name,password,email,status,comment).
but i also want to retain the existing data.so some of the entries in Table3 might have null values,but thats okay.
any suggestions?

Something along these lines should work (I didn't test it, so typos are possible):

INSERT INTO Table3

(userid

,name

,password

,email

,status

,comment)

SELECT userid

,username

,password

,email

,status

,NULL AS COMMENT

FROM Table1

UNION ALL

SELECT userid

,name AS username

,NULL as password

,NULL as email

,enable AS status

,comment

FROM Table2

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

shwetha004 SSC Enthusiast Points: 153 More actions · Answer 6

hi,

i tried your suggestion.i can parse it without any error.but when i tried to run it,i get:"String or binary data would be truncated.

The statement has been terminated."

seem to me dat there might be sum data loss if i continue.but i have checked that the columns match...here is my code

INSERT INTO table3

(Name,userid,password,email,status,comment)

SELECT username,userid,pwd ,email ,status ,NULL AS COMMENT

FROM table2

UNION ALL

SELECT name AS name ,userid ,NULL as password ,NULL as email ,enable AS status,comment

FROM table1

any suggestions?

Smithy SSC Enthusiast Points: 150 More actions · Answer 7

Make sure that your datatypes match up. For example if you try to put a column that is varchar(100) into a column that is varchar(50) some data may get truncated.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Yeah, what he says.

I know you're just learning this stuff. When you get an error, look it up in the Books Online. It'll mostly tell you what's going on. In this case, as described, you're trying to move more data into less space.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

shwetha004 SSC Enthusiast Points: 153 More actions · Answer 9

hi every one,

thanks for ur replies..i double checked my sql statement and executed it again..it worked!!

thanks so much:-)

shwetha004 SSC Enthusiast Points: 153 More actions · Answer 10

hi evryone..

i manage to execute the sql query, and got a 3rd table which merged all the data from table 1 and 2.however, when i randomly checked the data in table 3,i found out that only data from table1 has been inserted,while data from table 2 is null.and the freaky part is,only the userid column is affected.all other columns from table 2 were transferred correctly.and the userid column just has 'dbo' in it.

here is my query:

INSERT INTO table3(ID,UserID,event,message,TimeStamp)

SELECT 0 as ID,userid as UserID,event, message,log_date as TimeStamp

FROM table1

UNION ALL

Select ID,user as UserID,NULL as event,NULL as message, TimeStamp

FROM table2.

maybe there is some setting i need to change?

:unsure:

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 11

The sample query you're showing looks correct. Verify the data in the original columns. Double-check your actual query (I'm assuming you're posting a sample and you have a real query that you're running) to be sure something isn't up. Also, you don't have any constraints or triggers on the new table do you?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning