Solved

BULK EXPORT/INSERT Containing Text Column

Posted on 2004-10-07
7
520 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

773 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