Running large sql server file via command prompt + sql server bulk copy

Sql via command line

C:\Users\Pahuja Saurabh\Desktop\harinder>sqlcmd -S PAHUJSAU1\SQLEXPRESS -i R

eferencesTables17.sql

PS C:\Users\Pahuja Saurabh\DbScriptFolder> sqlcmd -S 981.143.99.95 -U peritias_demouser -P 'pwd' -i DataScript13Jan18.sql -o

C:\Temp\SqlQueryOutput.txt

sqlcmd -S localhost\sqlsrv2012 -d AdventureWorks2012 -i C:\DataFiles\EmployeeQuery.sql -o C:\DataFiles\Employees.txt

To specify a username, add the -U switch followed by the username. To include a password, add the -P switch followed by the password. If you enter the username option without the password option, you’ll be prompted for a password.

https://www.red-gate.com/simple-talk/sql/sql-tools/sql-server-sqlcmd-basics/

SQL server bulk copy sample

private static void CreateTableInDataBaseOfProductDataUploaded(BOMDataContext context, DataTable productDataTable, string productDataTempTableNameInSql)

        {

            using (var con = new SqlConnection(context.Connection.ConnectionString))

            {

                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(context.Connection.ConnectionString,

                    SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepIdentity))

                {

                    sqlBulkCopy.DestinationTableName = "ProductDataTableTemplate";

                    productDataTable.AcceptChanges();

                    foreach (var columnsMapping in AppConstant.ProductBoMDataTableColumnMapping)

                    {

                        sqlBulkCopy.ColumnMappings.Add(columnsMapping.Value, columnsMapping.Value);

                    }

                    sqlBulkCopy.ColumnMappings.Add(

                        AppConstant.Constant.ROWNUMBERCOLUMNINPRODUCTDATATABLE,

                        AppConstant.Constant.ROWNUMBERCOLUMNINPRODUCTDATATABLE);

                    sqlBulkCopy.ColumnMappings.Add(

                        AppConstant.Constant.INSTANCEIDCOLUMNINPRODUCTDATATEMPTABLE,

                        AppConstant.Constant.INSTANCEIDCOLUMNINPRODUCTDATATEMPTABLE);

                    con.Open();

                    sqlBulkCopy.WriteToServer(productDataTable);

                    con.Close();

                }

            }

        }

public static Dictionary<string, string> ProductBoMDataTableColumnMapping

            = new Dictionary<string, string>() {

                { "Column1", "SubAssemblyId"},

                { "Column2", "ComponentId"},

                { "Column3", "KeyStepId"},

                { "Column4", "SubAssembly" },

                { "Column5", "Component" },

                { "Column6", "KeyStep" },

                { "Column7", "Quantities" },

                { "Column8", "SupplierMaterialUnit" },

                { "Column9", "SupplierLaborUnit" },

                { "Column10", "SupplierSetupUnit" },

                { "Column11", "SupplierDirectTotal" },

                { "Column12", "SupplierOHBurdenUnit" },

                { "Column13", "LowestCurrentQuote" },

                { "Column14", "AnalysisMaterialUnit" },

                { "Column15", "AnalysisLaborUnit" },

                { "Column16", "AnalysisSetupUnit" },

                { "Column17", "AnalysisDirectTotal" },

                { "Column18", "AnalysisOHBurdenUnit" },

                { "Column19", "ShouldCostOfBoM" }

            };