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 : 1288 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

Latest Posts