Solved

Syntax for Insert Into  using Open Query

Posted on 2011-09-08
3
291 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:karinos57
3 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 500 total points
ID: 36506065
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
 

Author Closing Comment

by:karinos57
ID: 36506175
thnx
0
 
LVL 39

Expert Comment

by:lcohan
ID: 36506182
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question