Sql Server high performance insert in two related tables

Question

Sirs,

I have the following physical model below, resembling an class table inheritance like the pattern from Fowler (http://martinfowler.com/eaaCatalog/classTableInheritance.html)

CREATE TABLE [dbo].[ProductItem] (
[IdProductItem]     INT             IDENTITY (1, 1) NOT NULL,
[IdPointOfSale]     INT             NOT NULL,
[IdDiscountRules]   INT             NOT NULL,
[IdProductPrice]    INT             NULL);

CREATE TABLE [dbo].[Cellphone] (
[IdCellphone]    INT          IDENTITY (1, 1) NOT NULL,
[IdModel]    INT          NOT NULL,
[IMEI]  NVARCHAR (150) NOT NULL,
[IdProductItem] INT  NULL
);

ProductItem is my base class. It handles all actions related to the sales. Cellphone is a subclass from ProductItem. It adds the atributes and behavior specific that I need to use when I sell an cellphone (IMEI number, activate the cell phone etc)

I need to track each item of the inventory individually. When I receive a batch of 10.000 cellphone, I need to load all this information in my system. I need to create the cellphones and the productitem in my database.

If it was only one table, it is easy to use bulk insert. But, in my case I have an base class with some diferent subclasses represented by tables. What is the best approach to handle this task?

Regards

Camilo

Answer 1

If you're ok with buik inserts, it's still easiest to build a little script to build the tables using an appropriate sequence for referential integrity - in your case probably product, then instances of product (cellphones).