Sybase: Copy a table with SELECT INTO ignoring the IDENTITY property

When you use SELECT INTO to copy data from one table to a new table (e.g. to create a copy of the table) and your source table has an identity column, the corresponding column in the newly created table will also be an identity column. Here an example:

We have a table with an indentity column:

1> create table mytable_with_id (
2>  id int identity,
3>  something char(1))
4> go
1> sp_help mytable_with_id
2> go
...
 Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ----------- ---- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
 id          int       4 NULL  NULL     0 NULL         NULL      NULL             NULL                            1
 something   char      1 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
...

We select into a new table:

1> select * into mycopy from mytable_with_id
2> go

And get a table with an identity column:

1> sp_help mycopy
2> go
...
 Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ----------- ---- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
 id          int       4 NULL  NULL     0 NULL         NULL      NULL             NULL                            1
 something   char      1 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
...

One workaround to this is to convert the id column to an int. It then retains the same value but the column is not an identity column anymore:

1> select convert(int, id) as id, something into mycopy from mytable_with_id
2> go
1> sp_help mycopy
2> go
...
 Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ----------- ---- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
 id          int       4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 something   char      1 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
...

Since the identity property is also not propagated when using a UNION, you can also do the following:

1> select id, something into mycopy from mytable_with_id UNION select id, something from mytable_with_id where 1=0
2> go
1> sp_help mycopy
2> go
...
 Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ----------- ---- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
 id          int       4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 something   char      1 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
...

The second SELECT in the UNION doesn’t add new data (1 is generally not equal to 0) but prevents the identity property from being propagated.

You can also do it with a SELECT *:

1> select * into mycopy from mytable_with_id UNION select * from mytable_with_id where 1=0
2> go
1> sp_help mycopy
2> go
...
 Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ----------- ---- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
 id          int       4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 something   char      1 NULL  NULL     0 NULL         NULL      NULL             NULL                            0

Facebook

Twitter

Google +