How to do “INSERT INTO table1 (…) SELECT (…) FROM

Question

How do I write a LINQ to SQL equivalent of:

INSERT INTO Table1 (field1, field2, field3)
SELECT field1, field2, field3
FROM Table2
WHERE (field1= @field1)

Thanks

Answer 1

If field of both the tables is same then, use

insert into table1 select * from table2 where table2.field1='xyz';

in place of:

INSERT INTO Table1 (field1, field2, field3)
SELECT field1, field2, field3
FROM Table2
WHERE (field1= @field1)

Answer 2

Since you aren't returning any results, just use the low-level DataContext.ExecuteCommand() method:

using (MyDataContext dc = new MyDataContext())
{
    dc.ExecuteCommand(@"
        INSERT INTO Table1 (field1, field2, field3)
        SELECT field1, field2, field3
        FROM Table2
        WHERE (field1= {0})
        ",
        field1);
}

Answer 3

LINQ is a querying language, so it doesn't do updates or inserts. However -the LINQ to SQL entity object model has methods for handling CUD:

using(MyDataContext dc = new MyDataContext())
{
    //select the source entities from Table2
    var Table2Entities = (from e in dc.Table2 where e.Field1 == "value" select e);

    //for each result, create a new Table1 entity and attach to Table1
    Table2Entities.ForEach(t2e => dc.Table1.InsertOnSubmit(
        new Table1Entity {
            Field1 = t2e.Field1,
            Field2 = t2e.Field2,
            Field3 = t2e.Field3
        });

    //submit the changes
    dc.SubmitChanges();
}

The real difference here is that it requires two separate SQL transactionsinstead of one - one to select, and one to insert.