图片等多媒体文件插入到表 IF OBJECT_ID ('file_table', 'U') IS NOT NULL DROP TABLE file_table; GO CREATE TABLE file_table(FileName nvarchar(60), FileType nvarchar(60), Document varbinary(max)) GO FROM OPENROWSET(BULK 'C:\myText.txt', SINGLE_BLOB) AS Document INSERT INTO file_table(FileName, FileType, Document) SELECT 'Img252837387.jpg' AS 文件名, '.jpg' AS 文件类型, * FROM OPENROWSET(BULK 'C:\Img252837387.jpg', SINGLE_BLOB) AS Document INSERT INTO file_table(FileName, FileType, Document) SELECT '1.rar' AS 文件名, '.rar' AS 文件类型, * FROM OPENROWSET(BULK 'C:\1.rar', SINGLE_BLOB) AS Document GOINSERT INTO file_table(FileName, FileType, Document) SELECT 'myText.txt' AS 文件名, '.txt' AS 文件类型, * FROM OPENROWSET(BULK 'C:\1.rar', SINGLE_BLOB) AS Document GO
IF OBJECT_ID ('file_table', 'U') IS NOT NULL DROP TABLE file_table;
GO
CREATE TABLE file_table(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max))
GO
FROM OPENROWSET(BULK 'C:\myText.txt', SINGLE_BLOB) AS Document
INSERT INTO file_table(FileName, FileType, Document)
SELECT 'Img252837387.jpg' AS 文件名, '.jpg' AS 文件类型, * FROM OPENROWSET(BULK 'C:\Img252837387.jpg', SINGLE_BLOB) AS Document
INSERT INTO file_table(FileName, FileType, Document)
SELECT '1.rar' AS 文件名, '.rar' AS 文件类型, * FROM OPENROWSET(BULK 'C:\1.rar', SINGLE_BLOB) AS Document
GOINSERT INTO file_table(FileName, FileType, Document)
SELECT 'myText.txt' AS 文件名, '.txt' AS 文件类型, * FROM OPENROWSET(BULK 'C:\1.rar', SINGLE_BLOB) AS Document
GO
http://topic.csdn.net/u/20081024/11/846e3e56-218b-4b07-b733-ee87dc2fe687.html?882064923
一个有关存储过程导出数据为图片的方法create PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@photoname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
--http://topic.csdn.net/u/20080421/09/f0f1a4d3-ea79-43e4-a8e7-f55025543ee4.html?1600242342
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'textcopy /S ' + @srvname +
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @photoname +
' /W " ' + @whereclause +
'" /F ' + @filename +
' / ' + @direction
EXEC master..xp_cmdshell @exec_str ,no_output create table photo(id int identity(1,1),photo image,memo nvarchar(50) default 'my photo ')
go insert photo(photo) values(0x)
insert photo(photo) values(0x)
--select * from photo
--truncate table photo --读入
sp_textcopy 'ibm', 'sa ', '123', 'liz', 'photo', 'photo', 'd:\ip.jpg', 'where id=1', 'I' --输出
sp_textcopy 'ibm', 'sa ', '123', 'liz', 'photo', 'photo', 'e:\ip.jpg', 'where id=1', 'o'