Inserting TOP x Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting TOP x Records

Author

Topic

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2009-01-14 : 17:20:25

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)
Select CustomerNo,
Customer_Type,
UserLoaded = 'jfager',
DateLoaded = convert(varchar(10),GetDate(),101),
MarketingCode = 'GFA901D',
MailDate = '01/19/2009',
Medium = 'D',
OfferType = 'GiftMemb Offer'
From lefdata.jde_production.jfager.tmpGFA901D
Where Customer_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)
Select CustomerNo,
Customer_Type,
UserLoaded = 'jfager',
DateLoaded = convert(varchar(10),GetDate(),101),
MarketingCode = 'GFA901D',
MailDate = '01/19/2009',
Medium = 'D',
OfferType = 'GiftMemb Offer'
From lefdata.jde_production.jfager.tmpGFA901D
Where Customer_Type <> 'SD'
And CustomerNo not in (Select CustomerNumber from LifeExtension_PROD.dbo.mktMarketingTouchHistory)
And MarketingCode not in (Select MarketingCode from LifeExtension_PROD.dbo.mktMarketingTouchHistory)
And MailDate not in (Select MailDate from LifeExtension_PROD.dbo.mktMarketingTouchHistory)

Any light shed will be appreciated.

John

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-01-14 : 17:26:35

I normally create a seperate insert table


--Make sure to change the datatypes below to the correct ones, I simply guessed
Create table #TmpInsert(ID  int Identity(1,1) ,CustomerNumber int, CustomerTypeWhenMailed varchar(30), UserLoaded bit, DateLoaded bit, MarketingCode varchar(30),MailDate datetime, Medium varchar(30), OfferType varchar(30))


Insert Into #TMPInsert(CustomerNumber, CustomerTypeWhenMailed, UserLoaded, DateLoaded, MarketingCode, 
MailDate, Medium, OfferType)
Select CustomerNo, 
Customer_Type, 
UserLoaded = 'jfager', 
DateLoaded = convert(varchar(10),GetDate(),101),
MarketingCode = 'GFA901D', 
MailDate = '01/19/2009',
Medium = 'D', 
OfferType = 'GiftMemb Offer'
From lefdata.jde_production.jfager.tmpGFA901D
Where Customer_Type <> 'SD'
And CustomerNo not in (Select CustomerNumber from LifeExtension_PROD.dbo.mktMarketingTouchHistory)
And MarketingCode not in (Select MarketingCode from LifeExtension_PROD.dbo.mktMarketingTouchHistory)
And MailDate not in (Select MailDate from LifeExtension_PROD.dbo.mktMarketingTouchHistory)


Insert Into LifeExtension_PROD.dbo.mktMarketingTouchHistory
select CustomerNumber, CustomerTypeWhenMailed, UserLoaded, DateLoaded, MarketingCode, 
MailDate, Medium, OfferType
from #TmpInsert a
where a.ID between 10001 and 20001 --u can keep changing this or write a cursor to loop for you.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2009-01-14 : 17:56:49

This works out.. grrrreat!!

thanks for the help.

John

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2009-01-14 : 18:03:55

BTW, I noticed that I need to be connected to the server/Db where the insert is going to, b/c I was connected to a different server and it kept erroring out.

Is this an accurate assessment?

thx,

John

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-14 : 18:05:06

Yes, being connected to the correct server/db definitely helps when running queries

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2009-01-15 : 11:01:34

Even if I prefix it with the servername, Dbname and schema?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-01-15 : 12:14:29

In a ideal scenerio (Properly configured Network, and enough horsepower running your sqldb) it should run either way. A lot of times we do not have the option for the ideal, and when it's not it is better to run the query against the connected correct server. You can also try creating a permant table rather then a tmp with proper index's to always use for your imports, along with using a smaller import amount (i.e. 5000 records at a time).

So the answer to your question is idealy it should run regardless, but if it is not try to tweak your configuration using some methods above to eliminate the error.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources