Update and insert via temp table

Question

I am trying to update multiple records at once using a stored procedure, and if that record is not in the table, it has to insert this as new record. I got Id as the primary key on the table. Method I tried below is first inserting data in to a temp table and then updating the original table.I used a sample stored procedure from similar post to create this, as I am bit new to writing stored procedures, I am getting errors on the stored procedure. Please help

CREATE PROCEDURE [dbo].[xxxxxx]

DECLARE @Tbl  TABLE(
Id [int] NOT NULL PRIMARY KEY,
Payroll_Id [int],
ProductCode  nvarchar(255),
Description nvarchar (255),
Qty  nvarchar(255))

BEGIN


INSERT  INTO @Tb1 ([Payroll_Id],[ProductCode],[Description],[Qty])
 Select  @Paroll_Id as [Paroll_Id],@ProductCode as [ProductCode],@Description as [Description], @Qty as [Qty]

Update tps
Set [Payroll_Id]= tmp.Payroll_Id


,[ProductCode]= tmp.ProductCode
,[Description] = tmp.Description
,[Qty] = tmp.Qty
FROM dbo.SmLine tps
INNER JOIN @Tb1 tmp on tmp.Id= tps.Id
INSERT INTO SMLine ([Payroll_Id],[ProductCode],[Description],[Qty]) 
Select
tmp.Payroll_Id,tmp.ProductCode,tmp.Description,tmp.Qty
From @Tb1 tmp
LEFT JOIN dbo.SMLine tps ON tps.Id = tmp.Id
WHERE dbo.SmLine IS NULL
END

Which dbms are you using? (That code is product specific. Perhaps MS SQL Server.) — jarlh, Apr 02 '19 at 09:08
I added the `sql-server` tag as that is clearly T-SQL and SQL Server — , Apr 02 '19 at 09:09
Did you ever declare `@Tb1`? And what exactly are the errors you get? Your post also misses the beginning of the `CREATE PROCEDURE ...` statement. Better post the complete thing. — sticky bit, Apr 02 '19 at 09:14

score 0 · Answer 1 · answered Apr 02 '19 at 09:19

0

I recommend using MERGE statement for this purpose.

 MERGE dbo.destination_table AS target  
 USING 
 (
      /* SELECT statement for source data */
 ) AS source
 ON 
 (
     /* key column conditions to identify if record exits or not 
     i.e. target.id= source.id */
 )  
 WHEN MATCHED THEN   
    UPDATE SET Name = source.Name  
 WHEN NOT MATCHED THEN  
    INSERT 
    (
      /* some columns from target */
    )  
    VALUES 
    (
      /* some values from source */
    )

answered Apr 02 '19 at 09:19

Pratik Bhavsar

808
8
32

Read Aaron Bertrand's [Use Caution with SQL Server's MERGE Statement](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/) – Zohar Peled Apr 02 '19 at 09:20
@ZoharPeled Thanks, that list is very useful and I will definitely want to go through it every time i am planning to use MERGE. However, I believe for a simple requirement like that of Randy, there will not be any issues. – Pratik Bhavsar Apr 02 '19 at 09:23

Update and insert via temp table

1 Answers1