SQL Server/T-SQL Tutorial/Data Types/OPENROWSET

Материал из SQL эксперт
Перейти к: навигация, поиск

Inserting or Updating an Image File Using OPENROWSET and BULK

   <source lang="sql">

UPDATE and OPENROWSET can be used together to import an image into a table. OPENROWSET can be used to import a file into a single row, single column value. OPENROWSET ( BULK "data_file",| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB )

Parameter Description data_file The name and path of the file to read. SINGLE_BLOB | Designate the SINGLE_BLOB object for importing into a varbinary(max) data SINGLE_CLOB | type, SINGLE_CLOB for ASCII data into a varchar(max) data type, and SINGLE_NCLOB SINGLE_NCLOB for importing into a nvarchar(max) UNICODE data type. Referenced from: SQL Server 2005 T-SQL Recipes A Problem-Solution Approach 20> 21> 22> CREATE TABLE ImageTable( 23> ID int NOT NULL, 24> Gif varbinary(max) NOT NULL 25> ) 26> GO 1> 2> INSERT ImageTable 3> (ID, Gif) 4> SELECT 1, 5> BulkColumn 6> FROM OPENROWSET(BULK "C:\yourImage.gif",SINGLE_BLOB) AS x 7> GO Msg 4860, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 2 Cannot bulk load. The file "C:\yourImage.gif" does not exist. 1> 2> SELECT Gif 3> FROM ImageTable 4> WHERE ID = 1 5> GO Msg 208, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 2 Invalid object name "ImageTable". 1> 2> UPDATE ImageTable 3> SET Gif = 4> (SELECT BulkColumn 5> FROM OPENROWSET(BULK 6> "C:\newImage.gif", 7> SINGLE_BLOB) AS x) 8> WHERE ID =1 9> GO Msg 208, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 2 Invalid object name "ImageTable". 1> 2> drop table ImageTable 3> GO Msg 3701, Level 11, State 5, Server BCE67B1242DE45A\SQLEXPRESS, Line 2 Cannot drop the table "ImageTable", because it does not exist or you do not have permission. 1></source>


OPENROWSET(BULK "C:\ER.vdx",SINGLE_BLOB)

   <source lang="sql">

18> CREATE TABLE dbo.VisioDocs 19> ( 20> id INT NOT NULL, 21> doc XML NOT NULL 22> ); 23> GO 1> 2> INSERT INTO dbo.VisioDocs (id, doc) 3> SELECT 1, * 4> FROM OPENROWSET(BULK "C:\ORM.vdx", 5> SINGLE_BLOB) AS x; 6> INSERT INTO dbo.VisioDocs (id, doc) 7> SELECT 2, * 8> FROM OPENROWSET(BULK "C:\ER.vdx", 9> SINGLE_BLOB) AS x; 10> INSERT INTO dbo.VisioDocs (id, doc) 11> SELECT 3, * 12> FROM OPENROWSET(BULK "C:\UML.vdx", 13> SINGLE_BLOB) AS x; 14> INSERT INTO dbo.VisioDocs (id, doc) 15> SELECT 4, * 16> FROM OPENROWSET(BULK "C:\ER.vdx", 17> SINGLE_BLOB) AS x; 18> GO Msg 4860, Level 16, State 1, Server J\SQLEXPRESS, Line 2 Cannot bulk load. The file "C:\ORM.vdx" does not exist. 1> 2> SELECT id, doc FROM dbo.VisioDocs; 3> GO id doc


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1> 2> 3> drop table VisioDocs; 4> GO 1></source>