export data from every table

Question

Post reply

export data from every table

Lotus

Ten Centuries

Points: 1170
More actions
October 6, 2005 at 11:22 am

#67053

As part of the upgrade, I would like to export to data out of all the tables for a specific owner to their respective <table name>.txt files and then delete the data from each of these tables.
Can I do this using DTS, I want to run this package from VB.

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

How do you reinsert it???

Why can't you keep it in place?

Lotus Ten Centuries Points: 1170 More actions · Answer 2

I want to reinsert it using DTS as well.

The reason I want to do this, as part of our upgrade, we are making our product's table unicode friendly, which means we find all the char,varchar & text columns and convert them to nchar,nvarchar & ntext respectively.

Right now, with data it is very time consuming. To think other alternatives, I was wondering, if I could delete the data, alter the column's data types and then reinsert the data, if this could improve the time to convert. I just want to time upgrade process with both methods.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Why not just run the alter table command on all the columns at the same time (just throwing ideas, I never had to do this)?

Lotus Ten Centuries Points: 1170 More actions · Answer 4

I don't know if SQL Server will be able to handle all the columns at one time, since I will have lot of text columns and it might need lot of tempdb to run all transactions at one time.

Also, if one convert fails, I need to be able to exit out of the install.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Good points... Maybe a faster solution would be to do it like EM does it. Create a new table with the correct datatypes, then reinsert the data, drop the old table, rename the new one. You might have to do some test to see what version is faster depending on how many columns have to be modififed (9 ints to 1 varchar column VS 9 varchars to 1 int column).

Lotus Ten Centuries Points: 1170 More actions · Answer 6

I have trying to avoid scripting all the triggers if I have to go this route. I could give it a try.

For char & varchar, I run Alter table Alter column

For text columns, I create a temp column with ntext, copy the data, drop the old column and rename the new column with the new column.

For now I wanted to try the DTS method, deleting all the data, alter the table and then re-insert the data back

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I C, as I said I never had to do that so I'll trust your tests to know what's the fastest way .

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 8

For me, DTS is a last resort. You can always put this script in DTS and run that. Use this script at your own risk.

DECLARE @fetch     INT
        , @bcp       VARCHAR (8000)
        , @sql       VARCHAR (8000)
        , @delim     CHAR (1)
        , @owner     VARCHAR (255)
        , @folder    VARCHAR (255)
        , @filename VARCHAR (255)
        , @fullname VARCHAR (255)
        , @tablename VARCHAR (255)
        , @server    VARCHAR (255)
        , @dbname    VARCHAR (255)
SELECT @owner = '{table owner}'
       , @delim = CHAR (2)
       , @folder = '{OS folder name}'
       , @server = @@SERVERNAME
       , @dbname = DB_NAME ()
DECLARE c_tables CURSOR LOCAL
FOR
    SELECT o.name
    FROM   sysobjects o
      JOIN sysusers   u ON o.uid = u.uid
    WHERE o.xtype = 'U'
      AND u.name = @owner
    ORDER BY o.name
FOR READ ONLY
OPEN c_tables
WHILE 1 = 1
BEGIN
    FETCH c_tables INTO @tablename
    SELECT @fetch = @@FETCH_STATUS
    IF @fetch != 0
--THEN
      BREAK
--END IF
    SELECT @filename = @tablename + '.txt'
         , @fullname = @folder + '\' + @filename
    SELECT @bcp = 'BCP "' + @dbname + '.' + @owner + '.' + @tablename + '"'
                + ' OUT "' + @fullname + '"'
                + ' -q' -- quoted identifiers
                + ' -T' -- trusted authentication
                + ' -S' + @server
                + ' -c' -- character format
                + ' -t' + @delim -- each column terminated by ASCII (2)
    PRINT @bcp
    EXEC master..xp_cmdshell @bcp
    SELECT @sql = 'TRUNCATE TABLE [' + @owner + '].[' + @tablename + ']'
    PRINT @sql
    EXEC (@sql)
END
CLOSE      c_tables
DEALLOCATE c_tables

Lotus Ten Centuries Points: 1170 More actions · Answer 9

Thanks for the script to use BCP. Will this script handle if the table's data has non-english data as well?

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 10

By 'non-English', do you mean, a collation that does Unicode? It might work, but I have little experience. The script might need to be tweaked a bit for that.

You might want to consider replacing the '-c -t' options -- wherein I specified character format, each column delimited by ASCII (2) -- with the '-n' option, for "native" formats. I've had trouble with "native" formats before, and have never had trouble using the character formats -- but I *have* had trouble when the column terminator was included in data strings. That's why I use ASCII (2) as a delimiter -- it hardly ever shows up in real data. But you're probably safer with the ' -n' option in general.

I would also take out the TRUNCATE TABLE part until you're sure this script will give you what you need.

Something else to consider is whether any of the tables have IDENTITY columns. All that means, really, is that when you go to put the data back into the tables, you'll need further specifications for telling BCP that your file contains the IDENTITY values. BOL has the scoop.

As usual, the usual disclaimer -- this script is free, as is the advice, and worth every penny. I don't ensure it, and please use it at your own risk.