1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[SQL] Transform and insert millions of records into multiple tables using powershell

Discussão em 'Outras Linguagens' iniciado por Stack, Julho 15, 2021.

  1. Stack

    Stack Membro Participativo

    Retrieved 1 million records from a table using powershell. Need to transform all these 1 million records and then insert into another parent table along with some child tables. I am able to do this using for-each loop (for every record, transform and then insert) but taking tens of hours. Tried the below approach mentioned in the below URL but it didn't help much. Building batch insert statement powershell to sql

    Is there any alternative way to resolve the issue using powershell? Please see the below sample code snippet where dataset from table[0] contains 1 million records which are iterated using ForEach

    $DataSet.Tables[0] | ForEach {
    $VALUE1 = $_.VALUE1
    $VALUE2 = $_.VALUE2
    $VALUE3 = $_.VALUE3
    $VALUE4 = $_.VALUE4
    $InsertQuery = "INSERT INTO PARENT_TABLE([column1],[column2],[column3],[column4]) VALUES (@col1,@col2,@col3,@col4);
    INSERT INTO CHILD_TABLE1([column1],[column2],[column3]) VALUES (@col1,@col2,@col3);
    INSERT INTO CHILD_TABLE2([column1],[column2],[column3]) VALUES (@col1,@col2,@col3);"
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandText = $InsertQuery
    $SqlCmd.Parameters.Add("@col1", $VALUE1) | Out-Null
    $SqlCmd.Parameters.Add("@col2", $VALUE2) | Out-Null
    $SqlCmd.Parameters.Add("@col3", $VALUE3) | Out-Null
    $SqlCmd.Parameters.Add("@col4", $VALUE4) | Out-Null
    $SqlCmd.ExecuteNonQuery() | Out-Null
    }

    Continue reading...

Compartilhe esta Página