Insert new row with id

Question

Hi

I have a table where the id is not an identity column for we need to be able to assign the ids later on our own. How could i insert records and assign an id based on the current id (+ 1) of the target table? The records come from other table(s).

E.g.
- 2 tables: Table_Old_Address, Table_New_Address
- Table_New_Address is empty
- Insert records coming from Table_Old_Address into Table_New_Address and assigned an ID continously

Edit:
Anyone could help?
How could i number rows in my select statement? I've tried @@ROWCOUNT but it returns 0.

Answer 1

Hi,

by the way - this is a german forum.. :-)

Could you please show the tableheaders and some example data. I think you want to edit a row wich doesn´t have a id. So you have to read the row values to 2 parameters - 1 for the old value and 1 for the new value. If you want to edit a row you have to compare the old value`s and paste the new after that comparision. I could provide you some code if you write some more informations..

Answer 2

The example above is made up and i've already solved the problem. Thanks for the answer though.
For those who wants to know: I've solved it using Row_Number().

Answer 3

Hallo, Syslock,

 

I have also the same problem like this. I created a new view based of two tables. And for this new view I would like to insert a column with id. Could you tell me in detail, how do you solve your problem? thanks a lot!

best regards

H.Q. Qu

Answer 4

Hi H.Q. Qu

E.g. your sql for your view is:

Select table1.field1, table1.field2, table2.field1 from table1, table2

then you could insert an id field like this:

Select ROW_NUMBER() OVER (ORDER BY table2.field1) AS 'ID', table1.field1, table1.field2, table2.field1 from table1, table2

Hope it helps.