Need to insert counts to Columns in Table

Question

Need to insert counts to Columns in Table

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
March 27, 2008 at 3:16 pm

#70897

I'm creating a DTS w/several steps. It creates a master list table, then it cleans it up (i.e. bad addresses, etc.) At the end of each clean-up step, another step gets the new count and inserts it into a Log Table. My problem is when the second Insert to the Log Table runs, it appends another row, and then the next count insert Step does the same, and so forth. How do I get it to insert subsequent counts withing the same row as the first count Step, which includes a DateStamp. My code is below and the sample result as well.
JobDate Initial OptOutEM OptOutDM BadAddr CleanAddr
Count Count Count Count Count
-----------------------------------------------------------------
3/25/2008 826867 12343 65432 124680 36969
3/26/2008 826901 12343 80350 71358 2461
3/27/2008 755734 NULL NULL NULL NULL
NULLNULL 104181 NULLNULL NULL
NULLNULL NULL 40839 NULLNULL
NULLNULL NULL NULL 1579NULL
NULLNULL NULL NULL NULL2464
My code looks something like this:
------------------------- step one ----------------------
insert into TempMasterCustomerTable_Log_je (JobDate, InitialCount)
select getdate(), count(*) from TempMasterCustomerTable_je (nolock)
------------------------- step two ----------------------
insert intoTempMasterCustomerTable_Log_je (OptOutEMCount)
select count(*) from TempMasterCustomerTable_je
whereOptOutEM = 'y'
------------------------- step three ----------------------
insert into TempMasterCustomerTable_Log_je (OptOutDMCount)
select count(*) from TempMasterCustomerTable_je
whereOptOutDM = 'y'
------------------------- step four ----------------------
insert intoTempMasterCustomerTable_Log_je (BadAddrCount)
selectcount(*) from TempMasterCustomerTable_je
whereCleanFlag like 'BAD%'
------------------------- step five ----------------------
insert intoTempMasterCustomerTable_Log_je (CleanAddrCount)
selectcount(*) from TempMasterCustomerTable_je
whereCleanFlag like 'CL%'
Any light will be appreciated,
John

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

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

David Jackson SSCertifiable Points: 6414 More actions · Answer 1

You have two options, do an initial insert and then update newly inserted row, or capture what you want to insert into variables and insert in one hit. The latter would look something like

Declare @InitialCount int, @OptOutEMCount int, @OptOutDMCount int, @BadAddrCount int, @CleanAddrCount int

------------------------- step one ----------------------

select @InitialCount = count(*) from TempMasterCustomerTable_je (nolock)

------------------------- step two ----------------------

select @OptOutEMCount = count(*) from TempMasterCustomerTable_je

where OptOutEM = 'y'

------------------------- step three ----------------------

select @OptOutDMCount = count(*) from TempMasterCustomerTable_je

where OptOutDM = 'y'

------------------------- step four ----------------------

select @BadAddrCount = count(*) from TempMasterCustomerTable_je

where CleanFlag like 'BAD%'

------------------------- step five ----------------------

select @CleanAddrCount = count(*) from TempMasterCustomerTable_je

where CleanFlag like 'CL%'

insert into TempMasterCustomerTable_Log_je (JobDate, InitialCount, OptOutEMCount, OptOutDMCount, BadAddrCount, CleanAddrCount)

values(GetDate(), @InitialCount, @OptOutEMCount, @OptOutDMCount, @BadAddrCount, @CleanAddrCount)

Note, not tested above, typos may bite you!

HTH

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 2

I like this. This may actually work. I'll let you know.

thx a bunch.

John