BULK EXPORT/INSERT Containing Text Column

I would like to know the best way to BULK copy a specific portion of a table (specified by query) into another table in another Database.  The tables are defined exactly the same.  Currently I have been unsuccessful in going to file first because one of the columns is of type text and the import always fails because the text data column invariable always contains the field terminator.  Is there a way to encode the text column? What is the best way to successfully copy the tables? Please provide full syntax if possible.
ketemaAsked:
Who is Participating?
 
moduloConnect With a Mentor Commented:
PAQed with points refunded (250)

modulo
Community Support Moderator
0
 
AustinSevenCommented:
One option is to specify a different field terminator that is very unlikely to be in the text.   ie. a BPC option.

AustinSeven
0
 
ketemaAuthor Commented:
Can you give an example?  THe Text Field contents are Emails, so there is a very wide range of characters, and I haven't found one where it covers everything?  Is it possible to use a unique combination of characters?
0
 
ketemaAuthor Commented:
I found the answer on my own.  The trick is to use the SQL Enterprise manager to create a DTS package that copies the table.  Then to make it executable from a T-SQL script call it as a COM object.:

--Declaring variable
DECLARE @objdts int
 
--Creating object
EXEC sp_OACreate 'DTS.Package', @objdts OUTPUT
 
--Loading DTS storage file to object
EXEC sp_OAMethod @objdts, 'LoadFromStorageFile', NULL, 'C:\MyDTS.dts', ''
 
--Executing DTS package through an object
EXEC sp_OAMethod @objdts, 'Execute'

now that package that was created can be run from an client application that execute T-SQL on the server.
0
 
AustinSevenCommented:
That's an impressive solution (I've made a note of it).   I call DTS packages using xp_cmdshell to call DTSRUN.   It makes me wonder why I didn't suggest to you calling DTS - even just using xp_cmdshell/DTSRUN.   Well done anyway.

AustinSeven
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.