January 13, 2004 at 9:25 am
how do i copy a table with text data type from one server to another.
dts has problems with truncating the text field.
January 14, 2004 at 1:38 am
Probably you could set up the other server as a "Linked Server" and use SELECT INTO.
SELECT * INTO linkedserver.database.schema.TextTable FROM TextTable
Or create a copy of the table and use regular insert. ( the SELECT INTO doesnt create an exact schema copy of original table. column defaults, indexes, pk's ... are not duplicated )
/rockmoose
You must unlearn what You have learnt
January 14, 2004 at 6:59 am
I believe I picked this up as one of the scripts submitted to the site;
there's an export and inport function below; i never tested this myself:
--**************************************
--
-- Name: Procedure to Export/Import Imag
-- es In/From SQL SERVER
-- Description:the procedure is used as
-- an Interface to the TextCopy Utility sup
-- plied with SQL SERVER in order to simpli
-- fy handling of BLOBS (images/docs)
-- By: Eli Leiba
--
-- Inputs:@runpath varchar(100), -- text
-- Copy Location
@srvrvarchar(50), -- server TO LOAD
@db varchar(50), -- DATABASE TO LOAD
@usrvarchar(50), -- login USER
@pwdvarchar(50), -- login password
@tblvarchar(50), -- TABLE TO load/unload
@colvarchar(50), -- COLUMN TO load/unload
@whrvarchar(200), -- WHERE clause
@filvarchar(100), -- filename including path
@modchar(1) -- I FOR LOAD INTO Sql
--
-- Assumes:-- usage (assuming TextCopy.e
-- xe is in c:\)
CREATE TABLE pic (pic_id int,picture image)
INSERT INTO pic VALUES (1,null)
UPDATE pic SET picture = 'xx'
-- Inserting image
EXEC sp_imp_exp_images 'c:\textCopy.exe',
'PCN1943',
'PUBS',
'sa',
'sa',
'pic',
'picture',
'"where pic_id = 1"',
'c:\pic.jpg',
'I'
-- Extracting Image
EXEC sp_imp_exp_images 'c:\textCopy.exe',
'PCN1943',
'PUBS',
'sa',
'sa',
'pic',
'picture',
'"where pic_id = 1"',
'D:\pic.jpg',
'O'
--
-- Side Effects:Import/Export of BLOBS
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- http://www.1SQLStreet.com/xq/ASP/txtCodeId.498/
-- lngWId.5/qx/vb/scripts/ShowCode.htm--for details.--**************************************
--
CREATE PROCEDURE sp_imp_exp_images
(@runpath varchar(100), -- textCopy Location
@srvrvarchar(50), -- server TO LOAD
@db varchar(50), -- DATABASE TO LOAD
@usrvarchar(50), -- login USER
@pwdvarchar(50), -- login password
@tblvarchar(50), -- TABLE TO load/unload
@colvarchar(50), -- COLUMN TO load/unload
@whrvarchar(200), -- WHERE clause
@filvarchar(100), -- filename including path
@modchar(1)) -- I FOR LOAD INTO Sql , O FOR output FROM SQL
AS
DECLARE @cmd varchar(1000)
SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil+ ' /' + @mod
EXEC Master..xp_cmdShell @cmd
GO
Lowell
January 14, 2004 at 7:27 am
thanks lowell,
this looks like it will help for the image files but i am still troubled by a description field (text datatype).
we are migrating our backend web database to a new structure, and the product description field contains formatted pages of text.
We resorted to changing the datatype to a varchar(8000), migrating the data, and then changing back to a text datatype. We do lose some special characters (ie. an apostrophe (') turns into a (?)) but if the text is longer than 8000 I risk truncation of data.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy