query that insert and update rows - without cursor

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)

query that insert and update rows - without cursor

Author

Topic

asafg
Starting Member

39 Posts

Posted - 2009-07-12 : 06:24:24

I have a table with the fields:

id ,startDate, endDate, action

when the endDate>= 31/12/1999
I want to set the end date to 31/12/1999
and add a new row:
id,1/1/2000, endDate(original),action

I still cover the time period I had before - but using 2 records and not just one.

the reason for all this is that records after 1/1/2000 should be calculated according to new rules.

The Question:
It's very easy to make this happen with a cursor but it takes forever... is there a way to do the same job with a query?

Thanks
Asaf

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-12 : 08:25:43

seems like this


INSERT INTO YourTable
SELECT id ,'1/1/2000', endDate, action
FROM YourTable
WHERE endDate>= '31/12/1999'

UPDATE t
SET t.endDate='31/12/1999'
FROM YourTable t
WHERE endDate>= '31/12/1999'
AND StartDate <> '1/1/2000'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources