Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The best way to do this would be to use SqlBulkCopy to add the data to a temporary table and then feed that data into the stored proc. You will need to write some SQL code to do the processing but the performance benefits of doing it this way should be worth the effort.</p> <p>If you create a new stored proc then you have the added benefit of running all of this code inside the database engine so you will not be switching back and forth between your application and the DB engine.</p> <p>Some Code:</p> <pre><code> var importData = new DataSet(); xmlData.Position = 0; importData.ReadXml(xmlData); using (var connection = new SqlConnection(myConnectionString)) { connection.Open(); using (var trans = connection.BeginTransaction()) { using (var sbc = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, trans) { DestinationTableName = myTableName }) { foreach (DataColumn col in importData.Tables[0].Columns) { sbc.ColumnMappings.Add(col.ColumnName, col.ColumnName); } sbc.WriteToServer(importData.Tables[0]); //table 0 is the main table in this dataset // Now lets call the stored proc. var cmd = new SqlCommand("ProcessDataImport", connection) { CommandType = CommandType.StoredProcedure }; cmd.CommandTimeout = 1200; cmd.ExecuteNonQuery(); trans.Commit(); } connection.Close(); return null; } } </code></pre> <p>Where XmlData is a stream with the Xml data matching your bulk import and myTableName contains the table you want to import into. Rememeber, when doing a bulk copy, the column names must match 100%. Case is important too.</p> <p>The proc would look something like this:</p> <pre><code>CREATE PROCEDURE [ProcessDataImport] AS BEGIN DECLARE @IMPORTCOL INT WHILE EXISTS (SELECT X FROM TEMPTABLE) BEGIN SELECT @IMPORTCOL = (SELECT TOP 1 COLUMN1 FROM TEMPTABLE) EXEC DOTHEIMPORT @IMPORTCOL DELETE FROM TEMPTABLE WHERE COLUMN1 = @IMPORTCOL END END </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POreducing insert statement time when dealing with large amount of data
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGraymatter
    1. USGraymatter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POreducing insert statement time when dealing with large amount of data
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COcan u provide an example or a link that can be useful to read ? please
      singulars
      1. PO
      1. USSora
    2. COI will update the answer with some code.
      singulars
      1. PO
      1. USGraymatter
    3. COwhat should be the DestinationTableName ? bcs in my case i don't know what is the Table Name it's a stored Procedure it can contains multiple Tables
      singulars
      1. PO
      1. USSora
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload