Search code examples
ssissql-insertsql-server-2016

How to insert rows into a SQL table with a SELECT NOT EXIST statement from one server to another?

My goal is to look on ID column in TableA -> ServerA and then if there are any new records that program could find in TableB -> ServerB that are not in TableA -> ServerA, insert those into TableA -> ServerA.

I have initialized an SSIS package to make this happen and then I'm planning to schedule that SSIS package with SQL Server Agent to run every one hour.

Now as a challenge, moving data between two servers, I had to create a linked server and then I was able to make a Select query to select all the data that are newly inserted into the table we're monitoring. I have trouble inserting those multiple rows into the table now.

Here is my SQL statement connected with Linked Server to get all the newly added values;

SELECT ID, trx_date, work_order, Department, work_center, operation_no, operator, total_labor_hrs, job_start, job_end, qty_ordered, qty_produced, item_no, lot_no, default_bin, posted, wrapped, total_shift_hrs, check_emp, machine, operation_complete
FROM STLEDGSQL01.MES_DEV.dbo.wrap_labor AS SRC
WHERE (NOT EXISTS (SELECT ID, trx_date, work_order, Department, work_center, operation_no, operator, total_labor_hrs, job_start, job_end, qty_ordered, qty_produced, item_no, lot_no, default_bin, posted, wrapped, total_shift_hrs, check_emp, machine, operation_complete
FROM wrap_labor_SSIS AS TGT WHERE (TGT.ID = SRC.ID)))

Following is my SSIS package and how I'm planning to run the Execute SQL Task function enter image description here

Could someone help me here to get the SQL Query to INSERT those records which I'm SELECTING above?

I've tried the following with no luck;

INSERT INTO [MACOLA_TABLES_SQL01].[dbo].[wrap_labor_SSIS] VALUES (
SELECT ID, trx_date, work_order, Department, work_center, operation_no, operator, total_labor_hrs, job_start, job_end, qty_ordered, qty_produced, item_no, lot_no, default_bin, posted, wrapped, total_shift_hrs, check_emp, machine, operation_complete
FROM STLEDGSQL01.MES_DEV.dbo.wrap_labor AS SRC
WHERE (NOT EXISTS (SELECT ID, trx_date, work_order, Department, work_center, operation_no, operator, total_labor_hrs, job_start, job_end, qty_ordered, qty_produced, item_no, lot_no, default_bin, posted, wrapped, total_shift_hrs, check_emp, machine, operation_complete
FROM wrap_labor_SSIS AS TGT WHERE (TGT.ID = SRC.ID))))

I always try to make a dbfiddle for clear understanding of the SQL tables but in this situation with two servers it's not easy task for me to create one, but I will answer any questions/clarifications anyone need.

Solution

  • I've done this before and the MERGE command works perfectly.

    Basically:

    MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

    There are lots of good tutorials on it's usage online, i.e. https://www.sqlservertutorial.net/sql-server-basics/sql-server-merge/