February 22, 2008 at 2:14 pm
Hello all,
I am writing a stored procedure to add/update data to multiple tables for historical purposes. I would like to have the server insert new records if they don't exist and update a record if it already exists. I know I could do this using a cursor and IF EXISTS, but I would like to know if there is way to do it without a cursor.
This is running on 2000.
Here is a reduced version of a table the data needs to go in:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable (
Pay_Period_Date datetime NOT NULL ,
Pay_Period_Type varchar (1) NOT NULL ,
Employee_ID int NOT NULL,
Gross numeric(18, 2) NOT NULL ,
Federal_Tax numeric(18, 2) NOT NULL ,
CONSTRAINT PK_PayrollHistoryFund PRIMARY KEY CLUSTERED
(
Pay_Period_Date,
Employee_ID,
Pay_Period_Type
) ON [PRIMARY]
) ON [PRIMARY]
Here is some sample data that might be inserted (or updated) in the table:
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== Insert the test data into the test table
INSERT INTO #mytable (Pay_Period_Date, Pay_Period_Type, Employee_ID, Gross, Federal_Tax)
SELECT 'Jan 25 2008 12:00AM','W','5','566.40','66.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','6','163.57','19.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','22','566.40','66.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','49','151.69','12.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','94','110.40','11.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','115','110.33','11.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','116','566.40','19.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','127','106.48','7.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','139','140.14','9.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','144','161.92','19.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','158','566.40','47.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','209','197.01','15.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','246','130.24','10.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','252','74.08','5.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','715','110.00','11.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','719','209.25','0.00' UNION ALL
SELECT 'Jan 25 2008 12:00AM','W','727','566.40','66.00'
Currently I have created a single insert statement for each table, however the program allows the user to repost current data to the history files (this process will be dramatically changed this in a future version). This would cause a primary key violation.
Any ideas/guidance would be appreciated.
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
February 22, 2008 at 2:29 pm
I'm not sure I'm catching all of the details, but it doesn't sound like you need a cursor at all.
Assuming your temp table was the source and you wanted to push data into MyDestTable1 and MydestTable2, you'd be doing :
insert MyDestTable1 (fieldlist)
select fieldlist from
#MyTable mt
where not exists( select null
from MyDestTable1 dest
where mt.payperiod_date=dest.payperiod_date
and mt.employeeID=dest.employeeid
and mt.pay_period_type=dest.pay_period_type)
insert MyDestTable2 (fieldlist)
select fieldlist from
#MyTable mt
where not exists( select null
from MyDestTable2 dest
where mt.payperiod_date=dest.payperiod_date
and mt.employeeID=dest.employeeid
and mt.pay_period_type=dest.pay_period_type)
Update MyDestTable1
set fieldlist=mt.fieldlist
from MyDestTable1 inner join #MyTable mt on
mt.payperiod_date=MyDestTable1 .payperiod_date
and mt.employeeID=MyDestTable1 .employeeid
and mt.pay_period_type=MyDestTable1 .pay_period_type
----------------------------------------------------------------------------------
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?
February 22, 2008 at 3:03 pm
Matt Miller (2/22/2008)
I'm not sure I'm catching all of the details, but it doesn't sound like you need a cursor at all.
Matt, thanks for the quick response. I don't always explain things the best way, but you got enough of the details to put me on a better path. I figured there had to be an easier way, but I had been looking at this so long, I couldn't see the obvious. For simplicity sake, I didn't mention that the dates being inserted come from a different table, but I can work those into the inner select statements in the semi-pseudo code you provided. I will let you know if I run into any snags.
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply