CODEWITHSUNDEEP
sqlsql-servermergesql-server-2012
mhn
mhn

Reputation: 2750

using MERGE for incremental insert

I have a scenario where one column of the target table needs to be auto incremented . I do not have identity enabled on this column. So i need to pick the last number and add 1 to it , each time an insert is done.

http://sqlfiddle.com/#!6/61eb4/5

A similar scenario is given in the fiddle link. I do not want the productid of ProductChanges table to be inserted. Instead, i need the last id to be picked and i need it to be incremented and inserted for each new row

Upvotes: 1

Views: 8518

Answers (2)

Dev
Dev

Reputation: 3580

1)create sequence and set to target table.

example

CREATE SEQUENCE table_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

2)create trigger for that sequence,to set the table

CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT 
ON myTable
FOR EACH ROW
WHEN (new.id is null)
DECLARE
v_id qname.qname_id%TYPE;
BEGIN 
SELECT table_seq.nextval INTO v_id FROM DUAL;
:new.qname_id := v_id;
END my_trigger;

Upvotes: 0

mhn
mhn

Reputation: 2750

Code to get this working

DECLARE @intctr int
SELECT @intctr = MAX(productid)+1 from products
DECLARE @strQry varchar(200)
SET @strQry = 
'CREATE SEQUENCE dbo.seq_key_prd
    START WITH ' +convert( varchar(12),@intctr) +'  INCREMENT BY 1 ;'
    print @strQry
    exec( @strQry)


alter table Products
    add default next value for seq_key_prd
    for ProductId;
GO


--Merge statement for data sync
MERGE Products USING ProductChanges ON (Products.Productid = ProductChanges.Productid)
WHEN MATCHED AND Products.VendorlD =0 THEN DELETE
WHEN NOT MATCHED by target then insert (productid,Productname,VendorlD)
values(default,productname,VendorlD)
 WHEN MATCHED THEN UPDATE SET
Products.ProductName = ProductChanges.ProductName ,
Products.VendorlD = ProductChanges.VendorlD;

Upvotes: 6

Related Questions