CODEWITHSUNDEEP
sqlsql-serversql-server-2012
amateur
amateur

Reputation: 44605

copy data between sql tables

I am working with sql server 2012 and have a database.

In this database, I have 2 tables. 1 table the backend to real time data. The other the exact same structure but a staging table - no real time system working off it.

I want to write a sql query to be included in a stored procedure/sql job that runs against the staging table and update/insert data in to the real time table. If the record exist in the real time table, update it, if not insert in to it. As the staging table could have ~100,000 when it needs to run each day - I am concerned about performance, in particular the possibility of the real time table being locked. There is a possibility that the real time table is being updated from another source while the query is running, so locking at the row level may be required.

I have outlined the process and my concern so I would like assistance in writing the query. I need to not impact the performance of the real time table, but unsure no other system updates the row for each row as the data is being transferred. How could I do this with sql?

Upvotes: 0

Views: 217

Answers (1)

Jason Carter
Jason Carter

Reputation: 925

You want a merge: 

merge into Emp2 as Target
    using emp as Source on Target.FirstName=Source.Firstname and Target.LastName=Source.lastName
    when matched then 
        update set Target.FirstName=Source.FirstName,
                        Target.LastName = Source.LastName
    when not matched then
        insert (FirstName,LastName, LastTraining) values (Source.FirstName,Source.LastName,Source.LastTraining);

Upvotes: 1

Related Questions