SQL 如何将图像字段导出到文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1366544/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export image field to file?
提问by Scott Langham
I am using Microsoft SQL Server Management Studio to connect to a database. In it I've got a table, one column of which is an Image column containing file data. Another column is a string containing the file name.
我正在使用 Microsoft SQL Server Management Studio 连接到数据库。在其中我有一个表，其中一列是包含文件数据的 Image 列。另一列是包含文件名的字符串。
Is there some way I can write some sql script that will allow me to select a record and write this data to a file? Or if this isn't possible, what's a simple way for achieving this?
有什么方法可以编写一些 sql 脚本，让我可以选择一条记录并将这些数据写入文件？或者，如果这是不可能的，那么实现这一目标的简单方法是什么？
I've seen this related question, but it doesn't seem quite the same: Save image column to file in sql-server 2000
我看过这个相关的问题，但似乎不太一样：Save image column to file in sql-server 2000
回答by mathijsuitmegen
-- Write all database images (jpg) to file.
--------- --------- --------- --------- --------- --------- ---------
DECLARE CURSOR_ProductIds CURSOR FOR (SELECT ImgImagesId FROM ImgProductSample)
DECLARE @ProductId INT;
OPEN CURSOR_ProductIds
FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE @ImageData varbinary(max);
SELECT @ImageData = (SELECT convert(varbinary(max), ImageData, 1) FROM ProductImages WHERE Id = @ProductId);
DECLARE @Path nvarchar(1024);
SELECT @Path = 'C:\MyImages\Output';
DECLARE @Filename NVARCHAR(1024);
SELECT @Filename = (SELECT ImageFilename FROM ProductImages WHERE id = @ProductId);
DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @FullPathToOutputFile = @Path + '\' + @Filename;
DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
EXEC sp_OAMethod @ObjectToken, 'Open';
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
EXEC sp_OAMethod @ObjectToken, 'Close';
EXEC sp_OADestroy @ObjectToken;
FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId
END
CLOSE CURSOR_ProductIds
DEALLOCATE CURSOR_ProductIds
-- Make sure the following statement is executed to enable file IO
-- From http://msdn.microsoft.com/en-us/library/ms191188.aspx
--------- --------- --------- --------- --------- --------- ---------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
回答by user326608
From mkader at codeproject:
来自 codeproject 的mkader：
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE;
GO
Then dump a format file:
然后转储一个格式文件：
Declare @sql varchar(500)
SET @sql = 'bcp DBName.dbo.tblTableName format nul -T -n -f C:\testblob.fmt -S ' + @@SERVERNAME
select @sql
EXEC master.dbo.xp_CmdShell @sql
My format file looked like this:
我的格式文件如下所示：
10.0
12
1 SQLINT 0 4 "" 1 my_tablecol_id ""
2 SQLINT 1 4 "" 2 my_tablecol0 ""
3 SQLCHAR 2 256 "" 3 my_tablecol1 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 256 "" 4 my_tablecol2 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 256 "" 5 my_tablecol3 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 2048 "" 6 my_tablecol4 SQL_Latin1_General_CP1_CI_AS
7 SQLIMAGE 4 0 "" 7 my_imagecol ""
8 SQLCHAR 2 2048 "" 8 my_tablecol6 SQL_Latin1_General_CP1_CI_AS
9 SQLINT 1 4 "" 9 my_tablecol7 ""
10 SQLINT 1 4 "" 10 my_tablecol8 ""
11 SQLINT 1 4 "" 11 my_tablecol9 ""
12 SQLBIT 1 1 "" 12 my_tablecol10 ""
I then edited the format file like so:
然后我像这样编辑格式文件：
10.0
1
1 SQLIMAGE 0 0 "" 1 my_imagecol ""
And then ran this SQL in SSMS:
然后在 SSMS 中运行此 SQL：
Declare @mynum int
Declare @sql varchar(500)
SET @mynum = 49 -- something meaningful only to me
SET @sql = 'BCP "SELECT my_imagecol FROM DBName.dbo.tblTableName where my_tablecol_id=@mynum" QUERYOUT C:\myfilename.docx -T -fC:\testblob.fmt -S ' + @@SERVERNAME
EXEC master.dbo.xp_CmdShell @sql
This worked really well, any kind of data in the image column works.
这非常有效，图像列中的任何类型的数据都有效。
回答by mathijsuitmegen
-- Write database image (jpg) to file
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101754
--------- --------- --------- --------- --------- --------- ---------
DECLARE @ImageData varbinary(max);
SELECT @ImageData = (SELECT convert(varbinary(max), ImageData, 1) FROM ProductImages WHERE Id = 1);
DECLARE @Path nvarchar(1024);
SELECT @Path = 'C:\MyImages\Output';
DECLARE @Filename NVARCHAR(1024);
SELECT @Filename = (SELECT ImageFilename FROM ProductImages WHERE id = 1);
DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @FullPathToOutputFile = @Path + '\' + @Filename;
DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
EXEC sp_OAMethod @ObjectToken, 'Open';
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
EXEC sp_OAMethod @ObjectToken, 'Close';
EXEC sp_OADestroy @ObjectToken;
-- Make sure the following statement is executed to enable file IO
-- From http://msdn.microsoft.com/en-us/library/ms191188.aspx
--------- --------- --------- --------- --------- --------- ---------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
PS - The link to article 'Reading and Writing Files in SQL Server using T-SQL' was useful but I after I got the piece of code working it produced an invalid image file.
PS - 文章“使用 T-SQL 在 SQL Server 中读取和写入文件”的链接很有用，但在我运行一段代码后，它生成了一个无效的图像文件。
回答by Jaggan_j
I know this is above 2 year old thread, still wanted to post, because it could help someone. If we want to export images along with the other columns (which includes symbols) from a database table to a file, so that they can be imported into another SQL Server, this comes in very handy.
我知道这是 2 岁以上的线程，仍然想发布，因为它可以帮助某人。如果我们想将图像连同其他列（包括符号）从数据库表导出到文件，以便将它们导入另一个 SQL Server，这非常方便。
The below code will export the specified database table to the file path of choice.
下面的代码将指定的数据库表导出到选择的文件路径。
Declare @sql varchar(500)
SET @sql = 'bcp Your_db.Your_table out Your_File_Path(eg: C:\ImagesNames.Dat) -w -T -S ' + @@SERVERNAME
EXEC @sql
The -w switch is for a Unicode format data file and the delimiter will be the default tab delimiter if we don't specify a delimiter. In this case, the data file is saved with a dat extension. Maybe it could be saved in other formats as well, but I haven't tested it, whereas this one works perfectly.
-w 开关用于 Unicode 格式的数据文件，如果我们不指定分隔符，分隔符将是默认的制表符分隔符。在这种情况下，数据文件以 dat 扩展名保存。也许它也可以保存为其他格式，但我还没有测试过，而这个效果很好。
https://msdn.microsoft.com/en-gb/library/ms188289(v=sql.110).aspx
https://msdn.microsoft.com/en-gb/library/ms188289(v=sql.110).aspx
Then for the import:
然后对于导入：
BULK INSERT Your_db.Your_table
FROM 'Your_File_Path'
with (DATAFILETYPE='widechar')
This imports the data file very efficiently into the specified database table.
这非常有效地将数据文件导入到指定的数据库表中。
