Inserting TOP X Records

Question

Inserting TOP X Records

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
January 14, 2009 at 3:19 pm

#204137

I need to insert into a history table 70,000 records in chunks of 10k each time for performance reasons. I was able to insert the first 10k, but I'm having trouble writing the proper syntax so that the next 10k I insert are not the same 10k I just inserted. Below is the code for the first 10k:
Insert top (10000)
Into LifeExtension_PROD.dbo.mktMarketingTouchHistory
(CustomerNumber, CustomerTypeWhenMailed, UserLoaded, DateLoaded, MarketingCode,
MailDate, Medium, OfferType)
SelectCustomerNo,
Customer_Type,
UserLoaded= 'jfager',
DateLoaded= convert(varchar(10),GetDate(),101),
MarketingCode= 'GFA901D',
MailDate= '01/19/2009',
Medium= 'D',
OfferType= 'GiftMemb Offer'
Fromlefdata.jde_production.jfager.tmpGFA901D
WhereCustomer_Type <> 'SD'
Below is my code for the subsequent 10k insert which is not working:
Insert top (10000)
Into LifeExtension_PROD.dbo.mktMarketingTouchHistory
(CustomerNumber, CustomerTypeWhenMailed, UserLoaded, DateLoaded, MarketingCode,
MailDate, Medium, OfferType)
SelectCustomerNo,
Customer_Type,
UserLoaded= 'jfager',
DateLoaded= convert(varchar(10),GetDate(),101),
MarketingCode= 'GFA901D',
MailDate= '01/19/2009',
Medium= 'D',
OfferType= 'GiftMemb Offer'
Fromlefdata.jde_production.jfager.tmpGFA901D
WhereCustomer_Type <> 'SD'
AndCustomerNo not in (Select CustomerNumber from LifeExtension_PROD.dbo.mktMarketingTouchHistory)
AndMarketingCode not in (Select MarketingCode from LifeExtension_PROD.dbo.mktMarketingTouchHistory)
AndMailDate not in (Select MailDate from LifeExtension_PROD.dbo.mktMarketingTouchHistory)
Any light shed will be appreciated.
John

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

If you have a situation where some of the Customer_No values will already be in the target table, and you just have more rows about more stuff with the same numbers, your query won't select them because of the Where clause.

What I usually do with a complex one like this is a Left Outer join to the target table, and a "Where targettable.Customer_No is null" in the Where clause.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 2

Thanks for the suggestion. I went with a different one though, creating a separate insert temp table w/an identity field and insert from it sequentially using this identity field. It works too.

John

GSquared SSC Guru Points: 260824 More actions · Answer 3

Staging tables like that work quite well. Yep.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 4

latingntlman (1/14/2009)
Thanks for the suggestion. I went with a different one though, creating a separate insert temp table w/an identity field and insert from it sequentially using this identity field. It works too.
John

That's the way I do it especially for archive processes that don't use partitioned tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)