Is there any easy way to import multiple .txt files to SQL server?

Question

I am trying to import 24 .txt files to the server but I have limited privileges. All files start with the same characters, but '*' this masking cannot be used for this query. I use below script.

    SELECT * into TABLE
FROM OPENROWSET( BULK 'c:\path\files\good*.txt', FORMATFILE = 'c:\path\files\import.xml',FIRSTROW = 2) AS DATA;

Answer 1

You would need to create the table first and then insert each file using a loop.

try the following script after creating the destination table:

declare @filepath varchar(100)= 'c:\path\files\'
    ,@pattern varchar(100)= 'good*.txt'
    ,@TableName varchar(100)= 'TestTable'

DECLARE @query varchar(1000)
DECLARE @numfiles int
DECLARE @filename varchar(100)
DECLARE @files TABLE (SourceFileName varchar(200) NULL)

SET @query = 'master.dbo.xp_cmdshell "dir ' + @filepath+@pattern + ' /b"'
INSERT @files(SourceFileName) 
EXEC (@query)

DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT SourceFileName FROM @files WHERE SourceFileName IS NOT NULL

SET @numfiles =0
OPEN CUR
FETCH NEXT FROM CUR INTO @filename
WHILE (@@FETCH_STATUS = 0)
BEGIN
    print @filename
    SET @numfiles+=1

    SET @query = ('BULK INSERT ' + @TableName
                    + ' FROM ''' + @Filepath+@filename + ''' WITH(
                        FORMATFILE = ''c:\path\files\import.xml'',
                        FIRSTROW = 2
                        );'
              )
    PRINT @query
    EXEC (@query)

    FETCH NEXT FROM CUR INTO @filename
END

CLOSE CUR
DEALLOCATE CUR