SQL Tips

Various SQL oriented tips discovered during work, written down to remember them.

BCP

Export data to a file

  • bcp <dbname>..<tablename> out <filename>.dat /S<servername> /T /E /t# /c
  • bcp “<select query, with <dbname>..<tablename»” queryout <filename>.dat /S<servername> /T /E /t# /c

Import data from a file

  • sqlcmd -Q“DELETE FROM <tablename>” -S<servername> -d<dbname> -E
  • bcp <dbname>..<tablename> in <filename>.dat /S<servername> /T /E /t# /c

Comma Seperated List

Generate a comma seperated list of values in a table 

DECLARE @list NVARCHAR(MAX);
SELECT @list = COALESCE(@list + ',', '') + <column> FROM <table>;
PRINT @list;

Prevent Table X Lock

Prevent the database from taking an exclusive table lock 

BEGIN TRANSACTION;
SELECT * FROM <table> WITH (XLOCK, HOLDLOCK) WHERE 1 = 2

Run this in a separate thread without doing a commit. It will prevent the database from escalating key/page locks to table locks.