SQL Server – Insert a file into a nvarchar column

Note to self: It’s easy to import an entire file into SQL Server.

Given a table that looks like this:

Screenshot

CREATE TABLE [dbo].[TableWithLargeColumn](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Data] [nvarchar](max) NULL,
CONSTRAINT [PK_TableWithLargeColumn] 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]

You can insert a text file into the Data column like this:


insert into TableWithLargeColumn(Data)
select BulkColumn FROM
OPENROWSET (BULK N'C:\Users\public\testdata.txt', SINGLE_CLOB ) as Document

Advertisements
This entry was posted in SQL. Bookmark the permalink.

2 Responses to SQL Server – Insert a file into a nvarchar column

  1. Martin Oakes says:

    that worked, but I also needed to have a tag, which in my case was the name of the XML File, so..

    insert into TableWithLargeColumn(XmlFileName, Data)
    select ‘testdata.txt’ as XmlFileName, BulkColumn FROM
    OPENROWSET (BULK N’C:\Users\public\testdata.txt’, SINGLE_CLOB ) as Document

    ..did the job. This may be obvious! Then I could use the tag to do a table to table copy inside the database to the REAL target table. What I REALLY wanted to do was:

    insert into OtherTableWithLargeColumn( Data)
    SELECT BulkColumn FROM
    OPENROWSET (BULK N’C:\Users\public\testdata.txt’, SINGLE_CLOB ) as Document
    WHERE XmlFileName,= ‘testdata.txt’

    which of course won’t work because the WHERE works on the SELECT and not on the INSERT,
    so this two-step process worked. And I had 80 XML files to move, so I created one SQL for each starting with a folder listing to a text file, and ran those, one by one.

    Any suggestions on how to do this in Bulk? Anything that needs a field terminator (e.g. BULK INSERT) typically fails due to the XML containing who knows what characters.

  2. codealoc says:

    I’m not sure I totally understand, but I think I would approach your problem with a table variable listing the different file names and foldernames. It sounds like the file or folder names map to a table. Then loop through them with a cursor and then execute dynamic sql with sp_executesql.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s