Solved

Syntax for Insert Into  using Open Query

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now