Bulk Export from SQL Server into a CSV file

Well, here is a simple query/stored procedure that you can run to bulk export the tables in a database into a csv file.

Just change the “databaseName” in the file to the one you want to point to and also change the location if you wish. Currently it is the C:\ drive.

 

DECLARE @var nvarchar(MAX)

DECLARE curRunning
CURSOR LOCAL FAST_FORWARD FOR
select name from sysobjects where type = 'U'

Open curRunning

Fetch NEXT From curRunning into @var

WHILE @@FETCH_STATUS = 0
BEGIN
    --select 'exec master.dbo.xp_cmdshell ''bcp databaseName.dbo.' + @var + ' out C:\' + @var + '.csv -c -T -t ,'''
    DECLARE @Exec nvarchar(MAX)
    set @Exec = 'exec master.dbo.xp_cmdshell ''bcp databaseName.dbo.' + @var + ' out C:\' + @var + '.csv -c -T -t ,'''
    execute sp_executesql @Exec
FETCH NEXT FROM curRunning into @var
END

close curRunning
DEALLOCATE curRunning

Changing Dynamic SQL to Static SQL

I've been on this project for a couple of weeks now, changing store procedures that use dynamic sql to a static sql. The reason for this change is too speed up the store procedures.

The store procedure might have a dynamic sql statement like:

SET @tsql = 'SELECT * FROM TABLE1
WHERE TABLE1.Name = ' + @Name

IF @ID <> ''
@tsql = @tsql + ' AND TABLE1.ID = ' + @ID

exec sp_executeSql @tSqlQuery

This would be changed to:

SELECT * FROM TABLE1 WHERE TABLE1.Name = @Name
AND( (@ID <> '' AND TABLE1.ID = @ID) OR
(@ID = '' ))

Now came the problem if they dynamically set a column to be sorted:

IF LTRIM(RTRIM(@sortColumn)) <> ''
@tsql = @tsql + 'ORDER BY ' + @sortColumn

This unfortunately had to be solved by making a case statement for all possible columns that are returned. So in this case this table returns only two columns (name and id):

SELECT NAME, ID FROM TABLE1
WHERE TABLE1.Name = @Name AND ((@ID <> ''
AND TABLE1.ID = @ID) OR (@ID = '' ))
ORDER BY
CASE @sortColumn WHEN 'ID' THEN ID ELSE NULL END,
CASE @sortColumn WHEN 'NAME' THEN NAME ELSE NULL END

The reason for the seperate case statements in the example is because it can only return one data type. If NAME is of varchar and ID is of int, then they have to be separated.

Well, that's a very basic and simple run down of what I've been doing. I do run into larger more complex store procedures and other situations. For example, when a dynamic sql statement is using a table name as a variable.

BizTalk Assessment Question: BizTalk 2006 supports what SQL Servers

What versions of Microsoft(R) SQL Server(TM) are supported by Microsoft BizTalk(R) Server 2006? (Choose all that apply.)

• Microsoft SQL Server 6.5
• Microsoft SQL Server 2000
• Microsoft SQL Server 2000 with Service Pack 4
• Microsoft SQL Server 2005
• Microsoft SQL Server 7.0

Answer found: Microsoft SQL Server 2005 or Microsoft SQL Server 2000 with Service Pack 4