1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
behzad razzaqi
  • 1,503
  • 2
  • 18
  • 33

4 Answers4

1

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 : Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function

or use this

SqlBulkCopy.WriteToServer Method (DataTable)

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • thanks for pay attention to my problem,but how can i use bulk from linq query? – behzad razzaqi Jul 06 '15 at 05:45
  • @behzadrazzaqi - it not necessary you stick with linq for all problem, you should go with the solution which provide good way to implement with less complexity and faster insert for your problem – Pranay Rana Jul 06 '15 at 05:49
  • Ok,my data into the table1 and want copy all data two the table2,how can i write that with bulk in the c#? – behzad razzaqi Jul 06 '15 at 05:50
  • @behzadrazzaqi - just check second link which is more suitable for you ..SqlBulCopy that will have example code also... – Pranay Rana Jul 06 '15 at 05:54
  • thanks,best tutorial,but how can i select just name column from table1 with your link tutorial and sqlbulkcopy to table2? – behzad razzaqi Jul 06 '15 at 05:58
0

Try

var bulkCopy = new SqlBulkCopy(connection);
bulkCopy.DestinationTableName = "table2";
bulkCopy.ColumnMappings.Add("Name", "Name");

using (var reader = new EntityDataReader<Table1>(query))
{
    bulkCopy.WriteToServer(reader);
}

EntityDataReader

artm
  • 8,554
  • 3
  • 26
  • 43
0

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);
                    }
Sanjeev S
  • 626
  • 1
  • 8
  • 27
  • i want just insert all name column from table1 to table2,please write that – behzad razzaqi Jul 06 '15 at 05:59
  • (http://stackoverflow.com/questions/17088779/fill-datatable-from-linq-query) this link will help you – Sanjeev S Jul 06 '15 at 06:19
  • i'm write the DataTable dt = query_detail_allrow.CopyToDataTable();,but get error! – behzad razzaqi Jul 06 '15 at 06:29
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/82447/discussion-between-sanjeev-s-and-behzad-razzaqi). – Sanjeev S Jul 06 '15 at 06:51
  • var query_detail_allrow = from p in mainbehzad.TAXBILLCRMTEMPs select p; DataTable dt = query_detail_allrow.CopyToDataTable(); – behzad razzaqi Jul 06 '15 at 06:53
  • write up code and my error:Error 9 The type 'REPORT_MANAGER.TAXBILLCRMTEMP' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable(System.Collections.Generic.IEnumerable)'. There is no implicit reference conversion from 'REPORT_MANAGER.TAXBILLCRMTEMP' to 'System.Data.DataRow'. D:\MASHHAD SOFWARE\ver2\REPORT MANAGER\REPORT MANAGER\TaxCRMBILLLOAD.cs 222 28 REPORT MANAGER – behzad razzaqi Jul 06 '15 at 06:54
-1

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 https://technet.microsoft.com/en-us/library/ms188263%28v=sql.105%29.aspx

jameshwart lopez
  • 2,993
  • 6
  • 35
  • 65