Post reply

export many tables in just one dts

  • November 8, 2006 at 12:35 pm

    #367987

    hi sql group

    i have a big trouble , i need export 400 tables to text file , with

    column delimited (pipe between every field and add " only in char field ) i used bcp but this utility not export the headers so i need this info too.

    so i have to use DTS but how can created only 1 dts ???

    thks

  • November 8, 2006 at 12:40 pm

    #670690

    Why not do a backup of the db and restore on the other server?

    What do you need to accomplish exactly?

  • November 9, 2006 at 9:20 am

    #670878

    Gaby,

    You can use DTS, but it's going to be pretty tedious.  You might be better off using the bcp utility.  You could create a bcp command for each table, put them in a batch file, and run it all at once.

    Greg

    Greg

  • November 9, 2006 at 2:01 pm

    #670930

    hi i cant use a backup because i have a lot tables arround 1,200 tables! so i need only export 400 but its imposible do 400 dts , im trying to do this since VB but my code isnt run neither

  • November 9, 2006 at 2:12 pm

    #670935

    instead of comma delimited, can you use INSERT INTO TABLEXXX instead?

    if you can,Narayana Vyas Kondreddi has an excellent procedure to do that that he has shared: http://vyaskn.tripod.com/code.htm#inserts

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 9, 2006 at 2:34 pm

    #670942

    this procedure export to text file ???

  • November 10, 2006 at 9:09 am

    #671121

    I did this once - except it was the other way, I had to load data from text file into database.

    I create a DTS package to load 1 file to 1 table (in this case to bcp one table to one text file.) I created Global variables for the text file and table name.

    I created a table containing all the files with the corresponding table.

    CREATE TABLE TableA (TextFileName VARCHAR(100),

    DBName VARCHAR(100),

    TableName VARCHAR 256))

    Something liked this. I put all the files that needed to be loaded in the table.

    I wrote a store procedure to read TableA and using a loop to run the DTS Package one by one to load the text file.

    EXEC master..xp_cmdshell ''DTSRun /S "' + @ServerName + '" /E /N "' + @PackageName + '" /W "0" ' + @GlobalVariable + ''''

    @GlobalVariable is used to put the right text file and table in the Dts package.

Viewing 7 posts - 1 through 7 (of 7 total)

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