Solved

BULK EXPORT/INSERT Containing Text Column

Posted on 2004-10-07
7
509 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:ketema
  • 2
  • 2
7 Comments
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12249686
One option is to specify a different field terminator that is very unlikely to be in the text.   ie. a BPC option.

AustinSeven
0
 

Author Comment

by:ketema
ID: 12249718
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
 

Author Comment

by:ketema
ID: 12253315
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
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12256843
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12577879
PAQed with points refunded (250)

modulo
Community Support Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now