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?
3条答案
按热度按时间1l5u6lss1#
I believe the syntax is:
bxgwgixi2#
Did you try:
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:
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.