What is the best way to quickly insert SQL data and dependent rows?

Question

I need to write code to insert about 3 million rows of data.
At the same time, I need to insert the same number of lines of related messages.

those. The diagram is as follows:

Item
  - Id
  - Title

Property
  - Id
  - FK_Item
  - Value

My first attempt was something vague:

BaseDataContext db = new BaseDataContext();
foreach (var value in values)
{
    Item i = new Item() { Title = value["title"]};
    ItemProperty ip = new ItemProperty() { Item = i, Value = value["value"]};
    db.Items.InsertOnSubmit(i);
    db.ItemProperties.InsertOnSubmit(ip);
}
db.SubmitChanges();

Obviously, it was very slow, so I am now using something like this:

BaseDataContext db = new BaseDataContext();
DataTable dt = new DataTable("Item");
dt.Columns.Add("Title", typeof(string));
foreach (var value in values)
{
    DataRow item = dt.NewRow();
    item["Title"] = value["title"];
    dt.Rows.Add(item);
}
using (System.Data.SqlClient.SqlBulkCopy sb = new System.Data.SqlClient.SqlBulkCopy(db.Connection.ConnectionString))
{
    sb.DestinationTableName = "dbo.Item";
    sb.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Title", "Title"));
    sb.WriteToServer(dt);
}

But this does not allow me to add the corresponding "Property" lines.

, , , (, , , , - ), .

- ( , ) ?

Answer 1

:

1) BCP "",

CREATE TABLE stage(Title AS VARCHAR(??), value AS {whatever});

:

CREATE INDEX ix_stage ON stage(Title);

2) SQL INSERT Item:

INSERT INTO Item(Title) SELECT Title FROM stage;

3) , Property, Item:

INSERT INTO Property(FK_ItemID, Value)
SELECT id, Value
FROM stage
JOIN Item ON Item.Title = stage.Title

Answer 2

SQL Server - bcp. , - , script, . bcp , SP .

Answer 3

, , , .

Answer 4

, .NET SqlBulkCopy.