SQL Server Management Studio - not preserving identities during import

microsoft.public.dotnet.framework.adonet

7 Feb 2006 8:34 PM

Eric

I want to copy my local database to a remote server hosted by a thrid
party company. I started by creating all the tables using creation
scripts made by SSMS. Then I connected to the target database, and used
Tasks, Import to copy my local data to the remote database. I made sure
to select the Identity Insert checkbox for every table. After the
import, my data was there, except the identity values didn't come
across - they all got renumbered starting with 1. This broke my foreign
key relationships. How can I import the data so my identity values are
preserved (I'm starting with an empty target DB)?

PS: I can't use SQL "insert" scripts to migrate the data because some
columns have image data.

8 Feb 2006 3:20 AM

Val Mazur (MVP)

Eric,

If this is just a one-time task, then you could detach database from your
server and attach it to the target database. In this case all the data will
remain the same

--
Val Mazur
Microsoft MVP
http://xport.mvps.org

Show quote

"Eric" wrote:

> I want to copy my local database to a remote server hosted by a thrid
> party company. I started by creating all the tables using creation
> scripts made by SSMS. Then I connected to the target database, and used
> Tasks, Import to copy my local data to the remote database. I made sure
> to select the Identity Insert checkbox for every table. After the
> import, my data was there, except the identity values didn't come
> across - they all got renumbered starting with 1. This broke my foreign
> key relationships. How can I import the data so my identity values are
> preserved (I'm starting with an empty target DB)?
>
> PS: I can't use SQL "insert" scripts to migrate the data because some
> columns have image data.
>
>