How to auto increment INSERT INTO in SQL Server

Question

I'm trying to insert data from table A to table B with query

INSERT INTO tableB(Status, UserIn, TableBID, Name)
    SELECT 
        'Active',
        UserInput,
        COALESCE(MAX(TableAID),0)+1,
        Name
    FROM 
        tableA

But I get an error:

Invalid column name 'TableAID'.

Here my TableA:

CREATE TABLE TableA  
(
    Status VARCHAR(10),
    UserInput VARCHAR(25),
    TableAID INT,
    Name VARCHAR(32)
)

And my tableB

CREATE TABLE TableB
(
    Status VARCHAR(1),
    UserIn VARCHAR(25),
    TableBID INT,
    Name VARCHAR(32)
)

Based on your question, your error is because you don't actually have a column called TableBID in TableB. Your DDL says you do though. You need to post the correct SQL and DDL because it is currently contradictory. You also need to post what you're actually trying to do at a higher level, i.e. load these rows into this table, generating a new id — Nick.Mc
– Nick.Mc, Commented Apr 4, 2017 at 1:58

artm · Accepted Answer · 2017-04-04 01:55:36Z

10

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.

answered Apr 4, 2017 at 1:55

artm

8,5943 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kentgi Over a year ago

i already edited the question. but this answer is acceptable! thank for the reply!

Nick.Mc Over a year ago

The usual approach is to create an identity column in the target table but this is certainly ample, as long as you don't run the query twice.

Eray Balkanli Over a year ago

An awesome solution.

AlwaysData · Accepted Answer · 2017-04-04 02:12:49Z

4

Two things I noticed. One it looks like for Status you are trying to insert 5 characters of data into a varchar(1) column. Maybe you should change the column length or the data you are inserting.

Second, you have multiple columns alongside an aggregate function MAX without using a GROUP BY or OVER.

edited Apr 4, 2017 at 2:12

answered Apr 4, 2017 at 2:06

AlwaysData

5553 silver badges9 bronze badges

1 Comment

Kentgi Over a year ago

thank you correcting me, and thank you for the advice... :)

Collectives™ on Stack Overflow

How to auto increment INSERT INTO in SQL Server

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related