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 : 2182 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	@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

0	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

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

0	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

0	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

Latest Posts