Sql-server – Insert bulk Parent and Children rows using SQL Query

insertqueryrelational-theorysql server

We receive bulk data from our customers in a spread sheet. I loaded them in a temporary table as of now. I tried to normalize the data and create a parent table and each parent to have 4 or 5 child rows. Is there a way to insert all the parents and their children using queries? I am using MSSQL

Best Answer

Once you have normalised you need 2 queries, one to load parents then one to load children (you can only insert into 1 table at a time using sql)

It sounds like you do this ofter, so why not create a file with the sql commands that you use, and each time you do this task open the file up in your databases sql editor window and run (may-be first modifying) these commands. If it always the same sort of task, you could write a script that connects to the database and runs the sql statments you need - then it is a one click or command task.

Related Solutions

Sql-server – Advanced search engine or server for relational database

Stack Overflow search is now 81% less crappy, thanks to Lucene. Whether this, or any of the other document oriented search servers, would be a good fit is difficult to tell from the detail you've posted.

Lucene/Solr and their ilk are built to search textual data. It sounds like you should be considering a graph database. High Scalability has an interesting article on Neo4j which discusses some of the concepts well.

Sql-server – SQL Server 2005 – Generate and Bulk Insert 3 million rows across 2 tables

Your table structure looks like this?:

Table1
id (identity)
data1
data2
data3

Table 2
id (identity)
t1id (fk to table1)
data1
data2
...

-- Main Table insert
INSERT INTO table1 (data1,data2,data3)
VALUES('dummy1','dummy2','dummy3');

--get last inserted id
var=SELECT SCOPE_IDENTITY()

-- dependent insert into table2
INSERT INTO table2 (t1id,data1,data2,...)
VALUES(var,'dummy1','dummy2','...');

Look at this answer.
If you return the id from the newly inserted row of Table1 you could use that to insert the dependent row into Table2 without querying Table1 for every row.
If the select for the newly generated id is your bottleneck this should help. If you're full scanning your main table for every row, indexing the id column, if not already done, could also decrease the time needed for the lookup.

Best Answer

Related Solutions

Sql-server – Advanced search engine or server for relational database

Sql-server – SQL Server 2005 – Generate and Bulk Insert 3 million rows across 2 tables

Related Question