Insert values into multiple SQL tables

Question

What is a good method to insert table data that replies on many tables in C# and SQL server?

So for example say I have the following tables:

Products: PK=PID, Product_Name
Build: PK:BID, Build_Number, FK:PID
Files: PK:FID, File_Name, FK:PID
FileDetails: PK:FDID, FK:FID, FK:BID, File_Size, File_Version

So all the Primary Keys are auto incremented numbers

So I have an xml file, in there I have rows of information such as:

Product_Name ,Build_Number,File_Name,File_Size,File_Version
FooCreator1.0,112233      ,foo.exe  ,123456   ,3.5
FooCreator1.0,112233      ,bar.exe  ,234567   ,1.5

I want to be able to add the rows to my tables. Imagine my database is empty.

Do I first have to add all the products, then add all the builds, then add all the files, then join all the tables and then add all the file details?

EDIT: I will have around 20'000 rows and I will be storing around 40 values per line in the final unique table.

Answer 1

Yes. Open a transaction and add the rows in a subsequent order. You have to add the Product first and get its ID to be able to add a Build and use that ID as the foreign key. You could also write this as a stored procedure. SQL Server doesn't have list-type procedure parameters, but if you want to pass a complex structure like this in one step, and you already have it in XML format, you can pass that document, as SQL Server 2005 (you didn't say which version you're using) supports XML parameters. Having that, you can use XPath in your stored procedure to query for stuff and build the data.

Answer 2

C#/.NET may have a better way, but the usual way to do it is as you suggest (roughly). For each line in the XML file, you have to create the PRODUCT first (or get its PID if it already exists).

Then add the BUILD if it doesn't exist (get the BID if it does).

Then add the FILE if it doesn't exist (get the FID if it does).

Then add or update the many-to-many relationship in FILEDETAILS.

Each of the insert/select operations (product, build, file) would go as follows:

insert into PRODUCTS (PRODUCT_NAME) VALUES ('FooCreator1.0')
// ignore errors from preceding statement
select PID from PRODUCTS where PRODUCT_NAME = 'FooCreator1.0'

since, if someone's beat you to the punch in creating that product, you just want its PID for later inserts.

Answer 3

This is what I would do, although I'm not sure it's the best way:

declare @PID bigint
declare @BID bigint
declare @FID bigint

insert into Products (Product_Name)
values ('FooCreator1.0')

select @PID = scope_identity()

insert into Build (Build_Number, PID)
values (112233, @PID)

select @BID = scope_identity()

insert into Files (File_Name, PID)
values ('foo.exe', @PID)

select @FID = scope_identity()

insert into FileDetails (FID, BID, File_Size, File_Version)
values (@FID, @BID, 123456, '3.5')

Something like that? Probably want to run the whole thing in a transaction, in case anything goes wrong.

Answer 4

Essentially, yes (the last0. But how you do it may depend on the number of rows involved. If you have a few hundred (maybe a thousand or so), then you could use something like LINQ-to-SQL quite happily: create the objects you need, and use InsertOnSubmit to add the necessary items to the data-context. Finally, call SubmitChanges (job done; other ORM tools will work similarly).

If you have tens of thousands of rows (or whatever), then you might just take the whole xml into the database (perhaps a stored procedure with an xml parameter). Use Sql/xml at the database, and do 4 INSERT statements from the xml parameter.

For hundreds of thousands/millions of rows, you'd want to use SqlBulkCopy to push the data into a staging table (laid out like your example) - then use a stored procedure to do the 4 INSERTs. Getting xml to behave like csv is a job for a bespoke data reader.

Answer 5

I would recommend to use the insert statements that Andy White proposes in a stored procedure for several reasons:

• Performance, allowing the DB compile and cache statements.
• Security, call SP with input data as parameters will limit the usage of malicious values.
• Kepping the DB-stucture separated from the client, you will not need to recompile if/when you want to change the schema.
• Possibility to add internal logging/error handling without client interference.

Offcourse all depends on the usage of the operation. If it is one time operation then it will probably be overkill.

You should encapsulate the statement in transaction to ensure that all or nothing are done, and even use a TRY-CATCH. But as I wrote it all depends on the usage of the operation.

/ JB