Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Clear-Host
- # no these are not my real settings!
- $ServerName = "my.little.server.0800.database.windows.net"
- $targetDatabaseName = "MyLittleDatabase"
- $userName = "bob"
- $Password = "NoPasswords4U!"
- $pathToFiles = "C:\Users\richardlee\mylittleDatabase\dbo\Tables"
- $files = Get-ChildItem $pathToFiles
- Write-Host "There are $($files.Count) create table files to execute." -ForegroundColor DarkMagenta -BackgroundColor Yellow
- foreach ($file in $files){
- $old = Get-Content $file.FullName
- Set-Content -Path $file.FullName -Value "PRINT '$($file.FullName)'"
- Add-Content -Path $file.FullName -Value $old
- }
- ### batch files to sqlcmd
- $date1 = get-date
- sqlcmd -i $files.FullName -S $ServerName -d $TargetDatabaseName -G -U $Username -P $Password -I -y 0 -b -j
- $date2 = get-date
- $taskTime = "Bulk executing files using sqlcmd, task took(HH:MM:SS:MS) " + (New-TimeSpan -Start $date1 -End $date2)
- write-Host $taskTime -ForegroundColor White -BackgroundColor DarkGreen
- ### foreach file execute sqlcmd
- $date1 = get-date
- foreach ($file in $files) {
- sqlcmd -i $file.FullName -S $ServerName -d $TargetDatabaseName -G -U $Username -P $Password -I -y 0 -b -j
- }
- $date2 = get-date
- $taskTime = "Using sqlcmd for each file, task took(HH:MM:SS:MS) " + (New-TimeSpan -Start $date1 -End $date2)
- write-Host $taskTime -ForegroundColor White -BackgroundColor DarkCyan
- ### sql client
- $date1 = get-date
- $userDbCon = New-Object System.Data.SqlClient.SqlConnection
- $userDbCon.ConnectionString = "Server = $ServerName; Database = $targetDataBaseName; Authentication=Active Directory Password; UID = $Username; PWD = $Password;"
- $userDbCon.Open();
- $SqlExecCmd = New-Object System.Data.SqlClient.SqlCommand
- $SqlExecCmd.Connection = $userDbCon
- foreach ($file in $files) {
- $SqlExecCmd.CommandText = Get-Content $file.FullName
- $SqlExecCmd.ExecuteScalar()
- }
- $userDbCon.Close();
- $date2 = get-date
- $taskTime = "Using sqlclient for each file, task took(HH:MM:SS:MS) " + (New-TimeSpan -Start $date1 -End $date2)
- write-Host $taskTime -ForegroundColor White -BackgroundColor DarkMagenta
Add Comment
Please, Sign In to add comment