Creating Identity Field

Below is a simple CREATE TABLE statement in my DTS job that drops myTable,
recreates it and then transfers data from myTable in database 1 to the
myTable in destination database. Given the fact that myTable contains data
and that the causeID field may not begin with a 1 value, what parameters
should I use so SQL will not change or start with causeID = 1?
I just want my data to import and retain whatever identity values it has. Do
I have to specify the (1,1) after IDENTITY?
CODE:
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL,
[causeCode] varchar (3) NULL,
[causeName] varchar (50) NULL
)Scott wrote:

> I just want my data to import and retain whatever identity values it
> has. Do I have to specify the (1,1) after IDENTITY?
> CODE:
>
> CREATE TABLE [myDatabase].[dbo].[myTable] (
> [causeID] int IDENTITY(1,1) NOT NULL,
> [causeCode] varchar (3) NULL,
> [causeName] varchar (50) NULL
> )
You can use
Set IDENTITY_INSERT [myTable] ON
then insert the data and afterwards
Set IDENTITY_INSERT [myTable] OFF
New data which will be inserted later will get the correct identities.
HTH,
Stijn Verre^t.|||Why are you using IDENTITY at all? Why is "cause_code" not the proper,
relational key'|||causeID is the primary key, causeCode is a text field code that a user
enters.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
> Why are you using IDENTITY at all? Why is "cause_code" not the proper,
> relational key'
>|||Let me help out with Celko's angle here.
If I type causeCode = "foobar" and so does Celko, why do we have different
causeID values?
Now, let me say that I am not against using Identity (or a similar surrogate
key approach), but I do believe that causeCode should at least be unique /
non-repeating.
A
"Scott" <sbailey@.mileslumber.com> wrote in message
news:ex2v1t88FHA.3048@.TK2MSFTNGP10.phx.gbl...
> causeID is the primary key, causeCode is a text field code that a user
> enters.
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
>|||Probably something like this...
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL constraint SK_myTable unique
clustered,
[causeCode] varchar (3) NULL constraint PK_myTable primary key
nonclustered,
[causeName] varchar (50) NULL
)
This then satisfies a natural key and surrogate key, you would then use
causeID as the foreign key in other tables...
CREATE TABLE myAudit (
causeID int not null references myTable( causeID )
...
)
rather than...
CREATE TABLE myAudit (
causeCode varchar(3) not null references myTable( causeCode )
)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eudsZJ98FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Let me help out with Celko's angle here.
> If I type causeCode = "foobar" and so does Celko, why do we have different
> causeID values?
> Now, let me say that I am not against using Identity (or a similar
> surrogate key approach), but I do believe that causeCode should at least
> be unique / non-repeating.
> A
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:ex2v1t88FHA.3048@.TK2MSFTNGP10.phx.gbl...
>|||I think Scott simply has terminology mixed up which isn't a crime, some
people confuse a 'relational key' with a the standard Natural Key.
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL constraint SK_myTable unique
clustered,
[causeCode] varchar (3) NULL constraint PK_myTable primary key
nonclustered,
[causeName] varchar (50) NULL
)
This then satisfies a natural key and surrogate key, you would then use
causeID as the foreign key in other tables...
CREATE TABLE myAudit (
causeID int not null references myTable( causeID )
...
)
rather than...
CREATE TABLE myAudit (
causeCode varchar(3) not null references myTable( causeCode )
)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
> Why are you using IDENTITY at all? Why is "cause_code" not the proper,
> relational key'
>