T-SQL Bulk Insert

,

Change the @Path and @File variables to match your environment and this will work. Test it out, this saved me huge amount of time in not having to write one code for each of over 200 tables I import using a program I created.

This script allows you to place a file on a drive on your SQL server, and based on the name of that file, truncate and insert clean data.  This works for tab delimited text files only, if you have other delimiters adjust the script.

DECLARE @COUNTER int
DECLARE @MAXID INT
DECLARE @PATH varchar(256) = 'dir C:\FolderName'
DECLARE @FILE VARCHAR(255) = 'C:\FolderName\'
DECLARE @COMMAND varchar(1024) =  @PATH+' /A-D  /B'
DECLARE @SQL NVARCHAR(4000)
DECLARE @CTE TABLE (  
    IDX SMALLINT PRIMARY KEY IDENTITY(1,1) 
    , LINE VARCHAR(512) 
) 


PRINT @COMMAND
INSERT @CTE
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE @CTE WHERE  LINE IS NULL
DELETE @CTE WHERE SUBSTRING(LINE,CHARINDEX('.',LINE)+1,LEN(LINE)) <> 'TXT'
 
--select * from @cte

set @counter = 1
SET @MAXID = (SELECT MAX(IDX) FROM @cte) +1
--(select Top(1) idx from @cte)
while @counter <@MAXID
begin
  --select Name from @cte where idx = @counter
  
SET @SQL = 'TRUNCATE TABLE '
SET @SQL = @SQL + '['+(select SUBSTRING(line,0,CHARINDEX('.',line)) from @cte where idx = @counter)+ ']'
SET @SQL = @SQL + ' '+'Bulk Insert ' +(select SUBSTRING(line,0,CHARINDEX('.',line)) from @cte where idx = @counter)
SET @SQL = @SQL + ' '+ 'FROM '  + ''''+@FILE+(SELECT LINE FROM @cte WHERE idx = @counter)+''''+ ' ' + 'WITH (FIRSTROW = 2,FIELDTERMINATOR ='
SET @SQL = @SQL + '''\t'''+','+'ROWTERMINATOR =''\n'')'

--PRINT @SQL
	EXECUTE sp_executesql @SQL

if @COUNTER > @MAXID
   BEGIN
         BREAK  -- transfer control to next statement after loop
   END
  set @COUNTER = @COUNTER + 1
END

Rate

4 (1)

Share

Share

Rate

4 (1)