Re: identitycolumn as pk
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/03/04
- Next message: shank: "creating a DTS package"
- Previous message: Hugo Kornelis: "Re: Returning System Date (Without the time)"
- In reply to: Ivo Grootjes: "identitycolumn as pk"
- Next in thread: Ivo Grootjes: "Re: identitycolumn as pk"
- Reply: Ivo Grootjes: "Re: identitycolumn as pk"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 3 Dec 2004 09:34:38 -0500
Ivo Grootjes wrote:
> Hi NG,
>
> This would be my first post on a newsgroup so bare with me :)
>
> I'm working on an existing application which uses a database with
> tables which have identity columns as primarykeys. The webapplication
> needs new functionality which requires 4 tables to be copied from one
> database to another. The tables are related to eachother. For
> instance there's a chapter table and a publication table. a chapter
> can contain 0..* publications. The chapter table also has a relation
> toitself (allowing a hierarchical structure). I'm receiving the data
> through a asp.net dataset which contains 4 datatables. What i want to
> do is insert the data in the new database through 4 batch inserts but
> since i only know the primarykey after i've inserted a record i can't
> do this.
>
> What i'm doing right now to overcome this issue is loop through the
> asp.net datatables and insert the records one by one and store the
> generated keys so i can use them for related records. Because the
> tables consist of tens of thousands of records this is slow and not
> the preffered way of doing things.
>
> What would be a good solution to this. Should i generate a primarykey
> myself outside of the database and change the database schema? I have
> the feeling that that is the way to go but i'm not really sure. I
> think using identity columns as primary keys is a commonly used
> technique so i bet there must be other solutions to this problem.
> Changing the database schema ultimately is an option but we prefer
> not to.
>
> Can anybody point out some tips, reading material, or anything else
> which might help me to find a good solution to this problem?
>
> Thank in advance
I think what you're saying is that you have conflicts in your identity
values in the two databases.
You can use SET IDENTITY_INSERT ON <table_name> ON
in order to insert into the table without SQL Server creating the
identity value for you.
When you are done, you probably should run:
DBCC CHECKIDENT('table_name', RESEED)
to make sure SQL Server knows what number to use next.
-- David Gugick Imceda Software www.imceda.com
- Next message: shank: "creating a DTS package"
- Previous message: Hugo Kornelis: "Re: Returning System Date (Without the time)"
- In reply to: Ivo Grootjes: "identitycolumn as pk"
- Next in thread: Ivo Grootjes: "Re: identitycolumn as pk"
- Reply: Ivo Grootjes: "Re: identitycolumn as pk"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
