SQL-SERVER - APPEND from different databse's table

[Lachmayya]

Dear All,
I have created two databases in SQL-SERVER 2000 called Shop1 and Shop2.
Both database has the table 'Sales' with fields "Item","Rate"," QTY","Price"
I would like to append data from Shop2 Sales to Shop1 Sales table.

Your suggestions will be highly appreciated.

Thank you.

Lachmayya Siddanmane
Email: lachmayya@yahoo .co.in

[goingpostal]

Why not use Data Transformation Services (DTS)?

[sasime2]

INSERT into table1
SELECT *
from table2

....have all the fields arraigned in a same order for both tables....make sure both tables have the same field names......

[Jim Doherty]

Dear All,
I have created two databases in SQL-SERVER 2000 called Shop1 and Shop2.
Both database has the table 'Sales' with fields "Item","Rate"," QTY","Price"
I would like to append data from Shop2 Sales to Shop1 Sales table.

Your suggestions will be highly appreciated.

Thank you.

Lachmayya Siddanmane
Email: lachmayya@yahoo .co.in

In order to append from one database table to another identical but different database table of the same name and structure you should to reference the database name and owner prefix to the table name as part of the SQL so use this...

INSERT into Shop1.dbo.Sales
SELECT *
from Shop2.dbo.Sales

Regards

Jim :)

[sasime2]

jim is rit..sorry i thought he was askin to append tables in the a same DB

[bxchen]

Try DFSTrans, it can transfer data among different database with option append data. here is the web site: www.DirectFocus Software.com