Click here to Skip to main content
14,696,341 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Can any body help me out how i can update data from one server database table to another server database table using sql server 2008 and through sql queries?
Posted
Updated 11-May-20 9:25am
v2

Hi,

You should define Link Sever before executing query.

Link Server: Configuring Linked Servers[^]

Query:
SELECT *
INTO [TGT_LinkServer].[TGT_DatabaseName].[TGT_TableName]
FROM [SRC_LinkServer].[SRC_DatabaseName].[SRC_TableName]


Please do let me know, if you have any doubt.

Please provide "Vote":thumbsup: if this would be helpful, and make "Accept Answer" if this would be correct answer.:rose:

Thanks,
Imdadhusen
   
Comments
Ealps_grp 17-Nov-10 0:20am
   
it is showing error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'ServerName.TA.canteentxns'.
In addition to Sunasara Imdadhusen's answer you can also use OPENROWSET to fetch data in cross database situation like
SELECT a.* FROM OPENROWSET('MSDASQL.1', 'DRIVER=SQL Server;SERVER=192.168.2.2;UID=sa;PWD=;DATABASE=CBOS',
'SELECT * FROM MASTER') a
   
Comments
Ealps_grp 17-Nov-10 0:24am
   
OLE DB provider "MSDASQL.1" for linked server "(null)" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).".
OLE DB provider "MSDASQL.1" for linked server "(null)" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL.1" for linked server "(null)".
Goutam Patra 17-Nov-10 1:51am
   
Will you care to use your head and some googling? This was a suggestion how you can do it. Just putting the error message shows that you have not worked anything out.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900