August 8, 2011 at 2:10 pm
I am using a merge to populate a target table. This table has a new column which takes identity values. All the other columns are populated with Source table data, so when it comes to this new column, I don't know how to handle.
How do I get autoincremented values to populate an identity column on a MERGE statement?
August 8, 2011 at 2:30 pm
correction, using the WHEN NOT MATCHED THEN INSERT portion of the MERGE statement, I want to insert values into the identity column.
August 8, 2011 at 3:09 pm
you shouldn't have to explicitly insert a value into an identity column. The idea of the identity column is to auto increment - so when a new value is inserted into the table the value is inserted for you.
In the merge statement just specify the columns that you want to insert excluding the identity column and that should work ok.
Here's a little example:
-- Create a test table
CREATE TABLE TABLE1
(
id INT IDENTITY(1,1)
,custname VARCHAR(50)
CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (id ASC)
);
-- Populate the table with some data
INSERT INTO table1 (custname)
VALUES ('Mr'),
('Chris'),
('michael'),
('kerry'),
('gary'),
('adam'),
('wayne'),
('alan');
-- select from the table to see a before snapshot
SELECT * FROM table1;
-- declare table variable to be used as a source table
DECLARE @table2 TABLE
(
id INT IDENTITY(1,1)
,custname VARCHAR(50)
);
-- populate the source table
INSERT INTO @table2 (custname)
VALUES ('Mr'),
('Christopher'),
('Michael'),
('Kerry'),
('Gary'),
('adam'),
('wayner'),
('alan');
-- Merge source to target
MERGE INTO table1 tgt
USING @table2 src
ON tgt.custname = src.custname
WHEN MATCHED THEN
UPDATE SET
tgt.id = tgt.id + 1
WHEN NOT MATCHED THEN
INSERT (custname)
VALUES (src.custname);
-- select from the table to see after snapshot
SELECT * FROM table1;
Hope this helps..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply