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

PowerShell Help
1

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

 

2

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()
3

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:

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.