Adding Data to Filestream with T-SQL

I’ve written on how to enable Filestream and how to add a filegroup, but I haven’t touched the Filestream impact on your tables. This post will look at the table side of Filestream and how you get your binary data into the database.

Let’s assume you have a filestream enabled database. If you look at my File and Filegroups post, you’ll see how to do this. Given that, we now need to create a table and add data to it.

Creating a Table with Filestream

Filestream doesn’t have a separate entity like Filetables. Instead, the Filestream attribute is added to a column in a table, much like the identity property.

I’ll create a simple table that holds Filestream data.

CREATE TABLE FSDemo
(
    id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
  , name VARCHAR(20)  
  , jpg varbinary(MAX) FILESTREAM
);
go

Here we are creating a basic table that has an ID and name, which are essentially meta data for our filestream data. You could have any amount of information in this table, but the Filestream data is stored in the jpg field, where we have a varbinary(max) datatype, and the FILESTREAM attribute on the table.

I could have a more complex schema, such as the Production.Document table in Adventureworks2008 (shown below)

CREATE TABLE [Production].[Document](
    [DocumentNode] [hierarchyid] NOT NULL,
    [DocumentLevel]  AS ([DocumentNode].[GetLevel]()),
    [Title] [nvarchar](50) NOT NULL,
    [Owner] [int] NOT NULL,
    [FolderFlag] [bit] NOT NULL,
    [FileName] [nvarchar](400) NOT NULL,
    [FileExtension] [nvarchar](8) NOT NULL,
    [Revision] [nchar](5) NOT NULL,
    [ChangeNumber] [int] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [DocumentSummary] [nvarchar](max) NULL,
    [Document] [varbinary](max) FILESTREAM  NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
    );

Adding Data

To add data to this table, we have a variety of choices using T-SQL. It’s possible to use an application, and pass in parameters that are streamed to the table, but for the simplicity of this example, let’s show two ways in T-SQL.

First, let’s look at a direct insert. I can CAST my data to varbinary, and then insert it into the table. I have a small image that looks like this:

It’s a simple circle, very small. The actual data in this image is this:

0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4 D002A000000080004011A0005000000010000003E011B00050000000100000046 01280003000000010002000001310002000000120000004E00000000000000600 000000100000060000000015061696E742E4E45542076332E352E313000FFDB00 43000201010201010202020202020202030503030303030604040305070607070 706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C 0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0 C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C 0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F000001050 1010101010100000000000000000102030405060708090A0BFFC400B510000201 0303020403050504040000017D010203000411051221314106135161072271143 28191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435 363738393A434445464748494A535455565758595A636465666768696A7374757 67778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3 B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E 8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F01000301010101010101010100000000 00000102030405060708090A0BFFC400B51100020102040403040705040400010 277000102031104052131061241510761711322328108144291A1B1C109233352 F0156272D10A162434E125F11718191A262728292A35363738393A43444546474 8494A535455565758595A636465666768696A737475767778797A828384858687 88898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C 4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9 FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03 FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C1 8940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C 59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87 BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007 FFFD9

I can insert this data directly into my table:

INSERT INTO FSDemo(ID, name, jpg) 
Values (NEWID()
      , 'circle.jpg'
      , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9
      );
go

The other method I can show is a direct insert, streaming from the OPENROWSET command. Here I’ll pic an image on my system and insert it.

INSERT INTO FSDemo(ID, name, jpg) 
Values (NEWID()
      , 'uma.jpg'
      , (SELECT * FROM OPENROWSET(BULK N'C:\Users\Steve\Documents\SampleData\Uma.jpg', SINGLE_BLOB) AS CategoryImage)
      );
go

Now if I query the table

SELECT
  id, name, jpg
 from FSDemo;
go

I see my data.

When I look at this in Management Studio, I see the actual hex data, which isn’t easy to understand. I need another way to extract this data and render it. However I can show one more quick demo here. Let me take an XML document and insert it:

declare @list XML
select @list = '
<Lists>
  <Groceries Store=''Safeway''>
    <Item>Milk</Item>
    <Item>Eggs</Item>
    <Item>Bread</Item>
  </Groceries>
  <Drinks Store=''Tipsys''>
    <Drink>Fat Tire</Drink>
    <Drink>Klinker Brick Cabernet</Drink>
    <Drink>Patron</Drink>
  </Drinks>
  <Ranch>
    <Item>electric fence ribbon</Item>
  </Ranch>
</Lists>'
;
INSERT INTO FSDemo(ID, name, jpg) 
Values (NEWID()
      , 'sample.xml'
      , CAST( @list AS VARBINARY(MAX))
      );
go

Once this is done, the data is in binary, as I see from the selection from the table.

However if I cast this result back, I get the data as expected.

Hopefully this helps you to understand how to insert data into a Filestream table. In another post, I’ll look at how you can extract this data for rendering.