How to make "INSERT INTO table1 (...) SELECT (...) FROM table2" in LINQ?

Question

How to write the equivalent of LINQ to SQL:

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

thanks

Answer 1

Since you are not returning any results, just use a DataContext.ExecuteCommand()low level 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 2

LINQ is a query language, so it does not perform updates or inserts. However, the LINQ to SQL object model has CUD processing methods:

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 is that it requires two separate SQL transactions instead of one - one for selection and one for insertion.

Answer 3

,

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

:

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