Bulk insert of multiple txt files


  • I have around 400 tab delimited files (with headers) that need to be imported on a daily basis into a single SQL2008R2 table.  I don't have access to SSIS so this will need to be done a different way. I have figured out  the commands to import one file but need to do this for all the files automatically.  Ideally this would be something that could be scheduled like a task.

    My files have 7 columns:

    Computer Name

    Identity Code

    Display Name

    Display Version

    Install Date

    Uninstall String

    Quiet Uninstall String

    The name of my database is Inventory and the table is dbo.Software.

    I am not any good at scripting or sql, so please be specific in your instructions and thank you very much for the help.


    Thursday, September 19, 2013 11:26 PM


All replies

  • You can use BULK INSERT or bcp with FORMAT file.

    To loop through 400 files, use dynamic SQL. Here is an example for dynamic SQL looping (unrelated REBUILD indexes task):

    Load the list of files into a table and use it for looping through the entire set:

    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Friday, September 20, 2013 1:12 AM
  • You can try the below method. I am using a variable file name with index. If the file name has no index or it is a random filename then we can define a table containing file names and use the table in the below query.

    SET @ID=1
    WHILE @ID <> 10
    	SET @STRS = 'BULK INSERT Software '+
    				'FROM ''c:\data' + CAST(@ID AS VARCHAR) + '.txt'' ' +
    				'WITH (FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'
    	--EXEC @STRS
    	SET @ID = @ID + 1

    Regards, RSingh

    Friday, September 20, 2013 3:48 AM
  • This will be more dynamic way and without looping (bulk insert all files in a folder):

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

    create table t_files( filename varchar(255) null )

    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 += 
    	when not isnull([filename],'') = ''
    		BULK INSERT YourTableName FROM ''' + [filename] + '''
    			        WITH ( 
    					   FIELDTERMINATOR = '','', 
    					   ROWTERMINATOR = ''\n'', 
    					   FIRSTROW = 2 
    					) '
    	else ''
    	from t_files
    -- print @MyUuery

    Have Fun :-)


    Friday, September 20, 2013 5:31 AM