Inserting an Image file Using OPENROWSET and BULK

In SQL Server 2005 and 2008, 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.

The basic syntax for OPENROWSET as it applies to this recipe is as follows:

OPENROWSET
( BULK 'data_file' ,
SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB )

CREATE TABLE dbo.Images
(
 ImgID int NOT NULL,
 Img varbinary(max) NOT NULL
)
GO

Next, a row containing the image file will be inserted into the table:

INSERT dbo.Images
(
ImgID,
Img
)
SELECT 1,
BulkColumn
FROM OPENROWSET(BULK
'Path Of the Image File', SINGLE_BLOB) AS x

This next query selects the row from the table:

SELECT Img
FROM Images
WHERE ImgID = 1

Using Values As Table Source In Sql Server 2008

Using Values As Table Source In Sql Server 2008

        Insert Multiple Rows without having to retype the initial INSERT table name and column list.Using this same new feature in SQL Server 2008, you can also reference the VALUES list in the FROM clause of a SELECT statement...

        This will Tell US how to reference a result set without having to use a permanent or
temporary table.

Syntax:-

SELECT SrNo, F_Name, L_Name
FROM
(VALUES
(1, 'First_Name', 'Last_Name'),
(2, 'First_Name', 'Last_Name'),
(3, 'First_Name', 'Last_Name'),
(4, 'First_Name', 'Last_Name'),
(5, 'First_Name', 'Last_Name')
Users (SrNo, F_Name, L_Name)

This returns

SrNo    F_Name        L_Name

1        First_Name    Last_Name

2        First_Name    Last_Name

3        First_Name    Last_Name

4        First_Name    Last_Name

5        First_Name    Last_Name

How It Works

This is a new SQL Server 2008 technique for returning a result set to persist
the rows in storage. Breaking down the query, the first row in the SELECT clause listed the column
names:
 
SELECT SrNo, F_Name, L_Name
 
These are not actual column names from a referenced table—but instead are aliased names I
defined later on in the query itself. The next line defined the FROM clause for the data source, followed by a parenthesis encapsulating
the VALUES keyword:
 
FROM
(VALUES
 
The next few lines of code listed rows I wished to return from this query (similar to how I
inserted multiple rows in a single INSERT in the previous Query): 
(1, 'First_Name', 'Last_Name'),
(2, 'First_Name', 'Last_Name'),
(3, 'First_Name', 'Last_Name'),
(4, 'First_Name', 'Last_Name'),
(5, 'First_Name', 'Last_Name')


Lastly, after the final closing parenthesis for the row list, I defined a name for this data source
and the associated column names to be returned for each column (and to be referenced in the
SELECT clause):
 
Users (SrNo, F_Name, L_Name)

This new technique allowed me to specify rows of a table source without having to actually
create a temporary or permanent table.