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 : 1284 | 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

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

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

Login to post response