Post reply

Export tables to one text file and looping.

  • November 10, 2005 at 11:13 am

    #67927

    Dear Forum,

    I can't work out how to export multiple tables containing the same fields into one text file? Basically I want to append each table to the bottom of the text file? Can this be done in DTS?

    Also I need to join lots of data and export it. However I don't have enough room to join all the data at once in one table and export it. I would therefore like to do it in chunks, writing out a month at time, export the table, delete it, then write out the next month.

    Can anyone give me some clues how I might do this using DTS?

    Any help appreciated as I've been going mad searching the web for an answer.

    Thanks, Bill

  • November 11, 2005 at 1:14 am

    #603372

    Hi. I hope I understand you correctly, as this does not seem a very complex problem.

    Create a DTS package with Text file Destination as target connection.

    You can create multiple extracts from different tables into the same source.

    Use workflow to ensure that processes do not lock the target File whilst in use by other process. You can define columns and column targets in Transformation task properties.

    As for the export tasks, This should not be a problem if you use multiple extracts with different filters and use SQl tasks to delete the tables. workflow links will ensure correct sequencing.

    I don't think this is the most efficient way of solving the problem, but it should work

  • November 11, 2005 at 6:05 am

    #603395

    I havent' tried this in DTS but it should work: Why not do a union query that unions all the tables - as longs as the data types and sizes of all the fields returned are the same it should be fine

    Select X from table 1

    Union

    Select X from table 2

    etc

     

  • November 11, 2005 at 6:29 am

    #603402

    Thanks for the advice Daniel. To get the looping I used the second example on this web page which uses some ActiveX:

    http://sqljunkies.com/Article/A8CB0AFE-D143-4B49-B865-4FBBFEDFCCD7.scuk

    I still had the problem of not being able to write multiple files that append to the bottom of the text file. In the end I exported to an Access table that does append to the bottom. It's not ideal as I now have to export a huge file from Access to text but it does the job.

    Bad dog, Thanks for the suggestion. However, I wanted to union the tables outside of SQL server as each table is very large and our data warehouse is very full. I wish I had the space to do what you suggest!

    Thanks, Bill

  • November 11, 2005 at 12:22 pm

    #603487

    Why use DTS or Access at all.

    I do a lot of extractions to text files from different data sources with ADO and SQL and VB. There you can open a file and write to it from the tables choosen. That can be a loop and it is possible schedule the compiled script. The total file can be very large its just a matter of time to extraxt and write the file.

     

  • November 11, 2005 at 6:04 pm

    #603535

    Have you looked at bcp.exe with the -c -t options?  This should execute the fastest of all the different ways of doing this task.

    -Mike Gercevich

  • November 12, 2005 at 3:03 pm

    #603556

    Here are a couple of articles that demonstrate looping-

    How to loop through a global variable Rowset

    (http://www.sqldts.com/default.aspx?298)

    How to export all tables in a database

    (http://www.sqldts.com/default.aspx?299)

    The problem you will have is that DTS does not support appending files. I normally work around this by writing all files, then combining them afterwards using the DOS copy command. copy file1.txt+file2.txt+file3.txt finalfile.txt

     

    Darren Green
    SQLDTS.com   |   SQLIS.com   |   Konesans Ltd

  • November 13, 2005 at 2:39 am

    #603569

    Mike, I've looked at an introduction to bcp.exe on the web and it seems very straightforward I'll incorporate it into my T-SQL to write out multiple text files for each month. I'll adapt Method 2 in this example:

    http://www.databasejournal.com/features/mssql/article.php/3325701

    I can then use Darren's suggestion to use DOS to copy the final text files together.

    Gosta, is there a straightforward example of your technique on the web?

  • November 15, 2005 at 3:28 am

    #603845

    BCP works great. For the record I've put the T-SQL I used at the bottom of this reply.

    Having written to a text file I can see BCP will be really useful in future.

    Can you use BCP to write from SQL Server to MS Access using T-SQL?

    I can't find anything on the web that describes this.

    Thanks, Bill

    Declare @sql varchar(8000)

    Declare @month varchar(6)

    declare @STR varchar(500)

    SET @month=1

    WHILE @month<=12

    BEGIN

    --Drop table

    If Object_ID('myTABLE') is not NULL Drop TABLE myTABLE

    --Write out table

      SET @sql=' SELECT      *

        INTO  myTABLE

        FROM          myVIEW

        WHERE month='+@month

      EXEC(@SQL)

    --Write table to text file

    set @STR='bcp "SELECT * TABLE" queryout T:\sqlout\test'+@month+'.txt -c -U***** -P***** -S (local) -t \t'

    print @STR

    exec master..xp_cmdshell @STR

    --increment month by 1

    SET @month=@month+1 

    END

    GO

     

  • November 15, 2005 at 4:55 am

    #603856

    Bill, No BCP cannot be used to write to an Access db.  BCP can only read/write SQL tables/views to  other SQL Tables or Flat Files.
     
    -Mike Gercevich

  • November 15, 2005 at 5:09 am

    #603861

    Thanks Mike. All my questions are answered!

    All the best, Bill

  • November 15, 2005 at 5:12 am

    #603863

    Thanks Mike. All my questions are answered!

    All the best, Bill

Viewing 12 posts - 1 through 11 (of 11 total)

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