locked
Insert images into image datatype using SQL RRS feed

  • Question

  • I have a table like this in SQL Server

    CREATE TABLE [dbo].[myImages](
     [id] [smallint] IDENTITY(1,1) NOT NULL,
     [path] [varchar](200) NULL,
     [image] [image] NULL,
     CONSTRAINT [PK_myImages] PRIMARY KEY CLUSTERED 
    (
     [id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
    ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    I have a image in folder D:\images\Winter.jpg

    I want to insert that image path and image in Image datatype using SQL like

    insert into myImages([path],[image]) values
    So can you please complete the above sql statment?


    Friday, October 19, 2012 6:36 AM

Answers

  • http://msdn.microsoft.com/en-us/library/ms190312.aspx

    CREATE TABLE myTable(FileName nvarchar(60), 
      FileType nvarchar(60), Document image);
    GO
    
    INSERT INTO myTable(FileName, FileType, Document) 
       SELECT 'Text1.txt' AS FileName, 
          '.txt' AS FileType, 
          * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
    GO


    http://www.t-sql.ru


    • Edited by Alexey Knyazev Friday, October 19, 2012 6:47 AM
    • Marked as answer by Srini4781 Friday, October 19, 2012 6:56 AM
    Friday, October 19, 2012 6:44 AM

All replies

  • http://msdn.microsoft.com/en-us/library/ms190312.aspx

    CREATE TABLE myTable(FileName nvarchar(60), 
      FileType nvarchar(60), Document image);
    GO
    
    INSERT INTO myTable(FileName, FileType, Document) 
       SELECT 'Text1.txt' AS FileName, 
          '.txt' AS FileType, 
          * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
    GO


    http://www.t-sql.ru


    • Edited by Alexey Knyazev Friday, October 19, 2012 6:47 AM
    • Marked as answer by Srini4781 Friday, October 19, 2012 6:56 AM
    Friday, October 19, 2012 6:44 AM
  • Thanks Alex.. Its really helped me out.
    Friday, October 19, 2012 6:57 AM
  • HI Srini,

    ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

    For more informaton see the below link

    http://msdn.microsoft.com/en-us/library/ms187993.aspx

    As Alexey specified use OPENROWSET to insert File records


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. By ganeshk

    Friday, October 19, 2012 7:01 AM
  • Thanks Ganesh for the suggestion.

    i will use varbinary(max)  instead on Image datatype.

    Friday, October 19, 2012 9:15 AM