Syntax for Insert Into using Open Query

Hi,
i have a table called OldTable in my SqL Server, but i would like to append a new data that is sitting in another server into the OldTable.  How would i do that using open query method? Here is my situation:

OldTable:- this is the table that i would like to add some more or new data and it is my current server

NewTable:- this is a linked server and i want to bring the data in this table into the OldTable
thanks
karinos57Asked:
Who is Participating?
 
JestersGrindConnect With a Mentor Commented:
You have to create a linked server in your SQL Server that points to the other SQL Server.  Then you would run this.

INSERT INTO OldTable (col1, col2, etc...)
SELECT *
FROM OPENQUERY(YourLinkedServerName, 'SELECT Col1, Col2, etc... FROM RemoteTableName')

Greg

0
 
karinos57Author Commented:
thnx
0
 
lcohanDatabase AnalystCommented:
if the two servers are both SQL you don't necessarily need the OPENQUERY - you need that only if you define a linked server based on a ODBC which would be a waste if bothe are SQL.
Assuming you create a linked server using "Microsoft OLEDB provider for SQL Server" just use a full name qualifier like below:

--run on the target server

insert into OldTable select * from YourLinkedServerName.YourDBname.schema_name.NewTable

this assuming they have both SAME structure - if not just provide column list for both
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.