56408

How can I insert all rows from one table into another table with Linq-to-SQL?

Question:

I have two tables in SQL Server.

Table one contains these columns:

1-id 2-name 3-family 4-address

and table two contains these columns:

1-id 2-name

In table one I have 100000 rows and read all record with this query:

var query = (from p in datacontext.table1 select p).toArray();

I want insert all data from up query into the table2, now I use this method:

for(int i = 0; i < query.count(); i++) { table2 beh = new tabl2(); beh.name = query[0].name; datacontext.table2.insertonsubmit(beh); datacontext.submitchange(); }

Is there another way? Thanks.

Answer1:

Making use of Linq to SQL to insert record one by one will take lot of time. Instead of that I Suggest make use of Bulk insert so that your data get insert in one go in less amount of time for that you can make use of of DataTable and OpenXML. for that tutorial is : <a href="http://www.codeproject.com/Articles/417181/Bulk-Insertion-of-Data-Using-Csharp-DataTable-and" rel="nofollow">Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function</a>

or use this

<a href="https://msdn.microsoft.com/en-us/library/ex21zs8x.aspx" rel="nofollow">SqlBulkCopy.WriteToServer Method (DataTable)</a>

Answer2:

Try

var bulkCopy = new SqlBulkCopy(connection); bulkCopy.DestinationTableName = "table2"; bulkCopy.ColumnMappings.Add("Name", "Name"); using (var reader = new EntityDataReader<Table1>(query)) { bulkCopy.WriteToServer(reader); }

<a href="https://msdn.microsoft.com/en-us/library/system.data.entityclient.entitydatareader%28v=vs.110%29.aspx" rel="nofollow">EntityDataReader</a>

Answer3:

Using Bulk insert in System.Data.SqlClient data get insert in to db in less amount of time using the help of datatable

DataTable dt = getData(); SqlBulkCopyOptions options = SqlBulkCopyOptions.Default; using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlconnection, options, null)) { dt.Columns.Add("Id", typeof(long)).SetOrdinal(0); dt = AddDefaultColumn(dt); sqlBulkCopy.BulkCopyTimeout = 300; sqlBulkCopy.DestinationTableName = "tableName"; sqlBulkCopy.WriteToServer(dt); }

Answer4:

Say your table name is table1 and table2 with the columns of id and description you can use

INSERT INTO table2 (id, description) SELECT table2.id, table2.description FROM table1;

furthermore you can add a where

INSERT INTO table2 (id, description) SELECT table2.id, table2.description FROM table1 where table1.id =1;

you can visit this link for more info <a href="https://technet.microsoft.com/en-us/library/ms188263%28v=sql.105%29.aspx" rel="nofollow">https://technet.microsoft.com/en-us/library/ms188263%28v=sql.105%29.aspx</a>

Recommend

  • How to Insert Multiple Rows in a foreach command with LINQ?
  • Cannot convert from table to class
  • Should A repository call another repository? Or should a repository call a service layer?
  • How to make orientDB avoid null
  • Many to Many in Linq using Dapper
  • Multiple Left Join LINQ-to-entities
  • Regex for Specific Tag
  • How can we prepend rows to a react native list-view?
  • Exception creating JSON with LINQ
  • uml Composition relationships to RDF and OWL
  • Redshift Querying: error xx000 disk full redshift
  • Azure table store snapshot/backup capability
  • Linq Merge lists
  • Using Sax parsing to edit and write XML in VB6
  • What's the purpose of QString?
  • xtable package: Skipping some rows in the output
  • Jackson Parser: ignore deserializing for type mismatch
  • Rails Find when some params will be blank
  • Django: Count of Group Elements
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • Change JButton Shape while respecting Look And Feel
  • Deselecting radio buttons while keeping the View Model in synch
  • Problems to linebreak with an int in JLabel
  • How reduce the height of an mschart by breaking up the y-axis
  • output of program is not same as passed argument
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • How can I estimate amount of memory left with calling System.gc()?
  • Rearranging Cells in UITableView Bug & Saving Changes
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • How can I remove ASP.NET Designer.cs files?
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • Sorting a 2D array using the second column C++
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal