Load a 20GB File into database

Question

Load a 20GB File into database

XPSCodes-667952

SSChasing Mays

Points: 603
More actions
March 25, 2008 at 9:25 am

#188389

What would be the best way to load/unload a 20gb file from/to database.
Each row in the file needs to be split in to a header record(header table) and a 10 detail records(detail table).
--------------------------------------------------------------------------------------
Save our world, its all we have! A must watch video Pale Blue Dot[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

how often will you be loading the file? I would use bulk copy and then do the header - detail processing.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

XPSCodes-667952 SSChasing Mays Points: 603 More actions · Answer 2

Once every month.

Unload the file, do some processing, truncate the table, load file back to the table.

I'm not sure how i would implement this with bulkcopy. I have to split one single row in to multiple rows.

for e.g:

Create Table Header

(ID INT, HVALUE1 VARCHAR(10),HVALUE2 VARCHAR(10))

Create Table Detail

(ID INT, DVALUE1 VARCHAR(10),DVALUE2 VARCHAR(10),DVALUE3 VARCHAR(10)DVALUE4 VARCHAR(10)DVALUE5 VARCHAR(10))

--My input record from file will look something like this

Select 1, 'H1', 'H2', 'D11', 'D12', 'D13', 'D14', 'D15', 'D21', 'D22', 'D23', 'D24', 'D25', 'D31', 'D32', 'D33', 'D34', 'D35', 'D41', 'D42', 'D43', 'D44', 'D45'

Create Table Header

(ID INT, HVALUE1 VARCHAR(10),HVALUE2 VARCHAR(10))

Create Table Detail

(ID INT, DVALUE1 VARCHAR(10),DVALUE2 VARCHAR(10),DVALUE3 VARCHAR(10),DVALUE4 VARCHAR(10),DVALUE5 VARCHAR(10))

--My input record from file will look something like this

Select 1, 'H1', 'H2', 'D11', 'D12', 'D13', 'D14', 'D15', 'D21', 'D22', 'D23', 'D24', 'D25', 'D31', 'D32', 'D33', 'D34', 'D35', 'D41', 'D42', 'D43', 'D44', 'D45'

--This will become

Insert Header

Select 1, 'H1', 'H2'

Insert Detail

Select 1,'D11', 'D12', 'D13', 'D14', 'D15' UNION ALL

Select 1,'D21', 'D22', 'D23', 'D24', 'D25' UNION ALL

Select 1,'D31', 'D32', 'D33', 'D34', 'D35' UNION ALL

Select 1,'D41', 'D42', 'D43', 'D44', 'D45'

Luckily the number of detail records for a header is fixed. Means the files record length is fixed.

Is it possible to do this in bulkcopy? I'm not sure how i would reconstruct the file record from these 2 tables.

--------------------------------------------------------------------------------------
Save our world, its all we have! A must watch video Pale Blue Dot[/url]