Copying data from 2000 to 2005. Indentity insert not working

9 Sep 2006 3:54 PM

Charlie@CBFC

Hi:

I'm at my wits end. When copying data from sql2000 to sql2005, identity
columns are reseeded starting with 1. I have checked indentity insert on,
but makes no difference. How the heck do copy data from 2000-2005 and
without regenerating keys?

Thanks,
Charlie

9 Sep 2006 5:23 PM

Warren Brunk

You can remove the identity for the destination column, import the data
without identity insert on, and then readd the identity onto the column.

WB

Show quote

"Charlie@CBFC" <char***@comcast.net> wrote in message
news:%23ZERwfC1GHA.1256@TK2MSFTNGP02.phx.gbl...
> Hi:
>
> I'm at my wits end. When copying data from sql2000 to sql2005, identity
> columns are reseeded starting with 1. I have checked indentity insert on,
> but makes no difference. How the heck do copy data from 2000-2005 and
> without regenerating keys?
>
> Thanks,
> Charlie
>
>

9 Sep 2006 5:39 PM

Warren Brunk

When importing to a table with an identity column you want to turn identity
insert "off" and ignore the identity column.

Show quote

"Charlie@CBFC" <char***@comcast.net> wrote in message
news:%23ZERwfC1GHA.1256@TK2MSFTNGP02.phx.gbl...
> Hi:
>
> I'm at my wits end. When copying data from sql2000 to sql2005, identity
> columns are reseeded starting with 1. I have checked indentity insert on,
> but makes no difference. How the heck do copy data from 2000-2005 and
> without regenerating keys?
>
> Thanks,
> Charlie
>
>

9 Sep 2006 6:18 PM

Richard Mueller

Actually, the procedure would be:
=============
' Allow update of Identity field.
Set IDENTITY_INSERT NewMyTable ON

SELECT * INTO NewTable FROM OldTable

' Restore Identify field default behaviour.
Set IDENTITY_INSERT NewTable OFF
=============
However, permission to run "Set IDENTITY_INSERT" cannot be granted to users.
You must be a member of the sysadmin fixed server role, the db_owner or
db_ddladmin fixed database roles, or be the object owner. Í have found no
workaround.

Are you saying you do not raise an error when you run "Set IDENTITY_INSERT"?
Can anyone confirm if the behaviour in SQL Sever 2005 is different?

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

Show quote

"Warren Brunk" <wbr***@techintsolutions.com> wrote in message
news:OqQxhbD1GHA.3900@TK2MSFTNGP05.phx.gbl...
> When importing to a table with an identity column you want to turn
> identity insert "off" and ignore the identity column.
>
>
>
>
> "Charlie@CBFC" <char***@comcast.net> wrote in message
> news:%23ZERwfC1GHA.1256@TK2MSFTNGP02.phx.gbl...
>> Hi:
>>
>> I'm at my wits end. When copying data from sql2000 to sql2005, identity
>> columns are reseeded starting with 1. I have checked indentity insert
>> on,
>> but makes no difference. How the heck do copy data from 2000-2005 and
>> without regenerating keys?
>>
>> Thanks,
>> Charlie
>>
>>
>
>