Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

BULK EXPORT/INSERT Containing Text Column

Posted on 2004-10-07
7
Medium Priority
?
549 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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…

610 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