[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

Using TSQL to make a PK column identity specification

How do I, using TSQL, make a column in a table named IDNumber (which is a primary key) identity specification?  And how do I make it, if it is identity specification, not identity specification?
0
stev0931
Asked:
stev0931
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot change a column to change the identity specification, you have to drop and recreate the column to change it.
another way is to recreate the column entirely.

you should not need to change the specification of identity in prod application, can you explain why you want to do this?
 
0
 
stev0931Author Commented:
I want to move data from 4 tables from one database to another.  I tried doing an Export Data, but it failed since the fields are identity specification
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>but it failed since the fields are identity specification

with DTS, there is a option to allow to insert into a identity field.
in TSQL, the command would be

SET IDENTITY_INSERT ON (tablename)

INSERT ....

SET IDENTITY_INSERT OFF (tablename)
0
 
stev0931Author Commented:
Thanks!
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now