CODEWITHSUNDEEP
sqlsybase
Moudiz
Moudiz

Reputation: 7377

inserte data with incremented values

I have this table tab1

name
-----
'big'
'smal'
'bird'

tab2 

id name
-- ---
1  empty
2  empty
3  empty
4  empty

I want to insert the name from tab1 to tab2 with incremented id this is desired result

  id name
    -- ---
    1  empty
    2  empty
    3  empty
    4  empty
    5  'big'
    6  'smal'
    7  'bird'

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

The correct way to handle this is for table2 to define id as an identity column. You do this when you define the table:

create table table2 (
    id int identity primary key,
    . . .

Then you can simply do:

insert into table2(name)
     select name
     from table1;

You should consider defining the table correctly, if it is not. But, you can also do this in more recent versions of Sybase doing:

insert into table2(name)
     select maxid + row_number() over (order by name), name
     from table1 cross join
          (select max(id) as maxid from table2) x;

Note: This will assign the id values in alphabetical order.

Upvotes: 1

Related Questions