Using OpenQuery With INSERT INTO Statement

Question

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?

Solution 1

I believe the syntax is:

TRUNCATE TABLE mytable

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

Solution 2

Did you try:

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

Comments

[Lloyd Banks]

I ended up using SELECT * FROM OPENQUERY (mylinkedserver, 'SELECT firstcolumn, secondcolumn FROM linkedtable')