About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Tuesday, July 01, 2008

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

1 comment:

maxdonati said...

Hy,

first, thank for the post, it was very useful.
I have added -U -P for user and pwd, but i have some difficult on systems with empty pwd. have you got some suggestions?

Max