SQL Server - insert into using multiple values

Question

I have a set of values in a variable table @vartable

I then want to do the following insert -

insert into tableA
values ((select max(tableA_id)+1 from tableA), 2147, (select id from @vartable), 1, 0, 0)

So for each id in the @vartable it does an insert using the id on the row.

How do I go about this?

edit to note that I need to update the first value (select max(tableA_id)+1 from tableA) as increasing by 1 each new row it inserts.

huMpty duMpty · Accepted Answer · 2014-05-22 16:20:56Z

2

insert into tableA (ColName1,ColName2,ColName3,ColName4,ColName5,ColName6)
Select (select max(tableA_id)+1 from tableA), 2147, id , 1, 0, 0
from @vartable

If you need the Max number to be increment, use below query which use ROW_NUMBER

  Insert into tableA (ColName1,ColName2,ColName3,ColName4,ColName5,ColName6)
  Select (ROW_NUMBER() 
                OVER (ORDER BY Id)+(select max(tableA_id) from tableA) )as aRow,
          2147, id , 1, 0, 0
  From @vartable

Here is fiddle link -- > http://sqlfiddle.com/#!3/cb04f/1

edited May 22, 2014 at 16:20

answered May 22, 2014 at 14:35

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

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

1 Comment

whoisearth Over a year ago

close. unfortunately what this does is assign all values the same first number (select max(tableA_id)+1 from tableA). I need this number to increase by 1 each new row it inserts.

Collectives™ on Stack Overflow

SQL Server - insert into using multiple values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related