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:
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
Post a Comment