i want to insert values from table1 to table2 with incremantal value

Question

i tried something like this but the id value is not changing, getting the value for first record and set the value for all rest...

insert into table1 (Id,b,c,d) (select (select max(Id)+1 from table1),x,y,z from table2 where... )

Which database product are you using? SQL Server? Oracle? Something else? — Daniel Renshaw
– Daniel Renshaw, Commented Apr 29, 2010 at 16:33
You know this is not going to be concurrency-safe (w/o locking down table1), do you? — Henk Holterman
– Henk Holterman, Commented Apr 29, 2010 at 16:37
thats ok i wont use that command for concurrency works.. for example ill run this command once a day.. — Adnan M. TURKEN
– Adnan M. TURKEN, Commented Apr 29, 2010 at 16:39

Mark Byers · Accepted Answer · 2010-04-29 16:49:43Z

Use an identity column. Then you can just do this:

INSERT INTO table1 (b, c, d) SELECT x, y, z FROM table2 WHERE ...

If you don't want to use an auto-increment column you can get the same effect by using adding the row number of the row instead of always adding 1. This syntax works in almost all mainstream SQL databases (not MySQL though):

INSERT INTO table1 (Id, b, c, d) SELECT (SELECT MAX(Id) FROM table1) + ROW_NUMBER() OVER (ORDER BY x), x, y, z FROM table2 WHERE ...

Collectives™ on Stack Overflow

i want to insert values from table1 to table2 with incremantal value

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related