Do you love a challenge? Converting flat file to SQL Server

[pcawdron]

I'm trying to automate the conversion of a 5.2 Million line flat file (No, I'm not kidding) to SQL Server on a monthly basis. (I've actually just solved my problem - but I'll post bot hthe question and the answer as it may help someone else)

I've simplified the problem below...

FlatFile
Dealer Category Value
One Cars 50,000
One Trucks 20,000
Two Cars 15,000
Two Motorcycles 35,000

NewTable
Dealer Cars Trucks Motorcycles
One 50,000 20,000
Two 15,000 35,000


CREATE PROCEDURE dbo.Flat_to_Columns @dealer CHAR(10) AS
DECLARE @c CHAR(30)
DECLARE column1 CURSOR FOR
SELECT [Category]
FROM dbo.FlatFile GROUP BY [Category]

OPEN column1
FETCH NEXT FROM column1 INTO @c
WHILE @@fetch_status = 0
BEGIN
UPDATE NewTable SET @c = (SELECT SUM(Value) FROM FlatFile WHERE Category = @c AND Dealer = @Dealer), Dealer = @Dealer
FETCH NEXT FROM column1 INTO @c
END
CLOSE column1
DEALLOCATE column1
GO

 

[pcawdron]

Here's the solution... Simply place the SQL in a string and then execute that...


CREATE PROCEDURE dbo.Flat_to_Columns @dealer CHAR(10) AS

DECLARE @c CHAR(30), @SQLString CHAR(500)

DECLARE column1 CURSOR FOR
SELECT [Category]
FROM dbo._FlatFile GROUP BY [Category]

OPEN column1
FETCH NEXT FROM column1 INTO @c
WHILE @@fetch_status = 0
BEGIN
SET @SQLString = N'UPDATE _NewTable SET [' + RTRIM(@c)
+ '] = (SELECT SUM(Value) FROM _FlatFile WHERE Category
= ''' + RTRIM(@c) + ''' AND Dealer = ''' + RTRIM(@Dealer)
+ ''') WHERE Dealer = ''' + RTRIM(@Dealer) + ''''
PRINT @SQLString
EXEC (@SQLString)
FETCH NEXT FROM column1 INTO @c
END
CLOSE column1
DEALLOCATE column1
GO

 

[mingus]

How long does this take? I have the same "need".
My solution was to write a program that took the two files, merged into one and use BULK INSERT on that new file. This only takes me a couple hours to do from start to finish, with the BULK INSERT taking only 30 minutes or less. (My case is 7 million records).

 

[balves]

It might be faster to do it another way:

1. Use DTS (or BCP pre-V7) to import the whole flat file, as is, to a staging table in SQL Server.

2. Use one SQL statement to insert the new table data

INSERT New_Data_Table
SELECT Dealer, Count(Cars), Count(Truck), ...
FROM Staging_Table
GROUP BY Dealer

Or...

There are some cool 3rd-party tools out there like Data Junction that do data translation efficiently.

 

[pcawdron]

It's pretty fast. I use a linkedserver query to transfer the flat file, as is, into SQL Server and then process it locally using this query. The transfer takes 30 minutes, the conversion takes 2 hours, but I think that's primarily due to the fact that my data is aggregated as an intermediary step. (This is only run at month-end, so the time factor isn't too serious)

Regards,
Peter

 

[aalmeida]

If you can put all your calculation into one SQL statment or a sequence of sql statments it is faster to use a view instead of a staging table all you do is bulimport to a staging view instead of a table. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"