import multiple text files & create tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

import multiple text files & create tables

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-16 : 15:11:10

I'm trying to import data from multiple text files into multiple tables that don't exist yet...so I'm using the procedure below.

Is there a way to do this in DTS by creating an import for one file and then using it as a template to iterate over for the other text files?

Thanks,

Paul

DECLARE @FileName varchar(255),
@TableCreateTemplate nvarchar(4000),
@SqlStatement nvarchar(4000)
CREATE TABLE #FileList
(FileName varchar(255))
INSERT INTO #FileList
EXEC master..xp_cmdshell
'dir /b C:\'
SET @TableCreateTemplate =
'CREATE TABLE [$(TableName)]
(
[Date] smalldatetime NOT NULL,
[Open] smallmoney NOT NULL,
[High] smallmoney NOT NULL,
[Low] smallmoney NOT NULL,
[Close] smallmoney NOT NULL,
[Volume] int NOT NULL
)'
DECLARE FileList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT FileName FROM #FileList
OPEN FileList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM FileList
INTO @FileName
IF @@FETCH_STATUS = -1 BREAK
SET @SqlStatement =
REPLACE(@TableCreateTemplate,
'$(TableName)',
@FileName)
EXEC (@SqlStatement) -- create table
SET @SqlStatement =
'BULK INSERT [' + @FileName + ']
FROM ''D:\My Investment Databases\Stock_Quotes\Quote_Data\AMEX\' +
@FileName + ''''
--EXEC (@SqlStatement) -- load data
END
CLOSE FileList
DEALLOCATE FileList

DROP TABLE #FileList

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-16 : 22:22:19

Please do not cross post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32569

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources