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

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

Generate a comma seperated list of values in a table

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

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.

• Lock Types for User Data
• SQL Server 2005 Waiting and Blocking Issues