generate insert statements from table sql server

For example, you might have one million records in your remote source, but only one thousand need to be inserted as new rows to your target table. I would assume that once the initial load is complete, most of the retrieved data will be discarded as duplicate. I suspect that the remote part of the query is retrieving and transferring all selected data (including the presumably large data and image_thumbnail values) from the remote database before checking locally for the existence of a local copy. I need to somehow change this to be able to run at the minimum every 1 minute (if possible). Right now, the stored procedure is running over 1 hour and 22 minutes. Goal: optimize this to run as fast as possible to read the source table and insert 'new' records into the target table using a linked server running across the network. VALUES (SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(),ĮRROR_PROCEDURE(), ERROR_MESSAGE(), GETDATE()) INSERT INTO connect_ (UserName, ErrorNumber, ErrorState, ErrorSeverity,ĮrrorLine, ErrorProcedure, ErrorMsg, ErrorDateTime) 'connect_image Inserted Records', GETDATE()) INSERT INTO connect_ (UserName, ErrorNumber, ErrorState,ĮrrorSeverity, ErrorLine, ErrorProcedure, ErrorMsg, ErrorDateTime) acc_image src ON ass.id = src.acc_slide_idĬonnect_image tgt ON src.acc_image_id = tgt.acc_image_id

acc_slide ass ON s.id = ass.acc_specimen_id acc_specimen s ON a.primary_specimen_id = s.id Src.data, src.created_date, src.row_version, src.sort_order Src.image_color_depth, src.image_thumbnail, Src.data_format, src.label, src.description, Src.acc_image_id, a.id, src.image_type_id, INSERT INTO connect_image (acc_image_id, acc_id, image_type_id,ĭata, created_date, row_version, sort_order) Here's the stored procedure code: ALTER PROCEDURE. I have an inserted_date that I don't know if I can use to optimize: CREATE TABLE. IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, I'm not sure how I should modify the stored procedure to pull data more quickly based on the max(identity).įYI, I can add columns to the target table if it makes sense to add a column to help filter this data further so that it only looks for recent images. There is also an identity column ( acc_image_id) in the source table. I am not sure if I can use the created_date in the source table to filter the data so it doesn't look thru all the data. I've included the source and target table schema so you can see all columns that I have to work with. The business requirements are to run this every minute, but based on the current outcome that is not possible. My goal would be to optimize so that we can run this on a re-occuring schedule. Is there any way to optimize this stored procedure so that it pulls only new records. The job is not being blocked and is still runnable. I've already moved all images over to this table so there are no new images to pull over so I'm assuming it's still reading the table for new records. When I run the stored procedure it is still running over 1 hour and 22 minutes.

The challenge is that the table stores 'images' with over 2 million rows. I created the below stored procedure to pull data from a source table and insert only 'new records' into a destination table.

Generate insert statements from table sql server