en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
ספט20

Written by: ronen ariely
20/09/2013 07:51 RssIcon

1. create table to hold a list of all the files

create table t_files( filename varchar(255) null )
GO

2. use this query to populate the table with all the files in the folder (after the job end or in the file step, you can delete or move from that folder all the old files)

truncate table t_files
insert into t_files
exec master.dbo.xp_cmdshell 'dir "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\DatabaseNameQ" /b /a-d'

3. use simple dynamic sql. you can get the query like this

declare @MyUuery nvarchar(max) = ''
select @MyUuery +=
    case
    when not isnull([filename],'') = ''
    then
        '
        BULK INSERT YourTableName FROM ''' + [filename] + '''
                    WITH (
                       FIELDTERMINATOR = '','',
                       ROWTERMINATOR = ''\n'',
                       FIRSTROW = 2
                    ) '
    else ''
    end
    from t_files
-- print @MyUuery

Have Fun :-)