Hi %

It's possible to use bulk insert operations if table containing the new rows to be inserted contains any kind of a surrogate id (or another uniqueness).

---========================================================

-- table definitions

-----------------------------------------------------------

-- destination table for new data to be inserted

DECLARE @Destination TABLE (

Id INT NOT NULL IDENTITY(1,1)

PRIMARY KEY CLUSTERED

,SomeInt INT

);

-----------------------------------------------------------

-- table containing all rows to be newly inserted into

-- destination table

-- IMPORTANT

-- the table containing the new data to be inserted requires

-- any kind of uniqueness (e.g. a surrogate id)

DECLARE @NewData TABLE (

SurrogateId INT NOT NULL

PRIMARY KEY CLUSTERED

,SomeInt INT

);

---========================================================

-- create some sample data

-----------------------------------------------------------

-- insert existing data into destination table

INSERT INTO @Destination

SELECT -777

UNION ALL SELECT -888;

-----------------------------------------------------------

-- new rows to be inserted into destination table

INSERT INTO @NewData

SELECT -1, 11

UNION ALL SELECT -2, 22;

---========================================================

-- bulk insert rows into destination table and catch new identities

-----------------------------------------------------------

-- variables to catch count of inserted rows and scope-identity

DECLARE

@LastIdentity INT

,@RowCount INT;

-----------------------------------------------------------

-- bulk insert new rows into destination table

-- IMPORTANT

-- to get the following operations work ensure to use an

-- ORDER BY in SELECT. To avoid problems due to paralellism

-- use MAXDOP 1 option what should be okay for an INSERT

INSERT INTO @Destination

SELECT SomeInt

FROM @NewData

ORDER BY SurrogateId

OPTION (MAXDOP 1);

-----------------------------------------------------------

-- catch the last created scope-identity and the count of

-- rows inserted

SELECT

@LastIdentity = SCOPE_IDENTITY()

,@RowCount = COUNT(*)

FROM @NewData;

-----------------------------------------------------------

-- use a CTE that creates a row number ordered by the

-- surrogate id of newly inserted rows

WITH cte AS (

SELECT

SurrogateId

,ROW_NUMBER() OVER (ORDER BY SurrogateId) RowNum

FROM @NewData

)

SELECT

*

-- calculate destination table IDENTITY values

,@LastIdentity - @RowCount + RowNum

FROM cte

Greets

Flo