Add an Incremental Number in a Field in Insert into Select Query in SQL Server

Add an incremental number in a field in INSERT INTO SELECT query in SQL Server

You can use the row_number() function for this.

INSERT INTO PM_Ingrediants_Arrangements_Temp(AdminID, ArrangementID, IngrediantID, Sequence)
    SELECT @AdminID, @ArrangementID, PM_Ingrediants.ID,
            row_number() over (order by (select NULL))
    FROM PM_Ingrediants 
    WHERE PM_Ingrediants.ID IN (SELECT ID FROM GetIDsTableFromIDsList(@IngrediantsIDs)
                             )

If you want to start with the maximum already in the table then do:

INSERT INTO PM_Ingrediants_Arrangements_Temp(AdminID, ArrangementID, IngrediantID, Sequence)
    SELECT @AdminID, @ArrangementID, PM_Ingrediants.ID,
           coalesce(const.maxs, 0) + row_number() over (order by (select NULL))
    FROM PM_Ingrediants cross join
         (select max(sequence) as maxs from PM_Ingrediants_Arrangement_Temp) const
    WHERE PM_Ingrediants.ID IN (SELECT ID FROM GetIDsTableFromIDsList(@IngrediantsIDs)
                             )

Finally, you can just make the sequence column an auto-incrementing identity column. This saves the need to increment it each time:

create table PM_Ingrediants_Arrangement_Temp ( . . .
    sequence int identity(1, 1) -- and might consider making this a primary key too
    . . .
)

How can I increment the value for each INSERT INTO iteration?

use row_number function to give your rows sequential numbers

insert into Table1 (column1,column2)
select 
    (select max(column1) from Table1) + row_number() over (order by T2.anotherColumn),
    T2.anotherColumn
from Table2 as T2

or more safe version (it would work even if you don't have any rows in Table1):

insert into Table1 (column1,column2)
select 
    isnull(T1.m, 0) + row_number() over (order by T2.anotherColumn),
    T2.anotherColumn
from Table2 as T2
    outer apply (select max(column) as m from Table1) as T1

How to insert Auto-Increment using SELECT INTO Statement? SQL SERVER

Use ROW_NUMBER to add sequential number starting from 1.

SELECT *,
    Auto = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
INTO table2 
FROM table1

How can I insert autoincrement value using SQL insert into select statement

Option 1

If you're on at least SQL Server 2012 (you didn't mention a specific version), you have a general sequence number generator that you can use. I like it a lot for this kind of scenario. In the DB1 database, you'd add your sequence like this:

