Identity col
ss
data
and would like to start it from 100. Could anybody please
give me the syntax
TIA
.
Bret Halford
I believe the supported way would be to bcp the data out,
drop the table and recreate it with that column having
identity, bcp in using -E.
Following that, newly generated identity values will start
with 1 larger than the largest value bcped in. If you want
to set it to a higher value, use the "set identity_insert <table> on"
option and insert a dummy row with a higher value, then turn the
option off and delete the dummy row. Future inserts will
increment from that value.
-bret
ss
"Bret Halford" <br...@sybase.com> wrote in message
news:40368D93...@sybase.com...
Jus
news:40369dc8$1@forums-1-dub...
> Can we run bcp in sql analyzer
No, it's a command line tool.
Justin.
Rob Verschoor
Let's assume your current table is as follows: create table t (a int,
b int)
Then run these statements:
create table t2 (n numeric(6) identity, a int, b int) -- change '6'
(the number of digits) as needed...
set identity_insert t2 on
insert t2 (n, a, b) values (99, 0, 0)
set identity_insert t2 off
delete t2
insert t2 (a,b)
select a,b from t
order by a -- 'order by' is needed only when you want a certain
ordering in the current rows; this can be omitted
And finally:
drop table t
go
sp_rename t2, t
go
I think this does what you want. Keep in mind that the insert...select
may take a long time if the table is big. Alternatively, you can use
bcp to copy the table (use -E during the bcp-in).
HTH,
Rob
-------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5
Author of "Tips, Tricks & Recipes for Sybase ASE" and
"The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop
mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------