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 
When INSERTING or DELETING rows from a table, the OUTPUT clause can be used to return a dataset containing the changes made. Mat Richardson explains how.
2019-12-27 (first published: 2017-05-02)
9,506 reads
2012-02-02
3,393 reads
2012-01-17
2,786 reads
2010-06-11
4,764 reads
This article from new author Oleg Netchaev describes the cursor-less script used to generate insert statements. This allows you to efficiently and easily add data generation statements to your project deployments.
2010-05-14 (first published: 2009-07-01)
31,186 reads