?
Solved

BULK EXPORT/INSERT Containing Text Column

Posted on 2004-10-07
7
Medium Priority
?
551 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
5 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

850 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