CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
dopplesoldner
dopplesoldner

Reputation: 9479

SQL Server - copy data from staging table

I am using staging tables to perform validation and insert into live.

Suppose I have a table PERSONS

TABLE Persons
(
Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
HouseNumber int,
)

and a STAGING TABLE as follows

TABLE Persons_Staging
(
Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
HouseNumber varchar(255),
)

I need to write a procedure to transfer data from the staging table to the live table while ensuring no duplicates are inserted. How can I achieve that?

Thanks in advance

Upvotes: 5

Views: 7302

Answers (2)

Schuere
Schuere

Reputation: 1649

you could use this with a left outer join on both tables to get all the data that isn't the same. That data you then can insert into your column

INSERT INTO Tab1(front,end,number)
SELECT first,last,nr from tab2 LEFT OUTER JOIN tab1 ON front = first AND last = end AND convert(int,number) = CONVERT(int,nr)
WHERE tab1.ID is null

this could work, on the other hand there are tools made for this kind of stuff

Upvotes: 0

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47038

Use the MERGE command.

Something like this:

MERGE 
    INTO Persons AS TARGET
    USING Persons_Staging AS SOURCE
    ON TARGET.ID = SOURCE.ID
    --WHEN MATCHED
    --    THEN UPDATE???
    WHEN NOT MATCHED BY TARGET
        THEN INSERT (Id , LastName , FirstName, HouseNumber)
    VALUES (SOURCE.Id , SOURCE.LastName , SOURCE.FirstName, SOURCE.HouseNumber)
    -- WHEN NOT MATCHED BY SOURCE
    --    THEN DELETE???
;

If you want to update existing records you uncomment the UPDATE part and add a suitable update clause. The same with the delete part.

Upvotes: 9

Related Questions