Microsoft SQL Server

Tags:
• programming
• sql

Bulk copying

The bcp command-line utility is useful for bulk-copying data when using Microsoft SQL server.
Its documentation/download link can be found here: https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15#remarks
Example invocation: (using Azure AD login with -G and -T)

bcp dbo.MYTABLE out mytable.csv -c -t ',' -S database.server.com -d some_database -G -T

Format files

Format files allow you to ensure that data is transferred in its proper format to and from a database. For example, two different columns in the same table might use two different collations (encodings), and so specifying just one text encoding for the entire input/output file will lead to errors.

You can create a format file for a table with format nul -f format.xml -x, for example:

bcp dbo.MYTABLE format nul -f mytable-csvformat.xml -x -c -t ',' -S database.server.com -d some_database -G -T

This creates a format file mytable-csvformat.xml, which also captures the options -c and -t ',' inside the file. We could then use in mytable_new_entires.csv -f mytable-csvformat.xml to ensure that the data in the file mytable_new_entires.csv is imported in the exact same way. For example:

bcp dbo.MYTABLE in mytable_new_entires.csv -f mytable-csvformat.xml -S database.server.com -d some_database -G -T

An example format file might look like:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="254" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="24"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="24"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="80" COLLATION="Finnish_Swedish_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="SHORT_DESCRIPTION" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="TIME_OF_CREATION" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="4" NAME="LAST_CHANGE" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="5" NAME="NAME" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

Code Page conversion

Without a format file, bcp will use the client's code page to read and write to the input/output file. To turn this off, specify -C RAW (-C OEM is the default value).
If you specify a proper format file with the -f option this shouldn't be a problem, though!

Bulk-exporting with a query

bcp supports exporting with a query. To use this option, you can specify an SQL query within double quotes after the schema name. For example:

$QUERY = "SELECT TOP (100) ID,TIME_OF_CREATION FROM dbo.MYTABLE"
bcp "$QUERY" queryout result.csv -c -C RAW -t ',' -S database.server.com -d some_database -G -T

Lessons learned

If your goal is to generate lots of dummy rows for testing purposes, and some columns contain large amounts of data, rather than filling those large columns with lots of data in a file and then uploading it with BCP, it may be a smarter idea to give the large columns a nul value in the file and filling those columns with data after uploading using an UPDATE statement.

Cache

If the cache is causing issues with a query, you can find the cache entry for a specific query like so:

SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE '%SELECT Column1, Column2%'

Where %SELECT Column1, Column2% is a part of your query text. The % is similar to .* in regular expressions, matching any number of any following characters.

In the results are the matching texts along with their plan handles. You can pass the plan handle for a query to DBCC FREEPROCCACHE like so:

DBCC FREEPROCCACHE (0x06000D008F3F34368067414F0400000001000000000000000000000000000000000000000000000000000000);

This clears the cache entries for this cached plan.