Shulou(Shulou.com)05/31 Report--

This article is about how to copy Image field data to the same field in the new table, the editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

Recently, an upgrade program is being written, which requires that all the photos in an old database be transferred to the new database. For the time being, the old database is called OldDB, the new database is called NewDB, the fields in the new database are [Photo], and the old database is called [Picture]. The following editor will explain how to copy Image field data to the same field in the new table.

How to copy Image field data to the same field in the new table

First read out the data from the old database, and then insert it with insert into:

Insert into [table1] values ("& rs (" Picture ") &")

Later, I found that it didn't work. I thought the data type was wrong, so I changed the code, as follows:

Insert into [table1] values ('"& rs (" Picture ") &"')

Added an extra pair of single quotation marks, the system still prompted an error. The following is the stored procedure:

CENTER > ccid_nobr >

Table width= "400" border= "1" cellspacing= "0" cellpadding= "2" bordercolorlight = "black" bordercolordark = "# FFFFFF" align= "center" >

Tr >

Td bgcolor= "e6e6e6" class= "code" >

Pre > ccid_code >

If exists (select * from dbo.sysobjects

Where id = object_id (N' [dbo]. [sp_textcopy]')

And OBJECTPROPERTY (id, NissIsProcedure') = 1)

Drop procedure [dbo]. [sp_textcopy]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE sp_textcopy (

@ srvname varchar (30)

@ login varchar (30)

@ password varchar (30)

@ dbname varchar (30)

@ tbname varchar (30)

@ colname varchar (30)

@ filename varchar (30)

@ whereclause varchar (40)

@ direction char (1))

AS

DECLARE @ exec_str varchar

SELECT @ exec_str =

'textcopy / S'+ @ srvname +

'/ U' + @ login +

'/ P' + @ password +

'/ D' + @ dbname +

'/ T' + @ tbname +

'/ C' + @ colname +

'/ W "' + @ whereclause +

'"/ F' + @ filename +

'/' + @ direction

EXEC master..xp_cmdshell @ exec_str

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

/ ccid_code > / pre >

/ td >

/ tr >

/ table >

/ ccid_nobr > / CENTER >

This is a statement that calls textcopy to import and export picture data. I originally intended to call the same stored procedure for the new and old databases. The old database first exports the data to the hard disk, and the new database is importing. After the test, it is found that it is not successful, the page does not show the data, and the data can be exported by textcopy test alone, but the call in the program is not successful. I then replace it with the following stored procedure:

CENTER > ccid_nobr >

Table width= "400" border= "1" cellspacing= "0" cellpadding= "2" bordercolorlight = "black" bordercolordark = "# FFFFFF" align= "center" >

Tr >

Td bgcolor= "e6e6e6" class= "code" >

Pre > ccid_code >

If exists (select * from dbo.sysobjects

Where id = object_id (N' [dbo]. [sp_imageio]')

And OBJECTPROPERTY (id, NissIsProcedure') = 1)

Drop procedure [dbo]. [sp_imageio]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

Create proc p_binaryIO

@ servename varchar (30)

-- Server name

@ username varchar (30)

-- user name

@ password varchar (30)

-- password

@ tbname varchar (500)

-- Database.. Table name

@ fdname varchar (30)

-- Field name

@ fname varchar (1000)

-- directory + file name. Use / overwrite: @ filename+_temp during processing

@ tj varchar (1000) =''

-- deal with the conditions. For data import, if the condition contains @ fdname, specify the table name prefix

@ isout bit=1-- 1 export (default), 0 import

AS

Declare @ fname_in varchar 1000

-- bcp handles answer file name

, @ fsize varchar (20)

-- the size of the file to be processed

@ m_tbname varchar (50)

-- temporary table name

, @ sql varchar (8000)

-- get the size of the import file

If @ isout=1

Set @ fsize='0'

Else

Begin

Create table # tb (optional name

Varchar (20), size int

, creation date varchar (10), creation time

Varchar (20)

, last write operation date varchar (10)

Last write operation time varchar (20)

, last visit date varchar (10)

Last access time varchar (20), feature int)

Insert into # tb

Exec master..xp_getfiledetails @ fname

Select @ fsize= size from # tb

Drop table # tb

If @ fsize is null

Begin

Print 'file not found'

Return

End

End

-- generate a data processing response file

Set @ masked tbnamekeeper'

[# # temp'+cast (newid () as varchar (40)) +']'

Set @ sql='select * into'+ @ masked tbnameplate 'from (

Select null as Typ

Union all select 0 as prefix

Union all select'+ @ fsize+' as length

End of union all select null as

Union all select null as format

) a'

Exec (@ sql)

Select @ fname_in=@fname+'_temp'

, @ sql='bcp "'+ @ masked tbnameplate'" out "'+ @ fname_in

+'"/ S"'+ @ servename

+ case when isnull (@ username,'') =''then''

Else'"/ U"'+ @ username end

+'"/ P''+ isnull (@ password,'') +'/ c'

Exec master..xp_cmdshell @ sql

-Delete temporary tables

Set @ sql='drop table'+ @ m_tbname

Exec (@ sql)

If @ isout=1

Begin

Set @ sql='bcp "select top 1'+ @ fdname+' from'

+ @ tbname+case isnull (@ tj,'') when''then''

Else 'where' + @ tj end

+'"queryout"'+ @ fname

+'"/ S"'+ @ servename

+ case when isnull (@ username,'') =''then''

Else'"/ U"'+ @ username end

+'"/ P"'+ isnull (@ password,'')

+'"/ I"'+ @ fname_in+' "'

Exec master..xp_cmdshell @ sql

End

Else

Begin

-- prepare temporary tables for data import

Set @ sql='select top 0'+ @ fdname+' into'

+ @ from tbnamekeeper 'from' + @ tbname

Exec (@ sql)

-- Import data into temporary tables

Set @ sql='bcp "'+ @ masked tbnameplate'" in "'+ @ fname

+'"/ S"'+ @ servename

+ case when isnull (@ username,'') =''then''

Else'"/ U"'+ @ username end

+'"/ P"'+ isnull (@ password,'')

+'"/ I"'+ @ fname_in+' "'

Exec master..xp_cmdshell @ sql

-- Import data into an official table

Set @ sql='update'+ @ tbname

+ 'set' + @ fdname+'=b.'+@fdname

+ 'from' + @ tbname+' aPerm'

+ @ mroomtbnamecake'b'

+ case isnull (@ tj,'') when''then''

Else 'where' + @ tj end

Exec (@ sql)

-Delete temporary tables for data processing

Set @ sql='drop table'+ @ m_tbname

End

-- Delete the data processing answer file

Set @ sql='del'+ @ fname_in

Exec master..xp_cmdshell @ sql

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

/ ccid_code > / pre >

/ td >

/ tr >

/ table >

/ ccid_nobr > / CENTER >

How to copy Image field data to the same field in the new table

The basic principle of this stored procedure is actually the same as the one above. I first test the export, after running in the program, constantly refresh the folder where the exported pictures are saved, and find that the system does produce data, which is stored in the name of file + tmp, but I don't know why, when the system is finished, all the pictures are still missing. This road seems to be impassable.

After the post was sent out, someone replied soon. But the answer to the reply is from the two stored procedures I found. It seems that the problem may not be solved.

But the task was still completed, and in the end, I had no choice but to send a letter of help to a hero I knew. He just provided a way of thinking and wrote it in a streaming way. Following this line of thinking, I changed the code to the following style in the mood of trying:

Set rs2=Server.CreateObject ("Adodb.Recordset")

Sql2= "select top 1 * from [User] order by UserID desc"

Rs2.open sql2,cn,1,3

Rs2 ("Photo") .AppendChunk rs ("Picture1")

Rs2.update

Rs2.close

Set rs2=nothing

The above is how to copy the Image field data to the same field in the new table. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.