SQL Server Using OpenQuery With INSERT INTO Statement

zzzyeukh  于 2023-10-15  发布在  其他
关注(0)|答案(3)|浏览(133)

I currently use the following to data warehouse a table everday:

DROP TABLE mytable 

SELECT firstcolumn, secondcolumn INTO mytable
FROM OPENQUERY (mylinkedserver, 'SELECT firstcolumn, secondcolumn FROM linkedtable')

I would like to start using TRUNCATE going forward (to perserve system resources and indexes):

TRUNCATE TABLE mytable

INSERT INTO mytable (firstcolumn, secondcolumn)
?

How do I use OpenQuery with the INSERT INTO statement?

1l5u6lss

1l5u6lss1#

I believe the syntax is:

TRUNCATE TABLE mytable

INSERT INTO mytable (firstcolumn, secondcolumn)
SELECT firstcolumn, secondcolumn 
FROM OPENQUERY (mylinkedserver, 'SELECT firstcolumn, secondcolumn FROM linkedtable')
bxgwgixi

bxgwgixi2#

Did you try:

INSERT INTO mytable(firstcolumn, secondcolumn)
  SELECT firstcolumn, secondcolumn
  FROM OPENQUERY
  (
    mylinkedserver, 
    'SELECT firstcolumn, secondcolumn FROM linkedtable'
  );
8fsztsew

8fsztsew3#

SELECT INTO by OPENQUERY seems to require non-existing table. An alternative is to use INSERT INTO which uses existing table but this one will also either dump the records over and over again (if there is no unique constraints on the destination table) or simply refuse to copy any record if any duplicate is detected.

But there is hope. If your objective is to update the table with only new records, the truncate may be an overkill. So, just treat this OPENQUERY as normal SQL by checking on the duplicate using WHERE clause. Something like this will do:

INSERT INTO mytable   
SELECT * from OPENQUERY(mylinkedserver, 'SELECT * FROM mytable_remote' ) rt 
WHERE NOT EXISTS 
(
SELECT 1 FROM mytable WHERE ID = rt.ID  
)

We assumed that the constraint, non-repeating check, is on ID; select all is also used. Also the ODBC Drivers and linked-server setups are presumed ready.

This should help with any such situation requiring updates of SQL Server table from remote data source. This can be set as a stored procedure and executed periodically.

相关问题