Fast and effective to insert 50,000 folder name into a SQL Server table

Welcome Forums General PowerShell Q&A Fast and effective to insert 50,000 folder name into a SQL Server table

This topic contains 2 replies, has 3 voices, and was last updated by

 
Participant
3 months, 1 week ago.

  • Author
    Posts
  • #110809

    Participant
    Points: 0
    Rank: Member

    Hi!

    I have a 50,000 folder names that uses numbers like 105671, 105672... and I need to insert the folder names into a SQL Server table. It takes almost 1 hours to do this.

    This is my current powershell

     

    $getTxt = Get-ChildItem "$FolderNP" -Directory -name | Out-File -filepath C:\KUMELLEN\NPList.txt

     

    foreach($line in [System.IO.File]::ReadLines("C:\KUMELLEN\NPList.txt"))

    {

     

    $NP_Folder_URL = $WIR_URL_NP+$line+"/"+$line+".htm"

    $queryInsertNP  = "insert into WIDS_ASS_SITE_DOC_PUBURL (SITE_REF, PUB_URL) values ('$line', '$NP_Folder_URL')";

    $Cmd.CommandText = $queryInsertNP;

    # Execute Command

    $Result = $Cmd.ExecuteNonQuery()

    }

    How can I make this more efficient?

    Appreciate your kind advise.

    Thank you.

    Regards.

    David

     

  • #110839

    Participant
    Points: 5
    Rank: Member

    David,

    I think you could gain some efficiency in your script by changing a couple things. 1st, the text file is unnecessary.  You should just iterate over the result of get-childitem or your $getTxt variable.  2nd you should try making one SQL statement and executing it after your loop.  I don't have an environment to test, but I think the script would look something like this.

    foreach($line in Get-ChildItem "$FolderNP" -Directory -name)
    { 
        $NP_Folder_URL = $WIR_URL_NP+$line+"/"+$line+".htm"
        $queryInsertNP += "insert into WIDS_ASS_SITE_DOC_PUBURL (SITE_REF, PUB_URL) values ('$line', '$NP_Folder_URL'); "
    }
    $Cmd.CommandText = $queryInsertNP
    $Result = $Cmd.ExecuteNonQuery()
  • #110842

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    The first question is are you ONLY inserting to this table? If you are, then you can use a SQL bulk insert which can insert millions of records expeditiously:

    http://www.sqlservercentral.com/articles/powershell/65196/
    https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9

    If the columns you are uploading match the columns in the database, you can use some simple code for the insert. If there are additional columns, you may see some unexpected behavior unless you map the columns. I would recommend just getting the insert working first and not trying to do the whole script. Wipe the table, take a CSV that has data and see if you can get the INSERT working properly. When you get it working, you should upload the records in a couple seconds.

The topic ‘Fast and effective to insert 50,000 folder name into a SQL Server table’ is closed to new replies.