[MSSQL] 이미지 넣기

1. 테스트를 위한 이미지 파일 확인

2. 이미지 파일을 특정 위치에 세팅

(D:\ABC.JPG)

3. 예제 쿼리

DECLARE @Temp TABLE ( [IDX] int IDENTITY(1,1) NOT NULL, [IMAGE] image NULL ) INSERT INTO @Temp([IMAGE]) SELECT * FROM OPENROWSET(BULK N'D:\ABC.JPG',SINGLE_BLOB) AS [Image]

How to insert a blob into a database using sql server management studio

http://stackoverflow.com/questions/1643627/how-to-insert-a-blob-into-a-database-using-sql-server-management-studio

Working with Large Value Types

https://msdn.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

MSDN has an article Working With Large Value Types which tries to explain how import works but it can get a bit confusing since it does 2 things together. So here's a simplified version and broken into 2 parts. Assume simple table:

CREATE TABLE [Thumbnail](
   [Id]        [int] IDENTITY(1,1) NOT NULL,
   [Data]      [varbinary](max) NULL
CONSTRAINT [PK_Thumbnail] PRIMARY KEY CLUSTERED 
(
[Id] ASC
) ) ON [PRIMARY]

If you run (in SSMS):

SELECT * FROM OPENROWSET (BULK 'C:\Test\TestPic1.jpg', SINGLE_BLOB)

it will show that the result looks like a table with one column named BulkColumn. That's why you can use it in INSERT like:

INSERT [Thumbnail] ( Data )
SELECT * FROM OPENROWSET (BULK 'C:\Test\TestPic1.jpg', SINGLE_BLOB)

The rest is just fitting it into insert with more columns which your table may or may not have. If you name the result od that select FOO then you can use SELECT Foo.BulkColumn and ass after that constants for other fields in your table.

The poart that can get more tricky is how to export that data back into a file so you can check that it's still OK. If you run on cmd line:

bcp "select Data from B2B.dbo.Thumbnail where Id=1" queryout D:\T\TestImage1_out2.dds -T -L 1 

It's going to start whining for 4 additional "params" and will give misleading defaults (whihc will result in changed file). You can accept first one, set the 2nd to 0 and then assept 3rd and 4th, or to be explicit:

Enter the file storage type of field Data [varbinary(max)]:
Enter prefix-length of field Data [8]: 0
Enter length of field Data [0]:
Enter field terminator [none]:

Then it will ask:

Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]: C:\Test\bcp_2.fmt

Next time you have to run it add -f C:\Test\bcp_2.fmt and it will stop whining :-) Saves a lot of time and grief.