I have suddenly faced a problem with moving some records in a SQL Server database from one table to another, using LINQ to SQL. Is it possible to write in LINQ to SQL a query just as simple as this:
INSERT INTO Table2 SELECT * FROM Table1 WHERE Selected = 1 GO DELETE FROM Table1 WHERE Selected = 1 GO without using loops and collections?
You absolutely don't need LINQ here. Consider performance & maintainability implications of issuing two SQL statements (or invoking a sproc) versus having to load potentially thousands of objects and then saving them back again.
Resort to SqlCommands. That will be your best choice.
I'm not aware of a way to move rows in Linq2Sql without a loop. :)
This kind of thing is much easier in SQL. You can use the output into clause to move rows in a single statement:
delete from Table1 output deleted.id, deleted.name into Table2 where Selected = 1 
THis is just a simple insert and delete method which should written together.
One insert necessary fields to second table and when its finished, the second statement will remove the fields from first Table
