Solved

Using TSQL to make a PK column identity specification

Posted on 2006-06-24
4
480 Views
Last Modified: 2008-02-26
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
Comment
Question by:stev0931
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16975700
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
 

Author Comment

by:stev0931
ID: 16976086
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 16976409
>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
 

Author Comment

by:stev0931
ID: 16976712
Thanks!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question