Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-11-11 : 10:34:37
|
| Thirumala writes "Here is my Question. Assuming that 'PUBS' database has all these tables likeAUTHORS ,TITLES ,DISCOUNTS etc...I WOULD LIKE TO EXPORT TO A TEXT FILES ALL THESE TABLESWITH THEIR TABLE NAMES. SO, THE TEXT FILES LOOKS LIKE THIS..AUTHORS.TXT , TITLES .TXT ,DISCOUNTS.TXT etc..I KNOW ONE WAY TO DO THIS CREATE A DB CONNECTION PUT a TEXT FILE(Destination) FOR EACH TABLE..IF I HAVE 100 TABLES THAT DTS PACKAGE WILL LOOK SO BAD AND MAINT.. IS VERY DIFFIECULT..I dont want to use BCP alsoAny thoughts ?THANKS IN ADVANCE" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-11 : 11:23:41
|
My thought would be use bcp where it is simple.To do it in a dts package you will have to crate a loop by playing around with the workflow and in an activeX script set the source and destination properties of the export. Not too difficult but considering how easy it is using bcp...If you are exporting to a delimited file you will also need to be using v2000 sp2 and to set the max col size in the activeX script too or it will truncate at 256 chars. (see below ).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 11/11/2002 11:26:32 |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-11-15 : 17:13:05
|
| Here's some VB/ADO pseudo-code.Open a connection to your server.-For each Table in the database--Create a new text file called "TableName.txt"--For each Field in the table---Print each fieldname to the text file + ','--next--print a CR/LF--for each record in the table---for each field in the table----print each fieldvalue to the text file + ','---next---Print a CR/LF--next--close your text file-nextSave this as a VB program and whenever you run it, it will enumerate all tables, and then all the fields from the table definition, and then all the records. This will be quite slow, but will run with no problem and it will always use the latest tables, their latest structures, and their latest data without having to edit the code.Edited by - jsmith8858 on 11/15/2002 17:15:10 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-15 : 17:51:08
|
| Along the lines of Nigel's suggestion to use bcp, you can use the following code:CREATE PROCEDURE ExportAllTables ASDECLARE @sql varchar(8000), @server varchar(128)SET @server='MySQLServer' --change this to match your SQL Server's nameSET @sql='SELECT ''bcp "'' + table_catalog + ''.'' + table_schema + ''.'' + table_name + ''" out "C:\'' + table_name + ''.txt" -S' + @server + ' -T -c'' FROM information_schema.tables WHERE table_type=''BASE TABLE'' AND table_name<>''dtproperties'''EXECUTE master..xp_cmdshell 'bcp "' + @sql + '" queryout C:\exportall.bat -S' + @server + ' -T -c'EXECUTE master..xp_cmdshell 'C:\exportall.bat'This uses xp_cmdshell twice: the first time captures all of the table names and generates the bcp commands necessary to export them to individual files...and it uses bcp to do it! The second call executes the batch file that the first command generated.The code above uses the -T flag in bcp to use a trusted connection, if you use SQL logins you need to use the -U and -P flags instead with the proper user name and passwords. You need to change the procedure accordingly. The same applies to the drive and path for the exported data files, NOT the exportall.bat file.Edited by - robvolk on 11/15/2002 17:55:18 |
|
|
|
|
|
|
|
|
DTS Export to TEXT FILES VERY TOUGH Question