Loop Through Table List And Import

Question

Post reply

Loop Through Table List And Import

raym

SSCrazy

Points: 2213
More actions
January 8, 2008 at 9:16 am

#177903

I have a 100 files which I want to import into a table.
When I run them one at a time I use the statement below and it works
Now to go through a 100 files which are about 300mb each would require
a loop.
I tried writing one and its not working? Where am I going wrong ?
--Insert Data "Original"
INSERT tb_TextImport EXECUTE MASTER..xp_cmdShell 'Type \\phumba\daily$\INCT\INCTEX01.txt'
GO
IF OBJECT_ID('tempdb..#Files') IS NOT NULL
DROP TABLE #Files
GO
CREATE TABLE #Files
(
FileIDINT IDENTITY(1,1)
,FileNameVARCHAR(257)
,RowsImportedINT
,StartTimeDATETIME
,EndTimeDATETIME
,TimeTakemVARCHAR(20)
,IndicatorCHAR(1)
,LoadDate DATETIME
)
--INSERT DATA
INSERT #Files(FileName) VALUES('INCTEX01.txt')
INSERT #Files(FileName) VALUES('INCTEX02.txt')
INSERT #Files(FileName) VALUES('INCTEX03.txt')
INSERT #Files(FileName) VALUES('INCTEX04.txt')
INSERT #Files(FileName) VALUES('INCTEX05.txt')
INSERT #Files(FileName) VALUES('INCTEX06.txt')
INSERT #Files(FileName) VALUES('INCTEX07.txt')
INSERT #Files(FileName) VALUES('INCTEX08.txt')
INSERT #Files(FileName) VALUES('INCTEX09.txt')
INSERT #Files(FileName) VALUES('INCTEX10.txt')
INSERT #Files(FileName) VALUES('INCTEX11.txt')
INSERT #Files(FileName) VALUES('INCTEX12.txt')
INSERT #Files(FileName) VALUES('INCTEX13.txt')
INSERT #Files(FileName) VALUES('INCTEX14.txt')
INSERT #Files(FileName) VALUES('INCTEX15.txt')
INSERT #Files(FileName) VALUES('INCTEX16.txt')
INSERT #Files(FileName) VALUES('INCTEX17.txt')
INSERT #Files(FileName) VALUES('INCTEX18.txt')
INSERT #Files(FileName) VALUES('INCTEX19.txt')
INSERT #Files(FileName) VALUES('INCTEX20.txt')
---LOOP THROUGH THE FILE LIST AND IMPORT
DECLARE @Query VARCHAR(1000)
DECLARE @FileID INT
DECLARE @Count1 INT
DECLARE @Filename VARCHAR(100)
--Create Table
SET @Count1 = 0
SET @Query ='Type \\phumba\daily$\INCT\' + @Filename
SET @FileID = (SELECT MAX(FileID) FROM #Files)
WHILE @Count1 <= @FileID
BEGIN
SET @Count1 = @Count1+1
SET @Filename = (SELECT FileName FROM #Files WHERE FileID = @Count1)
INSERT tb_TextImport EXEC MASTER..xp_cmdShell(@Query)
END

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 1

You're trying to exec @Query, but you're never adding the current @Filename to it.

WHILE @Count1 <= @FileID

BEGIN

SET @Count1 = @Count1+1

SET @Filename = (SELECT FileName FROM #Files WHERE FileID = @Count1)

SET @Query ='Type \\phumba\daily$\INCT\' + @Filename

print @Query

INSERT tb_TextImport EXEC MASTER..xp_cmdShell(@Query)

END

Say it with me, "DOH!" 😀

There is no "i" in team, but idiot has two.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

...and it would be a lot faster if you used BULK INSERT or BCP...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

raym SSCrazy Points: 2213 More actions · Answer 3

Thanks got it now. I have amended my script and it works.

--Uncompress Zip Files

EXEC master..xp_cmdshell '\\phumba\daily$\INFiles\Apps\gzip -d -S zip \\phumba\daily$\INCT\*.zip'

--Rename all files to files with .txt extension

EXEC master..xp_cmdshell 'rename \\phumba\daily$\INCT\*. *.txt'

--Create The Temp table to hold the filenames to be imported

IF OBJECT_ID('tempdb..#Files') IS NOT NULL

DROP TABLE #Files

GO

CREATE TABLE #Files

(

FileIDINT IDENTITY(1,1)

,FileNameVARCHAR(257)

)

--Insert the file details

INSERT#Files(FileName)

EXECxp_cmdshell 'dir /b \\MIS1\Tmandaily$\INCT\*.txt'

GO

-- Delete Invalid file names

DELETE FROM#Files WHERE FileName IS NULL

--Import All the files by Looping through the List

DECLARE @Query VARCHAR(1000)

DECLARE @FileID INT

DECLARE @Count1 INT

DECLARE @Filename VARCHAR(100)

--Working Variables

DECLARE @StartTime DATETIME

DECLARE @EndTime DATETIME

DECLARE @Rows int

--Set Variables

SET @Count1 = 0

SET @FileID = (SELECT MAX(FileID) FROM #Files)

WHILE @Count1 <= @FileID

BEGIN

SET @Count1 = @Count1+1

SET @Filename = (SELECT FileName FROM #Files WHERE FileID = @Count1)

SET @Query ='BULK INSERT tb_TextImport FROM ' + '''\\phumba\daily$\INCT\'+ @Filename+ '''' +

' WITH ( BATCHSIZE = 50000,FIELDTERMINATOR = ''\t'')'

-- Print @Query

SET @StartTime = GETDATE()

--Exec the query to bulk insert the data

EXEC (@Query)

SET @Rows = @@ROWCOUNT

SET @EndTime = GETDATE()

--Insert Load Stats

INSERT tb_loadStatus

SELECT

@Filename

,ISNULL(@Rows,0)

,@StartTime

,@EndTime

,CONVERT(VARCHAR, DATEADD(ss, DATEDIFF(ss, @starttime, @endtime), 0), 108)

,@Query

END

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

If you ever need to avoid the use of xp_CmdShell (lot's of DBA's simply don't allow its use for security reasons), then check this out...

EXEC Master.dbo.xp_DirTree '\\MIS1\Tmandaily$\INCT\',1,1

Won't help you with unzipping anything, but will allow you to get the contents of a directory. Does NOT require any special privs or settings (like xp_CmdShell does) and is available on 2k, 2k5, and 2k8.

You can dump the output into a temp table using INSERT INTO/EXEC just like you did with xp_CmdShell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

raym SSCrazy Points: 2213 More actions · Answer 5

Thanks will check it out now and modify my script. I ran it and it loaded the files in 30 mins, thats after using bulk insert..

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 6

Ys just gotta love Bulk Insert for speed... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)