Data script

[MC]

I am writing a script to retrieve data records of MarinLif table. The
problem is that i have a column MarinLif_Pictur e of type image so the
insert is wrong in this way. How do i correct it?
/*****MarinLIf TAble *************** *****/
DECLARE @ID INT
DECLARE @typeID INT
DECLARE @NAME NVARCHAR(50)
Declare @scName nvarchar(50)
Declare @distribution nvarchar(600)
Declare @maxSize nvarchar(200)
Declare @env nvarchar(200)
Declare @climate nvarchar(200)
Declare @country nvarchar(2000)
Declare @desc nvarchar(4000)
Declare @pic image

DECLARE CURS CURSOR STATIC FOR
SELECT MarinLIf_ID, MarinLIfTyp_ID, MarinLif_name,
MarinLIf_ScName ,MarinLIf_Distr ibution,
MarinLIf_MaxSiz e,MarinLIf_Env, MarinLIf_climat e,MarinLIf_Coun try,MarinLIf
_Desc,MarinLIf_ Pic
FROM MarinLif
OPEN CURS
FETCH NEXT FROM CURS INTO @ID,@typeID,@NA ME, @scName, @distribution,
@maxSize, @env, @climate,
@country, @desc, @pic
PRINT 'SET IDENTITY_INSERT MarinLif ON'
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'INSERT INTO MarinLif (MarinLIf_ID, MarinLIfTyp_ID, MarinLif_name,
MarinLIf_ScName ,MarinLIf_Distr ibution,
MarinLIf_MaxSiz e,MarinLIf_Env, MarinLIf_climat e,MarinLIf_Coun try,MarinLIf
_Desc,MarinLIf_ Pic)
VALUES (' + convert(varchar ,@ID) + ','
+ convert(varchar ,@typeID) + ','
+ '''' + @NAME + '''' +
+ '''' + @scName + '''' + ','
+ '''' + @distribution + '''' + ','
+ '''' + @maxSize + '''' + ','
+ '''' + @env + '''' + ','
+ '''' + @climate + '''' + ','
+ '''' + @country + '''' + ','
+ '''' + @desc + '''' + ','
+ '''' + @climate + '''' + ','
+ '''' + @pic + '''' + ')'
FETCH NEXT FROM CURS INTO @ID,@typeID,@NA ME, @scName, @distribution,
@maxSize, @env, @climate,
@country, @desc, @pic
END
PRINT 'SET IDENTITY_INSERT MarinLif OFF'
CLOSE CURS
DEALLOCATE CURS

I am trying to have a script with data that i have in a table for the
purpose of inserting this data in another database under the same table
name. The output of my script is just print statements. I will save
these statements and later on i will execute them on the other database
table.
The problem is with the image and text. Can u give me an example on how
to retrieve data from the image for my script?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

MC (an*******@disc ussions.microso ft.com) writes:

I am writing a script to retrieve data records of MarinLif table. The
problem is that i have a column MarinLif_Pictur e of type image so the
insert is wrong in this way. How do i correct it?
...
I am trying to have a script with data that i have in a table for the
purpose of inserting this data in another database under the same table
name. The output of my script is just print statements. I will save
these statements and later on i will execute them on the other database
table.
The problem is with the image and text. Can u give me an example on how
to retrieve data from the image for my script?

You can't assign to image variables, so this approach is not going
to work.

You are probably better off using BCP, a command-line which is designed
for importing and exporting data. In this case you could try:

bcp yourdb..MarinLi f out MarinLif.bcp -N -T -S source_server
bcp yourotherdb..Ma rinLif in MarinLif.bcp -N -T -S target_server

-N here means that you are using native datatypes with Unicode. -T is for
trusted connection. -S specifies the server.

For -N to work, the tables must be identical, including column order.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dan Guzman]

To add to Erland's response, if both databases are on the same server, you
can use the INSERT INTO... SELECT syntax:

INSERT INTO MyOtherDatabase .dbo.MarinLif
(
MarinLIf_ID,
MarinLIfTyp_ID,
MarinLif_name,
MarinLIf_ScName ,
MarinLIf_Distri bution,
MarinLIf_MaxSiz e,
MarinLIf_Env,
MarinLIf_climat e,
MarinLIf_Countr y,
MarinLIf_Desc,
MarinLIf_Pic
)
SELECT
MarinLIf_ID,
MarinLIfTyp_ID,
MarinLif_name,
MarinLIf_ScName ,
MarinLIf_Distri bution,
MarinLIf_MaxSiz e,
MarinLIf_Env,
MarinLIf_climat e,
MarinLIf_Countr y,
MarinLIf_Desc,
MarinLIf_Pic
FROM MyDatabase.dbo. MarinLif

--
Hope this helps.

Dan Guzman
SQL Server MVP

"MC" <an*******@disc ussions.microso ft.com> wrote in message
news:40******** ************@ne ws.newsgroups.w s...

I am writing a script to retrieve data records of MarinLif table. The
problem is that i have a column MarinLif_Pictur e of type image so the
insert is wrong in this way. How do i correct it?
/*****MarinLIf TAble *************** *****/
DECLARE @ID INT
DECLARE @typeID INT
DECLARE @NAME NVARCHAR(50)
Declare @scName nvarchar(50)
Declare @distribution nvarchar(600)
Declare @maxSize nvarchar(200)
Declare @env nvarchar(200)
Declare @climate nvarchar(200)
Declare @country nvarchar(2000)
Declare @desc nvarchar(4000)
Declare @pic image

DECLARE CURS CURSOR STATIC FOR
SELECT MarinLIf_ID, MarinLIfTyp_ID, MarinLif_name,
MarinLIf_ScName ,MarinLIf_Distr ibution,
MarinLIf_MaxSiz e,MarinLIf_Env, MarinLIf_climat e,MarinLIf_Coun try,MarinLIf
_Desc,MarinLIf_ Pic
FROM MarinLif
OPEN CURS
FETCH NEXT FROM CURS INTO @ID,@typeID,@NA ME, @scName, @distribution,
@maxSize, @env, @climate,
@country, @desc, @pic
PRINT 'SET IDENTITY_INSERT MarinLif ON'
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'INSERT INTO MarinLif (MarinLIf_ID, MarinLIfTyp_ID, MarinLif_name,
MarinLIf_ScName ,MarinLIf_Distr ibution,
MarinLIf_MaxSiz e,MarinLIf_Env, MarinLIf_climat e,MarinLIf_Coun try,MarinLIf
_Desc,MarinLIf_ Pic)
VALUES (' + convert(varchar ,@ID) + ','
+ convert(varchar ,@typeID) + ','
+ '''' + @NAME + '''' +
+ '''' + @scName + '''' + ','
+ '''' + @distribution + '''' + ','
+ '''' + @maxSize + '''' + ','
+ '''' + @env + '''' + ','
+ '''' + @climate + '''' + ','
+ '''' + @country + '''' + ','
+ '''' + @desc + '''' + ','
+ '''' + @climate + '''' + ','
+ '''' + @pic + '''' + ')'
FETCH NEXT FROM CURS INTO @ID,@typeID,@NA ME, @scName, @distribution,
@maxSize, @env, @climate,
@country, @desc, @pic
END
PRINT 'SET IDENTITY_INSERT MarinLif OFF'
CLOSE CURS
DEALLOCATE CURS

I am trying to have a script with data that i have in a table for the
purpose of inserting this data in another database under the same table
name. The output of my script is just print statements. I will save
these statements and later on i will execute them on the other database
table.
The problem is with the image and text. Can u give me an example on how
to retrieve data from the image for my script?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!