CODEWITHSUNDEEP
sqlsql-serversql-insertauto-increment
Catazza
Catazza

Reputation: 285

"Batch auto-increment" IDs for multiple row insertion

I am working in SQL Server 2017, and I have a table of the form:

tbl_current

COL1   COL2
-----------
 A      1
 B      3
 C     56

which I want to periodically insert into the table tbl_release.

This table would have an extra ID column, which I'd like to auto-increment with each "batch insertion". For example, let's say I perform the the ingestion of tbl_current into tbl_release, it would look like this:

tbl_release

ID   COL1   COL2
----------------
 1    A       1
 1    B       3
 1    C      56

Now, let's say I perform another ingestion with the same data, it'd look like:

tbl_release

ID   COL1   COL2
----------------
 1    A       1
 1    B       3
 1    C      56 
 2    A       1
 2    B       3
 2    C      56

What is the best way to achieve this? Is there some SQL Server feature that would allow to achieve this, or do I need to run some sub-queries?

Upvotes: 0

Views: 2825

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

Your id is really an object. I would strongly suggest that you give it a full table:

create table batches (
    batch_id int identity(1, 1) primary key,
    batch_start_date datetime,
    . . . 
);

Then, your existing table should be structured as:

create table releases (
    release_id int identity(1, 1) primary key,
    batch_id int not null references batches(batch_id),
    col1 char(1),
    col2 int
);

That way, your database has referential integrity.

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32707

I'd personallly use a sequence for this. Assuming the insert into your ephemeral table is done, it'd looks omething like this:

declare @ID int = next value for sequence dbo.mySequence;

insert into tbl_release
   (ID, col1, col2)
select @ID, col1, col2
from tbl_current;

Upvotes: 5

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can try this using MAX() function as shown below.

Declare @maxId int
set @maxId = (Select isnull(id, 0) from tbl_Current)
set @maxId = @maxId + 1
--Now to insert
insert into tbl_release values (@maxId, <Column1Value>, <Column2Value>)

For multiple insert you can try this

INSERT INTO tbl_release
SELECT @maxId, col1, col2 FROM tbl_Current

If your table's Id column is identity then you can also use the Scope_Identity to get the max value of Id column.

Upvotes: 0

Related Questions