November 19, 2007 at 7:33 am
I've used update to add data to records that fall between 2 dates, is it possible to use INSERT in the same way
update
set field1 = 'test' where [date1] between '01/01/2007' AND '11/01/2007'
November 19, 2007 at 8:14 am
mick burden (11/19/2007)
I've used update to add data to records that fall between 2 dates, is it possible to use INSERT in the same wayupdate
set field1 = 'test' where [date1] between '01/01/2007' AND '11/01/2007'
When you use update, you use the date range to restrict the rows that you are modifying. When you insert a new row, since you are not "reusing" existing rows, you do not need to specify a date range, since you have full control over what you are inserting. I.e. when you insert the row, just make sure that the row that is inserted is using a data that is in the range you want.
There are ways to ensure that all dates in a column are within a specific range. If this is what you'd like to achieve you could read about check constraints.
Regards,
Andras
November 19, 2007 at 8:23 am
thanks Andras, I was wondering if I need to use some kind of loop within my Store Procedure to do the inserts?
November 19, 2007 at 9:40 am
Mick - are you trying to build a row for each day between 2 dates?
If so - you probably want to use something involving what is known here as a Tally Table. A "tally table" is a table containing simply an unbroken sequence of numbers. This is useful when doing something like what you're talking about.
To build a Tally table, use something like this:
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
I'd recommend you hold onto it, but you don't really have to if you don't want to.
Once you have a tally table, your syntax becomes something like:
insert
(field1,datefield)
select
'test',
dateadd(day,tally.n,'12/31/2006')
from tally
where dateadd(day,tally.n,'12/31/2006')<'11/2/2007'
See - no loop....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 19, 2007 at 9:42 am
That's brilliant, thanks Matt
November 19, 2007 at 5:04 pm
Cool... glad to see someone else using a Tally table...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2007 at 9:40 pm
Hey - if you're going to give me a new stapler, expect to find some things stapled.....:D
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 21, 2007 at 4:10 pm
Matt Miller (11/19/2007)
Hey - if you're going to give me a new stapler, expect to find some things stapled.....:D
Heh! If that's true, guess I need to give you a lawnmower and beer making machine 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2007 at 7:57 pm
Jeff Moden (11/21/2007)
Matt Miller (11/19/2007)
Hey - if you're going to give me a new stapler, expect to find some things stapled.....:DHeh! If that's true, guess I need to give you a lawnmower and beer making machine 😛
Fine - just don't do both on the same day...I'm not sure the lawn could take that kind of abuse:Whistling:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 28, 2007 at 3:53 am
Matt Miller (11/19/2007)
Mick - are you trying to build a row for each day between 2 dates?If so - you probably want to use something involving what is known here as a Tally Table. A "tally table" is a table containing simply an unbroken sequence of numbers. This is useful when doing something like what you're talking about.
To build a Tally table, use something like this:
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
I'd recommend you hold onto it, but you don't really have to if you don't want to.
Once you have a tally table, your syntax becomes something like:
insert
(field1,datefield)
select
'test',
dateadd(day,tally.n,'12/31/2006')
from tally
where dateadd(day,tally.n,'12/31/2006')<'11/2/2007'
See - no loop....
Except that you need to express dates in universal YYYYMMDD format to avoid conflict with local date settings 🙂
Failing to plan is Planning to fail
December 28, 2007 at 4:27 am
that's great, thanks
December 28, 2007 at 11:54 am
Except that you need to express dates in universal YYYYMMDD format to avoid conflict with local date settings
Heh... yeah... then there's that...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply