Exporting Database tables to Text at the same time

Question

Post reply

Exporting Database tables to Text at the same time

gjuarez

Old Hand

Points: 395
More actions
July 18, 2016 at 7:33 pm

#311843

I have around 2000 tables i need to export to text files. I tried using the Export Wizard which works if you are doing 1 table at a time. Try to export table in an excel file but i have tables bigger than what Excel can handle. How else can i export several tables to text at once. Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 1

Do the tables all have the same columns?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gjuarez Old Hand Points: 395 More actions · Answer 2

gjuarez

Old Hand

Points: 395

July 18, 2016 at 9:21 pm

#1890752

No, different tables

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 3

If it were me, I'd write a loop to step through the tables to create a BCP command that did a SELECT * from each table and then use xp_CmdShell to execute it. I recommend using TABs for delimiters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63503 More actions · Answer 4

Is this close?

-- create a cursor to loop over this result set

/* create a cursor to loop through each table... */

DECLARE @CmdLine AS NVARCHAR(200);

DECLARE @CmdCursor AS CURSOR;

SET @CmdCursor = CURSOR FORWARD_ONLY FOR

SELECT 'bcp ' + '"SELECT * FROM ' +

QUOTENAME(DB_NAME())

+ '.'

+QUOTENAME(SCHEMA_NAME(schema_id))

+ '.'

+QUOTENAME(name)

+ '"'+' queryout'+' '

+ 'c:\' + name + '.csv -c -t, -T -S '

+ @@servername

FROM sys.objects

WHERE TYPE='u'

AND is_ms_shipped=0

AND name <> 'sysdiagrams';

OPEN @CmdCursor;

FETCH NEXT FROM @CmdCursor INTO @CmdLine;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @CmdLine;

EXEC xp_CmdShell @CmdLine;

FETCH NEXT FROM @CmdCursor;

END

CLOSE @CmdCursor;

DEALLOCATE @CmdCursor;

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 5

Have a look at this link

http://stackoverflow.com/questions/26302633/ssis-export-multiple-sql-server-tables-to-multiple-text-files

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 6

pietlinden (7/18/2016)
Is this close?
-- create a cursor to loop over this result set
/* create a cursor to loop through each table... */
DECLARE @CmdLine AS NVARCHAR(200);
DECLARE @CmdCursor AS CURSOR;
SET @CmdCursor = CURSOR FORWARD_ONLY FOR
SELECT 'bcp ' + '"SELECT * FROM ' +
QUOTENAME(DB_NAME())
+ '.'
+QUOTENAME(SCHEMA_NAME(schema_id))
+ '.'
+QUOTENAME(name)
+ '"'+' queryout'+' '
+ 'c:\' + name + '.csv -c -t, -T -S '
+ @@servername
FROM sys.objects
WHERE TYPE='u'
AND is_ms_shipped=0
AND name <> 'sysdiagrams';
OPEN @CmdCursor;
FETCH NEXT FROM @CmdCursor INTO @CmdLine;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @CmdLine;
EXEC xp_CmdShell @CmdLine;
FETCH NEXT FROM @CmdCursor;
END
CLOSE @CmdCursor;
DEALLOCATE @CmdCursor;

I believe that nails it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gjuarez Old Hand Points: 395 More actions · Answer 7

Thank you for the answers posted. I am a bit new on scripting Cursors so i was wondering how to use a character for field terminator instead of a comma, like "§".

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 8

gjuarez (7/19/2016)
Thank you for the answers posted. I am a bit new on scripting Cursors so i was wondering how to use a character for field terminator instead of a comma, like "§".

The field terminator has nothing to do with cursors and everything to do with BCP. Please see the following URL and look for the -t argument for how to use it to change the field terminator. I'd do it for you but want to inspire you to use existing documentation because you're the one that needs to support this. 😉

https://msdn.microsoft.com/en-us/library/ms162802(v=sql.120).aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)