In MSSQL, there is an image field in the table. How do I insert an image?

Image Type

Method:
1. creation process
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 (255)
Select @ exec_str =
'Textcopy/s' + @ srvname +
'/U' + @ login +
'/P' + @ password +
'/D' + @ dbname +
'/T' + @ tbname +
'/C' + @ colname +
'/W "' + @ whereclause +
'"/F' + @ filename +
'/' + @ Direction
Exec master .. xp_mongoshell @ exec_str

2. Create a table and initialize data
Create Table Name (No. Int, image column name image)
Go
Insert table name values (1, 0x) -- required, and not null
Insert table name values (2, 0x) -- required and not null
Go

3. Read
Sp_textcopy 'your server name', 'sa ', 'Your password', 'database name', 'table name', 'image column name', 'c: \ image .bmp ', 'Where id = 1', 'I' -- note that the condition is number = 1

Sp_textcopy 'your server name', 'sa ', 'Your password', 'database name', 'table name', 'image column name', 'c: \ bb.doc ', 'Where id = 2', 'I' -- note that the condition is number = 2

Go

4. Read data into files
Sp_textcopy 'your server name', 'sa ', 'Your password', 'database name', 'table name', 'image column name', 'c: \ image .bmp ', 'Where id = 1', 'O' -- note that the condition is number = 1

Sp_textcopy 'your server name', 'sa ', 'Your password', 'database name', 'table name', 'image column name', 'c: \ bb.doc ', 'Where id = 2', 'O' -- note that the condition is number = 2
Go

If textcopy is not an executable file
C: \ Program Files \ Microsoft SQL Server \ MSSQL \ binn
Copy textcopy.exe to the following directory:
C: \ Program Files \ Microsoft SQL Server \ 80 \ tools \ binn

BCP mode:

/* -- Bcp to import and export binary files

Supports import/export of image, text, and ntext Fields
Image is suitable for binary files, including: Word documents, Excel documents, images, music, etc.
Text and ntext are suitable for text data files.

Note: during import, all rows meeting the conditions will be overwritten.
During export, all rows that meet the conditions will be exported to the specified file.


This stored procedure is only implemented using BCP
--2003.08 -----------------*/

/* -- Call example
-- Data export
Exec p_binaryio 'zj', '','', 'Acc _ demo data .. tb', 'img ', 'c: \ zj1.dat'

-- Data Import
Exec p_binaryio 'zj', '','', 'Acc _ demo data .. tb', 'img ', 'c: \ zj1.dat', '', 0
--*/
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_binaryio] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_binaryio]
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 during processing: @ filename + _ temp
@ TJ varchar (1000) = '', -- processing condition. 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 processes the response file name
, @ Fsize varchar (20) -- size of the file to be processed
, @ M_tbname varchar (50) -- temporary table name
, @ SQL varchar (8000)

-- Obtains the size of the imported file.
If @ isout = 1
Set @ fsize = '0'
Else
Begin
Create Table # Tb (varchar (20), int size
, Created on varchar (10), created on varchar (20)
The last write operation date is varchar (10), and the last write operation time is varchar (20)
, Last access 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 @ m_tbname = '[# temp' + Cast (newid () as varchar (40) + ']'
Set @ SQL = 'select * into '+ @ m_tbname +' from (
Select null as type
Union all select 0 as prefix
Union all select '+ @ fsize +' as length
Union all select null as ended
Union all select null as format
)'
Exec (@ SQL)
Select @ fname_in = @ fname + '_ temp'
, @ SQL = 'bcp "'+ @ m_tbname +'" out "'+ @ fname_in
+ '"/S"' + @ servename
+ Case when isnull (@ username, '') = ''then''
Else '"/u"' + @ username end
+ '"/P"' + isnull (@ password, '') + '"/C'
Exec master .. xp_mongoshell @ SQL
-- Delete a temporary table
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_mongoshell @ SQL
End
Else
Begin
-- Prepare a temporary table for Data Import
Set @ SQL = 'select Top 0' + @ fdname + 'into'
+ @ M_tbname + 'from' + @ tbname
Exec (@ SQL)

-- Import data to a temporary table
Set @ SQL = 'bcp "'+ @ m_tbname +'" in "'+ @ fname
+ '"/S"' + @ servename
+ Case when isnull (@ username, '') = ''then''
Else '"/u"' + @ username end
+ '"/P"' + isnull (@ password ,'')
+ '"/I"' + @ fname_in + '"'
Exec master .. xp_mongoshell @ SQL

-- Import data to a formal table
Set @ SQL = 'update' + @ tbname
+ 'Set' + @ fdname + '= B.' + @ fdname
+ 'From' + @ tbname + 'A ,'
+ @ M_tbname + 'B'
+ Case isnull (@ TJ, '') when''then''
Else 'where' + @ TJ end
Exec (@ SQL)

-- Delete a temporary data processing table
Set @ SQL = 'drop table' + @ m_tbname
End

-- Delete the data processing response File
Set @ SQL = 'del '+ @ fname_in
Exec master .. xp_mongoshell @ SQL

Go