Copy data into Normalized Tables

Question

Post reply

Copy data into Normalized Tables

Richard Buchner, Chicago

Ten Centuries

Points: 1390
More actions
August 13, 2006 at 4:04 pm

#114601

I'm writing a series of Stored Procedures,
to copy data into normalized tables, for source tables.
they all use an iendity column as the primary key. and go up to 9 levels deep
Like :
Customers CustId is PK
Orders foreingnkey CustID, PK is OrderID
Order Details; PK OrderDetail ID, Foreign Key is OrderID

Any tips on best way to do this?
Thanks

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

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

Leda Behseresht SSC Veteran Points: 286 More actions · Answer 1

It depends... Are your target tables 'live', i.e., receiving data from other sources while you are doing these inserts?

If the tables are not live, then you could do INSERT / SELECT joining to a Numbers table like the one mentioned below

http://www.sqlservercentral.com/columnists/mcoles/2547.asp

If you're doing large amounts of data, you may need to break your result sets into smaller 'batches' to ensure you don't fill your transaction log.

EXAMPLE:

DECLARE @begID int, @endID int, @batchSize int, @maxID int

SELECT @begID = min(sourceRowID) FROM dbo.SourceTable

SELECT @maxID = max(sourceRowID) FROM dbo.SourceTable

SELECT @batchSize = 1000, @endID = @begID + @batchSize

WHILE @endID <= @maxID + @batchSize

BEGIN

INSERT INTO dbo.Customers

FROM dbo.SourceTable

WHERE SourceRowID >= @begID AND SourceRowID < @endID

SET @begID = @endID

SET @endID = @endID + @batchSize

END

If you're doing only a few rows or the tables are 'live', then create an INSERT stored procedure for each table involved with the identity column as an output parameter. Then use the output parameter as an input parameter in the next stored procedure call. Do this all in a WHILE loop.

Richard Buchner, Chicago Ten Centuries Points: 1390 More actions · Answer 2

leda,

thanks, the tables are "sort of live" users will be invoking the tem to copy

marketing proposales for new customers.

belpow is what you suggest then? Thanks

>>If you're doing only a few rows or the tables are 'live', then create an INSERT stored procedure for each table involved with the identity column as an output parameter. Then use the output parameter as an input parameter in the next stored procedure call. Do this all in a WHILE loop>>

Leda Behseresht SSC Veteran Points: 286 More actions · Answer 3

Yep, if others will be inserting data into these tables at the same time you're migrating data, then I'd suggest using the insert stored procedures in a while loop thru the source table.