Saturday, February 25, 2012
CheckPoint causing long SP durations
are good and low.
Periodically, there are massive INSERTS done in batch that number in the
many thousands.
While monitoring the Checkpoint Pages/Sec counter, whenever the checkpoint
is issued by SQL, the Duration times (which are monitored in Profiler) go up
dramatically causing client issues and then the duration times go back down
to low after the checkpoint has flushed the dirty pages to disk.
Any suggestions? IO to disk?
Don
SQL 2000 SP4
A checkpoint flushes all the dirty pages of the current database to disk.
When you have a lot of modified pages (as in your case when you do massive
inserts via a batch job), it can put considerable strain on the I/O subsystem.
If checkpoints are a serious problem for you, upgrading to a better your I/O
subsystem will always help :-) You can also consider pacing your INSERTs if
you can afford lengthening your batch processing time.
Note that there is a reason why people usually schedule their batch jobs
during a relatively less busy time period.
Linchi
"donsql22222" wrote:
> There are thousands of continuous SELECTS on the tables per min and durations
> are good and low.
> Periodically, there are massive INSERTS done in batch that number in the
> many thousands.
> While monitoring the Checkpoint Pages/Sec counter, whenever the checkpoint
> is issued by SQL, the Duration times (which are monitored in Profiler) go up
> dramatically causing client issues and then the duration times go back down
> to low after the checkpoint has flushed the dirty pages to disk.
> Any suggestions? IO to disk?
> Don
> SQL 2000 SP4
>
|||In addition to what Linchi says (which should be heeded by the way) make
sure your log files are not on the same physical drive array as the data or
tempdb.
Andrew J. Kelly SQL MVP
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:2BD564B3-C3A7-46D5-8F39-F6F4935CA69D@.microsoft.com...[vbcol=seagreen]
>A checkpoint flushes all the dirty pages of the current database to disk.
> When you have a lot of modified pages (as in your case when you do massive
> inserts via a batch job), it can put considerable strain on the I/O
> subsystem.
> If checkpoints are a serious problem for you, upgrading to a better your
> I/O
> subsystem will always help :-) You can also consider pacing your INSERTs
> if
> you can afford lengthening your batch processing time.
> Note that there is a reason why people usually schedule their batch jobs
> during a relatively less busy time period.
> Linchi
> "donsql22222" wrote:
Checkpoint causes need for better IO subsystem?
INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
causes distress for clients and needs to be fixed.
Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
the 10 seconds that the checkpoint takes place..10% have a duration increase.
It's during pereids of batch inserts that this happens which occurs many
times during the day at odd intervals.. I've written about this before and
someone suggested that the batch inserts take place off-peak. Can't be done.
The nature of the business dictates otherwise.
It's also been suggested that a better IO subsystem be installed. We're
using a 168bit/sec controller card and using PerfMon and tracking data
transfered over all of the hard drives, that during these batch inserts, the
total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
controller is able to handle the data.
To answer your other question..The MDF, LDF and C: drive are all on their
own physical separate disk drives and have been defragmented. These are huge
130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
Any Help appreciated.
Don
SQL 2000 SP4
Checkpoints tend to be semi-random writes across the entire database file
footprint. Batch inserts can be sequential or not, depending on whether
your clustered index based on a monotonically increasing column. As such,
the random write capability of the drives comes into play, not the data
throughput limit. Besides, the theoretical limits stated by the
manufacturers are under very narrowly defined conditions. If you believe
manufacturer specs match up to SQL Server usage, I have a bridge I would
like to offer for sale.
Given that you are on a bus architecture disk subsystem, high write activity
can block read activity, this causing your slow response. Five to ten
seconds typically matches the duration of a normal checkpoint. A high-end
disk subsystem with one or more gigabytes of cache and a full-duplex
connection path can help. That translates to a Fibre-Channel connected SAN.
I would also check on the Page Life Expectency performance counter. If it
is low, you may benefit from more physical RAM in the server. This will
allow more data to stay in cache longer, thus eliminating the need to
constantly reload the data from the disks.
Finally, you can change the clustered indexes to use a monotonically
increasing key, thus making the data loads sequential and reducing the
number of page splits, random IO operations, and overall server load during
a data load.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:614A712D-3C95-44F7-9D6A-719788FAAC83@.microsoft.com...
> Using Profiler and PerfMon, when there is a checkpoint, the durations of
> INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
> causes distress for clients and needs to be fixed.
> Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
> the 10 seconds that the checkpoint takes place..10% have a duration
> increase.
> It's during pereids of batch inserts that this happens which occurs many
> times during the day at odd intervals.. I've written about this before and
> someone suggested that the batch inserts take place off-peak. Can't be
> done.
> The nature of the business dictates otherwise.
> It's also been suggested that a better IO subsystem be installed. We're
> using a 168bit/sec controller card and using PerfMon and tracking data
> transfered over all of the hard drives, that during these batch inserts,
> the
> total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
> controller is able to handle the data.
> To answer your other question..The MDF, LDF and C: drive are all on their
> own physical separate disk drives and have been defragmented. These are
> huge
> 130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
> Any Help appreciated.
> Don
> SQL 2000 SP4
>
>
|||The Page Life Expectency performance counter hoovers around 850..not sure if
that's good or bad.
There's still 700M of RAM available and SQL's set dynamically to use all 4GB
of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
taking.
Not sure about how to setup a monotonically increasing key.
Currently, the Clustered index is on multiple cols (2)... (Name, Date)
Would a monotonically increasing key include a new column with an
incrementing sequential value?
such as this?
(newvalue, Name, Date)
don
"Geoff N. Hiten" wrote:
> Checkpoints tend to be semi-random writes across the entire database file
> footprint. Batch inserts can be sequential or not, depending on whether
> your clustered index based on a monotonically increasing column. As such,
> the random write capability of the drives comes into play, not the data
> throughput limit. Besides, the theoretical limits stated by the
> manufacturers are under very narrowly defined conditions. If you believe
> manufacturer specs match up to SQL Server usage, I have a bridge I would
> like to offer for sale.
> Given that you are on a bus architecture disk subsystem, high write activity
> can block read activity, this causing your slow response. Five to ten
> seconds typically matches the duration of a normal checkpoint. A high-end
> disk subsystem with one or more gigabytes of cache and a full-duplex
> connection path can help. That translates to a Fibre-Channel connected SAN.
> I would also check on the Page Life Expectency performance counter. If it
> is low, you may benefit from more physical RAM in the server. This will
> allow more data to stay in cache longer, thus eliminating the need to
> constantly reload the data from the disks.
> Finally, you can change the clustered indexes to use a monotonically
> increasing key, thus making the data loads sequential and reducing the
> number of page splits, random IO operations, and overall server load during
> a data load.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:614A712D-3C95-44F7-9D6A-719788FAAC83@.microsoft.com...
>
>
|||850 is a bit on the low side. 4000-10000 or higher is considered good. As
it is, you are rewriting memory every 14 minutes. Not great.
Identity columns provide monotonically increasing keys. SQL creates a
clustered index out of your primary key by default, but that is not a
requirement. You can separate the two.
Narrow clustered indexes work better. Google the following string for some
excellent articles on clustered index selection and its impact on
performance:
clustered index sql kimberly tripp
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:3F381A8B-84FB-4131-917E-24416EA5FE7E@.microsoft.com...[vbcol=seagreen]
> The Page Life Expectency performance counter hoovers around 850..not sure
> if
> that's good or bad.
> There's still 700M of RAM available and SQL's set dynamically to use all
> 4GB
> of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
> taking.
> Not sure about how to setup a monotonically increasing key.
> Currently, the Clustered index is on multiple cols (2)... (Name, Date)
> Would a monotonically increasing key include a new column with an
> incrementing sequential value?
> such as this?
> (newvalue, Name, Date)
> don
> "Geoff N. Hiten" wrote:
|||donsql22222 (donsql22222@.discussions.microsoft.com) writes:
> There's still 700M of RAM available and SQL's set dynamically to use all
> 4GB of RAM if needed. I'm thinking if SQL needed more RAM, it's there
> for the taking.
Just a check: you have Enterprise Edition? Standard only handles 2GB of
memory.
> Not sure about how to setup a monotonically increasing key.
> Currently, the Clustered index is on multiple cols (2)... (Name, Date)
> Would a monotonically increasing key include a new column with an
> incrementing sequential value?
> such as this?
Name does not sound like it would grow monotonically. :-) Furthermore it
sounds like something I would avoid in a clustred index. Since the
clustered key is also the row-locator in a non-clustered index, a wide
clustered index also make the NC indexes wide and less effecient.
What about the date, is always today's date, or could it be far in
the past? Dates are often good for monotonically clustered indexes.
Of course, there may be other parts of the application that would
perform less well, if there is no clustered index on name.
One alternative is to create the clustered index with a low fill
factor, say 50%. That would create gaps that newly inserted data
can be filled into, and you would thus avoid page splits. This
strategy would require you to routinely rebuild the index, to create
new gaps. I learned this idea from SQL Server MVP Greg Linwood. He
used GUIDs for this, and they are truely random. Nmaes may be less
random and the strategy may work less well for names.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||My strategy (stolen from Kimberly) is to create a clustered key from an
identity column. Lest I provoke the Wrath of Celko(tm), I don't actually
use that column anywhere in the application. It is simply to (a) force
insert order at the end of the table, and (b) provide for a very narrow
clustered key for index lookups and index intersection. It is a physical
characteristic only and has no place in my logical data model. Thus, the
Primary Key is materialized by a non-clustered index.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9780EDE041297Yazorman@.127.0.0.1...
> donsql22222 (donsql22222@.discussions.microsoft.com) writes:
> Just a check: you have Enterprise Edition? Standard only handles 2GB of
> memory.
>
> Name does not sound like it would grow monotonically. :-) Furthermore it
> sounds like something I would avoid in a clustred index. Since the
> clustered key is also the row-locator in a non-clustered index, a wide
> clustered index also make the NC indexes wide and less effecient.
> What about the date, is always today's date, or could it be far in
> the past? Dates are often good for monotonically clustered indexes.
> Of course, there may be other parts of the application that would
> perform less well, if there is no clustered index on name.
> One alternative is to create the clustered index with a low fill
> factor, say 50%. That would create gaps that newly inserted data
> can be filled into, and you would thus avoid page splits. This
> strategy would require you to routinely rebuild the index, to create
> new gaps. I learned this idea from SQL Server MVP Greg Linwood. He
> used GUIDs for this, and they are truely random. Nmaes may be less
> random and the strategy may work less well for names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||The Kimberly webcast of indexing was great. Thanks.
Now, is this a monotonically increasing indexing scheme that I've created?
Dropped all indexes.
I added a new field of type Indentity, decimal.
I then created a clustered unique index on this field.
I then created a nonclustered index on Name, date.
I'm still showing the problem indicated earlier...during checkpoints, some
large increases in duration of some INSERTS and SELECTS.
If this monotonically increasing that I've created looks correct, I might
just leave it in as it sounds like it has some performance benefits.
Thanks,
Don
"Geoff N. Hiten" wrote:
> My strategy (stolen from Kimberly) is to create a clustered key from an
> identity column. Lest I provoke the Wrath of Celko(tm), I don't actually
> use that column anywhere in the application. It is simply to (a) force
> insert order at the end of the table, and (b) provide for a very narrow
> clustered key for index lookups and index intersection. It is a physical
> characteristic only and has no place in my logical data model. Thus, the
> Primary Key is materialized by a non-clustered index.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9780EDE041297Yazorman@.127.0.0.1...
>
>
|||Kimberly is an excellent speaker. She is consistantly one of the top if not
the top rated speaker at any conference where she presents.
I usually use int or bigint for identity columns but decimal should be OK.
I like int and bigint for index intersection tuning. The new index
structure should help with caching and table fragmentation. You still may
have an inadequate IO subsystem, but at least your load isn't artifically
increased by a bad indexing scheme. I have had problems with checkpoints
slowing down regular IO before on SCSI disk arrays. RAID level choice will
have a drastic affect on how rapidly the subsystem can absorb data. See if
you can estimate the size of the checkpoint using performance monitor. If
it is over 300 MB or so, you probably will have to go to a SAN to completely
remove the performance hit.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:7008FA05-F0A5-4736-9C4D-C95FA19F3BA2@.microsoft.com...[vbcol=seagreen]
> The Kimberly webcast of indexing was great. Thanks.
> Now, is this a monotonically increasing indexing scheme that I've created?
> Dropped all indexes.
> I added a new field of type Indentity, decimal.
> I then created a clustered unique index on this field.
> I then created a nonclustered index on Name, date.
> I'm still showing the problem indicated earlier...during checkpoints, some
> large increases in duration of some INSERTS and SELECTS.
> If this monotonically increasing that I've created looks correct, I might
> just leave it in as it sounds like it has some performance benefits.
> Thanks,
> Don
>
> "Geoff N. Hiten" wrote:
|||Kimberly really is an outstanding presenter! I can't say enough positive
things about her indexing webcast. I'm a believer. I listened to it again,
and will again this afternoon as there's things I pickup each time through.
I'm feeling optimistic. I've put monotonically increasing indexes with
bigint on the identify col on all the tables in the DB..even the small ones
that were just heaps. I've got the LDF and MDF on their own defragged
physical drives. And preliminary tests show that now the highest duration is
approx 300ms during the checkpoint where it was 4000-5000ms for "some" of
the INSERTS before this. So i'm hoping!
There's only 9M records in the testDB so I'll not sure if the behavior will
change with the production size of approx 1.5B records in each of 3 tables.
Will be testing it in the next few days.
btw, the size of the checkpoint is only around 150MG...that led me to think
maybe it's not the IO and that maybe it's an indexing performance issue.
Don
"Geoff N. Hiten" wrote:
> Kimberly is an excellent speaker. She is consistantly one of the top if not
> the top rated speaker at any conference where she presents.
> I usually use int or bigint for identity columns but decimal should be OK.
> I like int and bigint for index intersection tuning. The new index
> structure should help with caching and table fragmentation. You still may
> have an inadequate IO subsystem, but at least your load isn't artifically
> increased by a bad indexing scheme. I have had problems with checkpoints
> slowing down regular IO before on SCSI disk arrays. RAID level choice will
> have a drastic affect on how rapidly the subsystem can absorb data. See if
> you can estimate the size of the checkpoint using performance monitor. If
> it is over 300 MB or so, you probably will have to go to a SAN to completely
> remove the performance hit.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:7008FA05-F0A5-4736-9C4D-C95FA19F3BA2@.microsoft.com...
>
>
Sunday, February 12, 2012
Check to see if a row exists in another table before insert
I have a stored procedure that selects invoices based on the date range
delete from BillingCurrent
insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),
GETDATE(), 112))
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent
delete from Billing30
insert into Billing30(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing3
Now, I need to check to see if the row exists in Billing30, if it exists in Billing 30 then I don't want it to insert into BillingCurrent.
You can use something along the lines of the code listed below.
Chris
IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is found
END
ELSE
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is not found
END
|||Ok I understand that and it's very helpful but,
for
IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is found
I want it to do Nothing if the row exists but I don't want it to exit because I need to do this for 5 tables
END
|||There's nothing to stop you using multiple IF statements or even nesting them if you desire, see below - note that I've reversed the logic.
Chris
IF NOT EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is not found
END
IF NOT EXISTS (SELECT 1 FROM Table2 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Table2 record is not found
END
IF NOT EXISTS (SELECT 1 FROM Table3 WHERE <insert criteria>)
etc....
|||ok so I can nest all of the if not exists and then after those just
if exists
end
to make it do nothing if the row already exists?
|||In my previous example none of the code within the BEGIN END blocks will execute if at least one row meeting the relevant criteria exists in each of the tables that you are checking. There's no need to add any additional code to make SQL Server do nothing - if a condition fails then the code within the associated BEGIN END block will not be executed, it's as simple as that. If all of the conditions fail then the batch will complete without executing any of the code within any of the BEGIN END blocks.
It isn't clear from the description of your scenario whether you will need to use nested or multiple IF statements so I can't help any further in that respect without more info.
Chris
|||
Ok, Heres my SP
This prints out (because I use a relation from the billing tables to the InvoiceDetails Table) A Billing statement for each customer, problem is: if a customer has an invoice this month and last month then if prints out two invoices. I'm trying to get it to check each table first billing120 then billing90 then billing60..... So the customer row only gets inserted once.
delete from Billing120
insert into Billing120(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120
delete from Billing90
IF NOT EXISTS (SELECT CustomerID FROM Billing120)--WHERE <insert criteria>)
BEGIN
insert into Billing90(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90
END
delete from Billing60
IF NOT EXISTS (SELECT CustomerID FROM Billing90)
BEGIN
insert into Billing60(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60
End
delete from Billing30
IF NOT EXISTS (SELECT CustomerID FROM Billing90)
BEGIN
insert into Billing30(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30
END
delete from BillingCurrent
IF NOT EXISTS (SELECT CustomerID FROM Billing90)
BEGIN
insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),
GETDATE(), 112))
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent
END
RETURN
|||Maybe you should try a different approach then, see below. This approach allows you to analyze the contents of the tables before performing any INSERTs etc...
Chris
DECLARE @.Billing120Exists BIT
DECLARE @.Billing90Exists BIT
DECLARE @.Billing60Exists BIT
DECLARE @.Billing30Exists BIT
SET @.Billing120Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing120) THEN 1 ELSE 0 END
SET @.Billing90Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing90) THEN 1 ELSE 0 END
SET @.Billing60Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing60) THEN 1 ELSE 0 END
SET @.Billing30Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing30) THEN 1 ELSE 0 END
--Insert logic here that examines the values of the @.BillingExists variables and performs the appropriate actions.
--If you want you can declare additional variables to indicate whether or not rows have subsequently been inserted into one of the tables.
|||Actually I ended up doing it this way,
delete from Billing120
insert into Billing120(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120
delete from Billing90
BEGIN
insert into Billing90(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90
END
delete from Billing60
BEGIN
insert into Billing60(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60
End
delete from Billing30
BEGIN
insert into Billing30(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)
And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30
END
delete from BillingCurrent
BEGIN
insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),
GETDATE(), 112))
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)
And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)
And not exists (select 1 from billing30 e where E.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent
END
RETURN
Thanks for the Help!