Bulk Insert

UtilSQL.DML.DBExecNonQueryText("TRUNCATE TABLE foo;", "bar"); using (SqlDataReader portal = UtilSQL.DML.DBExecReader("EXEC myproc", "mate")) { using (SqlConnection conn = UtilSQL.DML.GetSQLConnString("bar")) { conn.Open(); /// Note: for SqlBulkCopy to work the columns must be identically named /// and typed and all fields in the record must be copied or else you /// get a useless error about "LocaleID" /// /// Turns out that using SQLBulkCopy in the middle tier is faster than an /// insert using linked servers even if the data is prepped in a temp table /// first SqlBulkCopy bulk = new SqlBulkCopy(conn); bulk.DestinationTableName = "destTable"; // UID Username LastName FirstName Email isAdmin Inactive CostCenter Descr Bud_Read Bud_Write Facility SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("UID", "UID"); SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("Username", "Username"); SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("LastName", "LastName"); SqlBulkCopyColumnMapping mapping4 = new SqlBulkCopyColumnMapping("FirstName", "FirstName"); SqlBulkCopyColumnMapping mapping5 = new SqlBulkCopyColumnMapping("Email", "Email"); SqlBulkCopyColumnMapping mapping6 = new SqlBulkCopyColumnMapping("isAdmin", "isAdmin"); SqlBulkCopyColumnMapping mapping7 = new SqlBulkCopyColumnMapping("Inactive", "Inactive"); SqlBulkCopyColumnMapping mapping8 = new SqlBulkCopyColumnMapping("CostCenter", "CostCenter"); SqlBulkCopyColumnMapping mapping9 = new SqlBulkCopyColumnMapping("Descr", "Descr"); SqlBulkCopyColumnMapping mapping10 = new SqlBulkCopyColumnMapping("Bud_Read", "Bud_Read"); SqlBulkCopyColumnMapping mapping11 = new SqlBulkCopyColumnMapping("Bud_Write", "Bud_Write"); SqlBulkCopyColumnMapping mapping12 = new SqlBulkCopyColumnMapping("Facility", "Facility"); bulk.ColumnMappings.Add(mapping1); bulk.ColumnMappings.Add(mapping2); bulk.ColumnMappings.Add(mapping3); bulk.ColumnMappings.Add(mapping4); bulk.ColumnMappings.Add(mapping5); bulk.ColumnMappings.Add(mapping6); bulk.ColumnMappings.Add(mapping7); bulk.ColumnMappings.Add(mapping8); bulk.ColumnMappings.Add(mapping9); bulk.ColumnMappings.Add(mapping10); bulk.ColumnMappings.Add(mapping11); bulk.ColumnMappings.Add(mapping12); bulk.WriteToServer(portal); bulk.Close(); conn.Close(); recCountPortal = UtilSQL.DML.DBExecScalarInt64("SELECT COUNT (*) FROM destTable", "bar"); } } this.lblPortal.Text = recCountPortal.ToString() + " records from portal.";