Using SQL Server Bulk Copy Program Mode in IWS
SQL Server implementation
SQL Server provides a feature called BCP Mode, which stands for Bulk Copy Program
Mode. You can use it within the SQL Server
Management Console environment to implement a bulk copy of the contents of a
database table to a text file, and you can also implement a bulk copy of the
contents of a text file to a database table.
For example, you can create a query that uses a ‘bcp SELECT’ component to specify the desired data and source, followed by a ‘queryout’ component to specify an export, followed by the desired destination file and format components. The following example query found on the web uses the default instance of SQL Server, such that only the database, schema and table are specified as the source.
bcp "SELECT CountryRegionCode, Name FROM [AdventureWorks2012].[Person].[CountryRegion]" queryout "C:\Temp\CountryRegion.txt" -T -c –t,’
To specify a named instance, you can add the –S parameter, as shown below, to identify the server and instance name which contains the specified database, schema and table.
‘bcp "SELECT CountryRegionCode, Name
FROM [AdventureWorks2012].[Person].[CountryRegion]" queryout
"C:\Temp\CountryRegion.txt" -T -c -t, -S MyTechMantra\SQL2012'
The parameters are designated as follows:
Queryout: - this option allows one to specify the query to export.
File Name: - Specify the file name after queryout parameter where the query results will be stored.
-T parameter specifies that BCP utility will use trusted connection using integrated security to connect with SQL Server. If you wish to use a SQL Server Login then specify –U and –P
-c parameter specifies that character data type will be used for each field.
-t parameter allows one to specify a field delimiter. To specify comma (“,”) as field delimiter for data fields specify it as (-t,)
-S parameter can be used to specify
server name. For a named instance specify it as –S [SERVERNAME\INSTANCENAME].
You can take advantage of the BCP Mode of
SQL Server in a similar fashion within Indusoft Web Studio.
Indusoft Web Studio implementation
Instead of invoking BCP mode within SQL Server, you can use the BCP
command line program installed with SQL Svr, such that you can use WinExec() to open it, specify the
source and destination with the appropriate command line components, and this
can be implemented from a button in IWS:
Export Command:
$WinExec("bcp Measured_Data out ""c:\temp\export.csv"" -S ""TIM2\SQLEXPRESS"" -d ""VF_1"" -T -c -t "",""",0,1)
Import Command:
$WinExec("bcp Prod_Data in ""c:\temp\TestFileImportData.csv"" -S ""TIM2\SQLEXPRESS"" -d ""VF_1"" -T -c -t "",""",0,1)
These commands will export the table called Measured_Data to
a file called c:\temp\export.csv and import a file called
c:\temp\TestFileImportData.csv into a table called Prod_Data. Change the
command appropriately to use the filenames that you want. TIM2\SQLEXPRESS is the name of my SQL Server
instance. Replace it with the name of
your SQL Server instance. VF_1 is the
name of the database. You will need to
change this if your database name ever changes.
These commands also presume that the Windows user running InduSoft has SQL
permission to read and write these tables.
If this is not true, and you need to use a specific SQL username, then
replace the -T in the command to -U ""username"" -P
""password"" as shown below,
Export Command:
$WinExec("bcp Measured_Data out ""c:\temp\export.csv"" -S ""TIM2\SQLEXPRESS"" -d ""VF_1"" -U ""username"" -P ""password"" -c -t "",""",0,1)
Import Command:
$WinExec("bcp Prod_Data in ""c:\temp\TestFileImportData.csv"" -S ""TIM2\SQLEXPRESS"" -d ""VF_1"" -U ""username"" -P ""password"" -c -t "",""",0,1)
Here, 'username' is the name of a SQL Server user and 'password' is the SQL Server password for that user.
