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?
I believe the syntax is:
TRUNCATE TABLE mytable
INSERT INTO mytable (firstcolumn, secondcolumn)
SELECT firstcolumn, secondcolumn
FROM OPENQUERY (mylinkedserver, 'SELECT firstcolumn, secondcolumn FROM linkedtable')
Did you try:
INSERT INTO mytable(firstcolumn, secondcolumn)
SELECT firstcolumn, secondcolumn
FROM OPENQUERY
(
mylinkedserver,
'SELECT firstcolumn, secondcolumn FROM linkedtable'
);
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.
