Insert Data from a table in a database to another table in another database on same server

Posted by Anonymus under Sql Server on 9/26/2012 | Points: 10 | Views : 1333 | Status : [Member] | Replies : 5
Hi,
Can any one please help me with the syntax to insert data from a table in a database to another table in another database on same server?

I am using following syntax
  
INSERT INTO
DestinationDatabase.DestinationDatabaseTable(
column1,
column2)
SELECT * FROM SourceDatabase.SourceDatabaseTable


Is this syntax correct?? Please help me with this? Can any one give me correct syntax? I am basically writing a Stored Proc which will need this.


Please help.

Thanks

Regards,
Anonymus



Responses

Posted by: Sureshknkt7 on: 9/26/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

This will work for your requirement,


insert into Dhar1.dbo.NewTable select *from DemoDatabase.dbo.OldTable


-->Dhar1 and DemoDatabase are Different Databases

The Above Query work in Sql-Server

Thank You



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

Posted by: Pandians on: 9/26/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
@Anonymus

You have to use THREE part naming convension

DatabaseName.SchemaName.ObjectName

So, You have missed SchemaName.. (Your objects belongs to... i.e: dbo (or) ..)

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Posted by: Kianurieves on: 9/27/2012 [Member] Starter | Points: 25

Up
0
Down
If the two databases are on the same server, you should be able to create a SQL statement something like this:

UPDATE Test1.dbo.Employee
SET DeptID = emp2.DeptID
FROM Test2.dbo.Employee as 'emp2'
WHERE
Test1.dbo.Employee.EmployeeID = emp2.EmployeeID

http://www.dapfor.com/en/net-suite/net-grid/tutorial/currency-converter


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

Posted by: Jayakumars on: 9/27/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

put 2 connection string your webconfig
read 1 connection string retrieve corresponding table with dataset then
pass second dbconnection string pass and execute Query.


Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

Posted by: Jayakumars on: 9/27/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
Please mark as answer if it helpful to you.

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

Login to post response