Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cDc
Starting Member
30 Posts |
 Posted - 2003-12-14 : 09:29:39
|
| Hi Sorry if this is hard to explain!I was looking at the following URL for a sample database from microsoft im sure quite a few of you have seen it before! [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnduwon/html/d5schmdsgn.asp[/url] My question relates to the "items" table shown in the schema which has an identity field as the primary key. Im wondering how would one bulk-insert this data in the first place whilst still maintaining the relationship of this key? For example in this case (well assume it is) the store sells books, and at some point a "data vendor" would have supplied say a catalogue of 300,000 new books to sell on the website which the DBA would have imported via some method into a table but this would be before the records were inserted into the items table and the itemid would need to get updated. If there is a fk constraint between these fields I'd expect the item to be inserted first before the books table was updated.So, im wondering if anyone has experience of a similar situation, im currently testing this at the moment with similar data and have found two ways of doing this one is by using a cursor to firstly insert the data row by row into my items table and then update the books table with @@identity or alternatively by not making it an identity field (but still a pk) and pumping the new data into a temporary table first, reading the max itemid that already exists in my items table and doing it within a transaction to ensure no items get inserted in the meantime...this avoids a cursor but i'd expect to run into locking issues on a busy database via this method.the line on which I am thinking is to modify the design like thisCREATE TABLE [dbo].[Items] ( [itemid] [int] NOT NULL , [itemtypeid] [int] NOT NULL , [description] [varchar] (300) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[BookItem] ( [itemid] [int] NOT NULL , [bookisbn] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[Books] ( [isbn] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL , [booktitle] [varchar] (300) COLLATE Latin1_General_CI_AS NOT NULL , [publisherid] [int] NOT NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[Items] WITH NOCHECK ADD CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ( [itemid] ) ON [PRIMARY] GOALTER TABLE [dbo].[BookItem] ADD CONSTRAINT [FK_BookItem_Items] FOREIGN KEY ( [itemid] ) REFERENCES [dbo].[Items] ( [itemid] )GOhmm.. does it make sense what im trying to achieve or am i talking jibberish!? I think my main reasons for investigating this is that in my experience when working with data from third parties it can quite often change for example management may decided to change the vendor of their book data (in the case of microsoft sample) and the database then needs to have new data loaded in a completely new format. (for example they may not use isbn as the "key" when supplying the data) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-12-14 : 11:31:41
|
| Probably best to bcp into a staging table the set the id on insert into the production tables.Usually insert any new products so assigning the id then insert them into other tables joining to the product table to get the id.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
bulk inserting data with identity