How to transfer all the records from a table in one database to another table in another database [Resolved]

Posted by Anu_dgr8 under Sql Server on 7/8/2010 | Views : 1269 | Status : [Member] | Replies : 3
i have 2 databases db1 and db2 and 2 tables inside them tbl1 and tbl2 resp..nw i want to tranfer all the records from tbl1 to tbl2 using SQL Query in SQL SERVER 2005..How do i do this?

Thanks and Regards,
Hoosha Boosha



Responses

Posted by: PandianS on: 7/8/2010 [Member] [MVP] Silver

Up
0
Down

Resolved
Hi

I think the Two databases are there in same Server Instances.

You can do this by "Three part naming convension"

If you have enabled IDENTITY on that tables then. Yo have to consider the following

1. You want to transfer the all column info 'as is' including Identity column.

2. You want to transfer the all column info Except Identity column.

Including Identity:
Use Db2

Go

Set Identity_Insert Tbl2 on
Go

Insert Tbl2(column1,column2,...)
Select column1,column2,... from Db1.dbo.Tbl1
Go

Set Identity_Insert Tbl2 off
Go
Except Identity:
Use Db2

Go

Insert Tbl2(column2,column3,...)
Select column2,column3,... from Db1.dbo.Tbl1
Go
Note: Column1 is the Identity column enabled.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Chaitali on: 7/8/2010 [Member] Starter

Up
0
Down
insert into <new table name>
select * from <old table name>

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 7/8/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Very Good Asnwer from PandianS

he Pointed out the most important in this exercise

 from Db1.dbo.Tbl1



the DB1 is the database and dbo is the user, when you are doing the cross database queries you need it.




Thank you for posting at Dotnetfunda
[Administrator]

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response