create sequence dbo.WorkOrderSequence
  as int
  start with 5002230 --> pick a starting number greater 
                     --> than any existing [WorkOrder #] 

Then, you can just get the next number(s) in your insert-for-select statement:

insert DB1.dbo.WORKORDERS 
  ([WORK ORDER #], [CUSTOMER], [SO DATE], [SO NUMBER])
select 
  convert(varchar(10), next value for DB1.dbo.WorkOrderSequence ),
  x.Customer_Bill_Name,
  x.TransactionDate,
  x.Reference
from
  openquery
  ([DB29],
    'select distinct 
       Customers.Customer_Bill_Name, 
       JrnlHdr.TransactionDate,
       JrnlHdr.Reference
     from
       Customers
       inner join 
       JrnlHdr on 
         Customers.CustomerRecordNumber = JrnlHdr.CustVendId
     where
       JrnlHdr.JrnlKey_Journal = 11
       and 
       JrnlHdr.TransactionDate = CURDATE()' 
  ) as x

The sequence is a standalone auto-incrementing number. Every time you use the next value for dbo.WorkOrderSequence, it auto-increments. This way, you don't have to modify any table definitions.

Option 2

Alternatively, you could alter the DB1.dbo.WORKORDERS table so that the default value to use the expression...

alter table dbo.WORKORDERS
  alter column [Work Order #] nvarchar(10) not null
    default( convert( nvarchar(10), next value for dbo.WorkOrderSequence ) )

If you do this, then you can completely omit inserting the [Work Order #] altogether and let the default do the magic.

Option 3

If you're not on 2012, but on at least 2008, you can still get there...but it's a little trickier because you have to get the current starting [Work Order #]:

insert DB1.dbo.WORKORDERS 
  ([WORK ORDER #], [CUSTOMER], [SO DATE], [SO NUMBER])
select 
  convert(varchar(10), x.RowNum + y.MaxOrderNum ),
  x.Customer_Bill_Name,
  x.TransactionDate,
  x.Reference
from
  openquery
  ([DB29],
    'select distinct 
       row_number() over( order by JrnlHdr.TransactionDate ) as RowNum,
       Customers.Customer_Bill_Name, 
       JrnlHdr.TransactionDate,
       JrnlHdr.Reference
     from
       Customers
       inner join 
       JrnlHdr on 
         Customers.CustomerRecordNumber = JrnlHdr.CustVendId
     where
       JrnlHdr.JrnlKey_Journal = 11
       and 
       JrnlHdr.TransactionDate = CURDATE()' 
  ) as x
  cross join
  (
    select 
      convert( int, max( [Work Order #] ) ) as MaxOrderNum
    from 
      Db1.dbo.WORKORDERS
  ) as y

Option 4

If you're on something earlier than 2008...you'll probably want a stored procedure to do the insert in two steps: inserting the work orders into a temporary table (one with an auto-increment [Work Order #] starting at the current table's max( [Work Order #] ) + 1 )...and then step 2 would insert the temp table into WORKORDERS with a convert.

How to generate auto increment field in select query

If it is MySql you can try

SELECT @n := @n + 1 n,
       first_name, 
       last_name
  FROM table1, (SELECT @n := 0) m
 ORDER BY first_name, last_name

SQLFiddle

And for SQLServer

SELECT row_number() OVER (ORDER BY first_name, last_name) n,
       first_name, 
       last_name 
  FROM table1 

SQLFiddle

SQL insert into select auto increment variable

Seems like ROW_NUMBER can do this.

An abstract example:

DECLARE @T1 TABLE (ID INT)
DECLARE @T2 TABLE (ID INT, VALUE INT)
INSERT INTO @T1 VALUES (1), (4)
INSERT INTO @T2 SELECT *, (ROW_NUMBER() OVER(ORDER BY ID))+100 FROM @T1
SELECT * FROM @T1
SELECT * FROM @T2

Applying that to your code:

declare @t1 table
(
    transid             varchar(15) not null primary key,
    content             varchar(1000),
    userid              int not null,
    dtcreate            datetime not null
)

DECLARE @t2 TABLE ( userid INT, content VARCHAR(1000), dtcreate DATETIME)

INSERT INTO @t1 (transid,content,userid,dtcreate)
SELECT (CONVERT(VARCHAR(10), dtcreate, 112)+RIGHT('0000'+CONVERT(VARCHAR, ( 
row_number() over(order by userid))+100), 5)) , content, userid, dtcreate FROM @t2

One issue I recognize, is that you cannot omit ORDER BY, hopefully that's not a problem.

SQL Insert Function (Insert incremental value)

You can use ROW_NUMBER to generate sequential value for COLUMN1:

INSERT INTO TABLE_A (COLUMN1,COLUMN2,COLUMN3,COLUMN4) 
SELECT
    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) - 1,
    COLUMN6,
    COLUMN7,
    COLUMN8 
FROM TABLE_B;

How to auto increment INSERT INTO in SQL Server

You are getting that error because you are selecting from tableA, which doesn't have a TableBID column. As I understand you are trying to get the max tableBID and increment it by one for each row you insert from tableA, you could try:

INSERT INTO tableB(Status,UserIn,TableBID,Name)
SELECT 
'Active',
UserInput,
ROW_NUMBER() OVER (order by (select 1))+ (SELECT COALESCE(MAX(TableBID),0) FROM tableB ),
Name
FROM 
tableA

ROW_NUMBER() OVER (order by (select 1)) will return an incrementing number starting from 1 for each row you are inserting from tableA.

Are there a best practices of generate a increment sequence number in sql database?

Each RDBMS system usually has it own "auto-number" (usually used for PrimaryKeys) setup.

MySql, Ms-Sql-Server, ORACLE (the article shows the 3 different syntaxes for each...just at one url)

https://www.w3schools.com/sql/sql_autoincrement.asp

PostGres:

https://chartio.com/resources/tutorials/how-to-define-an-auto-increment-primary-key-in-postgresql/

Your question is tagged with (microsoft)sql-server, so I'll paste
that.

Syntax for SQL Server The following SQL statement defines the
"PersonKey" column to be an auto-increment primary key field in the
"Person" table:

CREATE TABLE dbo.Person (
    PersonKey int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255)
);

Do not reinvent the wheel.

So unless you are (trying) to INSERT a million rows in < 2 seconds....use what is already there for you.

Opinions.

You should not care that each primary-key is perfectly in sequence. Aka, "gaps" should be Ok.

if you think you need perfect sequencing, you need to ask yourself "why".

a primary key needs to be unique. having "order" helps with indexing.

but

1,2,3,6,7,9,11 are ordered. (4,5,8 are missing, but does it really matter that they are missing?)

I will add that Ms Sql Server has added "sequences" since version 2016 (or was it 2014?)

https://learn.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-2016

There are reasons to pick one over the other.

https://www.sqlshack.com/difference-between-identity-sequence-in-sql-server/

The 2 cent explanation is sequence can provide a range of values. And it is not tied to a single table (like identity is).

But practically, you end up having more "gaps" in the values..because once a sequence is requested, the same value is never regenerated...even if the ~potential rows for INSERT do not actually make it as inserted-rows.

---

Related Topics