none
Bulk insert of multiple txt files

    Question

  • 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.

    Jerry

    Thursday, September 19, 2013 11:26 PM

Answers

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):

    http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

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

    http://www.sqlusa.com/bestpractices2005/filesindirectory/


    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
    Moderator
  • 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.

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


    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 )
    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 :-)


    signature

    Friday, September 20, 2013 5:31 AM
    Moderator