# Step 1 - Output a table to a .dat or txt file. Do the same
thing for other four tables
bcp AdventureWorks2012.Sales.Currency
out BCP1.dat
-S localhost\InstanceName
-T -c
# Step 2 - In SSMS, create a blank table in the database with
the same structure as the original table for inputing. Do the same for other
four tables
USE AdventureWorks2012;
GO
SELECT *
INTO AdventureWorks2012.Sales.BCP1
FROM AdventureWorks2012.Sales.Currency
WHERE 1=2;
# Step 3 -Input the dat or txt file into the table. Do the
same thing for other four tables
bcp AdventureWorks2012.Sales.BCP1
in BCP1.dat
-S localhost\InstanceName
-T -c
# Step 4 - In SSMS, verify that you have 5 BCP tables
SELECT *
FROM sys.sysobjects
WHERE name
like '%BCP%'
# Step 5 - Create a tables.txt file with the five BCP table
names in it as below:
BCP1
BCP2
BCP3
BCP4
BCP5
# Step 6 - check the content of the file in PowerShell:
PS SQLSERVER:\>
get-content -path
C:\Users\Charlie\Documents\tables.txt
# Step 7 - It's better to edit the code in PowerShell ISE at
this point. Output the tables into native text file.
get-content .\tables.txt
| % {$BCPTable=$_; # Create a variable to hodl the BCP Table name
$Path="C:\Users\Charlie\Documents\$BCPTable"
+ "
-n.txt"; # Create a variable to Specify the file location
BCP AdventureWorks2008R2.Sales.$BCPTable OUT
$Path -S LocalHost\R2 -T
-n # This is it! It will bcp out all tables in a single
command. R2 is the instance name
}
# Step 8 - Save the above code as BCPOut.ps1 and execute it at
the right location or specify the BCPOut.ps1 location.
PS C:\users\charlie\documents>
.\bcpout
# Step 9 - We can zip the output file if needed. We need to
download and use the gzip tool. After the zip, five zip files are created for
bcp out.
get-content .\tables.txt
| % {$BCPTable=$_;
$Path="C:\Users\Charlie\Documents\$BCPTable"
+ "
-n.txt";
BCP AdventureWorks2008R2.Sales.$BCPTable OUT
$Path -S LocalHost\R2 -T
-n;
C:\Users\Charlie\Downloads\gzip124xN\gzip.exe
$Path
}
# Step 10 - checking the cost or progress through a status
file (assuming the bcp takes hours to complete).
get-content .\tables.txt
| % {
$BCPTable=$_;
$Path="C:\Users\Charlie\Documents\$BCPTable"
+ "
-n.txt";
$Date=get-Date;
"$Date" + " Starting
BCP: " + "$BCPTable" | Out-File C:\Users\Charlie\Documents\Status.txt
-Append
BCP AdventureWorks2008R2.Sales.$BCPTable OUT
$Path -S LocalHost\R2 -T
-n;
$Date2=get-Date;
$DateDiff
= ($Date2-$Date).Milliseconds
"$Date2" + " Ending BCP:
" + "$BCPTable"
+ " ---
This BCP takes " + $DateDiff +" milliseconds." | Out-File C:\Users\Charlie\Documents\Status.txt -Append
$Date=get-Date;
"$Date" + " Starting
Zip: " + "$BCPTable" | Out-File C:\Users\Charlie\Documents\Status.txt
-Append
C:\Users\Charlie\Downloads\gzip124xN\gzip.exe
$Path
$Date2=get-Date;
$DateDiff
= ($Date2-$Date).Milliseconds
"$Date2" + " Ending Zip:
" + "$BCPTable"
+ " ---
This Zip takes " + $DateDiff +" milliseconds." | Out-File C:\Users\Charlie\Documents\Status.txt -Append
}
<###### Output as below
01/24/2015 13:14:45 Starting BCP: BCP1
01/24/2015 13:14:45 Ending BCP: BCP1 --- This BCP takes 141
milliseconds.
01/24/2015 13:14:45 Starting Zip: BCP1
01/24/2015 13:14:46 Ending Zip: BCP1 --- This Zip takes 14
milliseconds.
01/24/2015 13:14:46 Starting BCP: BCP2
01/24/2015 13:14:46 Ending BCP: BCP2 --- This BCP takes 274
milliseconds.
01/24/2015 13:14:46 Starting Zip: BCP2
01/24/2015 13:14:46 Ending Zip: BCP2 --- This Zip takes 535
milliseconds.
01/24/2015 13:14:46 Starting BCP: BCP3
01/24/2015 13:14:47 Ending BCP: BCP3 --- This BCP takes 436 milliseconds.
01/24/2015 13:14:47 Starting Zip: BCP3
01/24/2015 13:14:47 Ending Zip: BCP3 --- This Zip takes 576
milliseconds.
01/24/2015 13:14:47 Starting BCP: BCP4
01/24/2015 13:14:48 Ending BCP: BCP4 --- This BCP takes 164
milliseconds.
01/24/2015 13:14:48 Starting Zip: BCP4
01/24/2015 13:14:48 Ending Zip: BCP4 --- This Zip takes 90
milliseconds.
01/24/2015 13:14:48 Starting BCP: BCP5
01/24/2015 13:14:48 Ending BCP: BCP5 --- This BCP takes 102
milliseconds.
01/24/2015 13:14:48 Starting Zip: BCP5
01/24/2015 13:14:48 Ending Zip: BCP5 --- This Zip takes 87
milliseconds.
##########>
