CODEWITHSUNDEEP
sql-serverdatabase
Naila Akbar
Naila Akbar

Reputation: 3358

insert data in sql server from other table

I've some records in table_3 and I want to insert that record to table_2 but value of first column (Id) will be (NUMBER+1) of table_1. My code is

INSERT INTO TABLE_2(Id,NAME)
SELECT (SELECT MAX(NUMBER) FROM TABLE_1)+1 AS Id,name
FROM TABLE_3

Count of table_1 is 200. and count of TABLE_3 is 10. I want new inserted Ids are like

(201,202,203,204,205,206,207,208,209,210)

but from query am getting is like...

(201,201,201,201,201,201,201,201,201,201)

Upvotes: 1

Views: 38

Answers (2)

void
void

Reputation: 7890

you can do it simply with only one select using ROW_NUMBER() and a group by

INSERT INTO TABLE_2(Id,NAME)
 SELECT MAX(NUMBER) + ROW_NUMBER() OVER (ORDER BY name),name
 FROM TABLE_3
 group by name

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

In your query (SELECT MAX(NUMBER) FROM TABLE_1)+1 is always the same, since MAX(NUMBER) is just 200.

If you want to increment by 1 for each new record inserted, starting at (SELECT MAX(NUMBER) FROM TABLE_1)+1, try this:

INSERT INTO TABLE_2 (Id, NAME)
SELECT Id + rn, name 
FROM (
   SELECT (SELECT MAX(NUMBER) FROM TABLE_1) AS Id, name,
          ROW_NUMBER() OVER (ORDER BY name) AS rn
   FROM TABLE_3 ) t

Upvotes: 1

Related Questions