Solved

Syntax for Insert Into  using Open Query

Posted on 2011-09-08
3
283 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using MERGE to UPDATE a third table 14 37
Help  needed 3 23
Query still returning duplicates 5 27
Sql server function help 15 28
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

785 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