May 3, 2010 at 7:48 am
I need to do an UPSERT. I read the forums and wrote my query:
If Exists (select x from x where x)
Update
Else
Insert
I'm struggling with constraining the insert to only the rows not updated. I'm selecting from and inserting in the same table, but at a different Pay Period level (ex. need to upsert pay period 8 data into pay period 9). Excluding the Pay Period key, there are four other keys: dept (which will always be the same) and account, employee_number and employee_name which MIGHT be different and thus need to be inserted. I keep getting more rows than I should. I'm only talking about a dozen rows involved. I probably could do it manually in less time than I've spent, but its killing me that I can't figure this out.
Thanks all.
May 3, 2010 at 7:57 am
If you could provide the DDL (CREATE TABLE statement(s)) for the table(s), sample data (series of INSERT INTO tablename statements) for the table(s) (NOTE: Sample data, not real data, the represents the problem you are attempting to solve), expected results when the query is executed (a picture here is worth a thousand words), and the code you have currently developed to solve the problem.
Do this and many people will jump at helping you, and you'll get tested code in return.
May 3, 2010 at 8:09 am
Randy i would love to help; these questions are low hanging fruit for me, and helps anyone who reads the thread, but
without the definitions of the tables, anything like an example i post is not going to match what you need; you need a specific answer to help you based on your actual tables.
Give us two things: the definition of the table you are inserting into...that's the CREATE TABLE statement, with the table definitions, the PK, everything like that.
second, where are you selecting from for the insert/update? is it from another table? if it is, we need that too.
here's a simple example of what I'm after:
CREATE TABLE SOMETABLE (PayPeriodkey int, dept int,account int, employee_number int,employee_name varchar(30))
create table ThisWeeksPayroll (PayPeriodkey int, dept int,account int, employee_number int,employee_name varchar(30))
if you give us that, we can give you clean code that has been tested, and most specially, can be peer reviewed by any of the other readers of this thread!
Lowell
May 3, 2010 at 8:53 am
Here's a small sample:
CREATE TABLE emp_labor_ytd(
cpp int NOT NULL,
ppy int NOT NULL,
cost_center char(5) NOT NULL,
nat_acct char(3) NOT NULL,
type char(5) NULL ,
emp_number int NOT NULL,
emp_name varchar(50) NOT NULL,
hrs_reg decimal(12,2) NULL CONSTRAINT DF_emp_labor_ytd_hrs_reg DEFAULT (0),
CONSTRAINT PK_emp_labor_ytd PRIMARY KEY NONCLUSTERED
(
cpp,
ppy,
cost_center,
nat_acct,
emp_number,
emp_name
) WITH FILLFACTOR = 90 ON primary
) ON primary
GO
insert emp_labor_ytd
select 8,2010,'47180','030','PHY',100083,'MARY',72.00
insert emp_labor_ytd
select 8,2010,'47180','030','PHY',108737,'EDUARDO',640.00
insert emp_labor_ytd
select 8,2010,'47180','040','MGMT',108812,'SUSAN',603.90
insert emp_labor_ytd
select 8,2010,'47180','060','CLER',108862,'SHANNON',3.50
insert emp_labor_ytd
select 8,2010,'47181','030','PHY',108629,'KENNETH',640.00
insert emp_labor_ytd
select 8,2010,'47181','080','TECH',105250,'ANITA',274.47
insert emp_labor_ytd
select 8,2010,'47182','030','PHY',108408,'JAMES',640.00
insert emp_labor_ytd
select 9,2010,'47180','030','PHY',108737,'EDUARDO',80.00
insert emp_labor_ytd
select 9,2010,'47180','040','MGMT',108812,'SUSAN',80.00
insert emp_labor_ytd
select 9,2010,'47181','030','PHY',108629,'KENNETH',80.00
insert emp_labor_ytd
select 9,2010,'47181','080','TECH',105250,'ANITA',80.00
insert emp_labor_ytd
select 9,2010,'47182','030','PHY',108408,'JAMES',80.00
This is all in the one table. I want to upsert cpp = 8 data into cpp = 9.
In this example, Eduardo, Susan, Kenneth, Anita & James get updated and Mary & Shannon should get inserted.
Thanks.
May 3, 2010 at 9:02 am
And here's my upsert query where the insert is beating me badly:
declare @cpp int, @ppy int
select@cpp = 9, @ppy = 2010
if exists
(
selectr.cpp, r.ppy, r.cost_center, r.nat_acct, r.emp_number, r.emp_name
fromemp_labor_ytd r join emp_labor_ytd y on (r.cost_center = y.cost_center
and r.nat_acct = y.nat_acct
and r.emp_number = y.emp_number
and r.emp_name = y.emp_name)
wherey.cpp = @cpp
and y.ppy = @ppy
and r.cost_center in ('47180','47181','47182')
)
updatex
setx.hrs_reg = x.hrs_reg + y.hrs_reg
fromemp_labor_ytd x join emp_labor_ytd y on (x.cost_center = y.cost_center
and x.cost_center = y.cost_center
and x.nat_acct = y.nat_acct
and x.emp_number = y.emp_number
and x.emp_name = y.emp_name)
wherex.cpp = @cpp and x.ppy = @ppy
and y.cpp = 8 and y.ppy = 2010
and x.cost_center in ('47180','47181','47182')
else
--insert
selecty.cpp, y.ppy, y.cost_center, y.nat_acct,
y.type, y.emp_number, y.emp_name, y.hrs_reg
fromemp_labor_ytd y join emp_labor_ytd r on (y.cost_center = r.cost_center
and y.nat_acct <> r.nat_acct
and y.emp_number <> r.emp_number
and y.emp_name <> r.emp_name)
wherey.cpp = 8
and y.ppy = 2010
and y.cost_center in ('47180','47181','47182')
group by y.cpp, y.ppy, y.cost_center, y.nat_acct,
y.type, y.emp_number, y.emp_name, y.hrs_reg
May 3, 2010 at 9:09 am
Something is missing. You have provided sample data, but your description is lacking.
If there is base data for the table followed by an "UPSERT" of data, which is what you are actually trying to accomplish, I can't see it in what is provided.
Also, don't use words to desribe what the expected results are, show us the expected results; give us something to compare our results against.
May 3, 2010 at 10:18 am
My table is compiled year-to-date payroll information and is the source for several reports. The cpp column is the pay period. I gave sample data for pay period 8 and pay period 9 for three departments. I need to update/insert the year-to-date data in pay period 9 with the data from pay period 8 for just these three departments. In a couple of weeks I'll have to do the same for pay period 10, and so on. Due to changes in the payroll system for these three departments, the compiled data from pay period 8 (cpp=8) will no longer be included when I compile year-to-date. Thus the need to upsert the cpp = 8 data into the reporting table for each subsequent pay period for the remainder of the year.
Thanks.
May 3, 2010 at 10:40 am
Randy Doub (5/3/2010)
My table is compiled year-to-date payroll information and is the source for several reports. The cpp column is the pay period. I gave sample data for pay period 8 and pay period 9 for three departments. I need to update/insert the year-to-date data in pay period 9 with the data from pay period 8 for just these three departments. In a couple of weeks I'll have to do the same for pay period 10, and so on. Due to changes in the payroll system for these three departments, the compiled data from pay period 8 (cpp=8) will no longer be included when I compile year-to-date. Thus the need to upsert the cpp = 8 data into the reporting table for each subsequent pay period for the remainder of the year.Thanks.
Doesn't anwser my concerns. Provide sample data showing the beginning state of the data. Sample data for the UPSERT. Then what the data should look like when the UPSERT is done.
The above dwescription does nothing for me in clarifying what needs to be done. A picture is worth a thousand words. Show us what the final result should look like when done.
May 3, 2010 at 12:21 pm
Doesn't anwser my concerns. Provide sample data showing the beginning state of the data. Sample data for the UPSERT. Then what the data should look like when the UPSERT is done.
This all takes place within the one table, emp_labor_ytd.
My inital code samples create the begining state of the data.
To isolate the data for the upsert:
select * from emp_labor_ytd where cpp = 8
This is what the data should look like when the UPSERT is done:
--results after upsert
insert emp_labor_ytd
select 9,2010,'47180','030','PHY',108737,'EDUARDO',720.00
insert emp_labor_ytd
select 9,2010,'47180','040','MGMT',108812,'SUSAN',603.90
insert emp_labor_ytd
select 9,2010,'47181','030','PHY',108629,'KENNETH',720.00
insert emp_labor_ytd
select 9,2010,'47181','080','TECH',105250,'ANITA',354.47
insert emp_labor_ytd
select 9,2010,'47182','030','PHY',108408,'JAMES',720.00
insert emp_labor_ytd
select 9,2010,'47180','030','PHY',100083,'MARY',72.00
insert emp_labor_ytd
select 9,2010,'47180','060','CLER',108862,'SHANNON',3.50
Thanks for your help.
May 3, 2010 at 2:03 pm
You had the right general idea, with a slight misunderstanding. You can't check the EXISTS first you have to check the exists for each line. Just do the update first to catch any rows that do exist, then do the insert afterwards for any rows that don't exist.
declare @cpp int, @ppy int
select @cpp = 9, @ppy = 2010
update x
set x.hrs_reg = x.hrs_reg + y.hrs_reg
from emp_labor_ytd x
join emp_labor_ytd y on (x.cost_center = y.cost_center
and x.cost_center = y.cost_center
and x.nat_acct = y.nat_acct
and x.emp_number = y.emp_number
and x.emp_name = y.emp_name)
where x.cpp = @cpp and x.ppy = @ppy
and y.cpp = 8 and y.ppy = 2010
and x.cost_center in ('47180','47181','47182')
--insert
INSERT INTO emp_labor_ytd (cpp, ppy, cost_center, nat_acct, type, emp_number, emp_name, hrs_reg)
select @cpp, @ppy, y.cost_center, y.nat_acct,
y.type, y.emp_number, y.emp_name, y.hrs_reg
from emp_labor_ytd y
where y.cpp = 8
and y.ppy = 2010
and y.cost_center in ('47180','47181','47182')
AND NOT EXISTS (SELECT * FROM emp_labor_ytd r
WHERE (y.cost_center = r.cost_center
and y.nat_acct = r.nat_acct
and y.emp_number = r.emp_number
and y.emp_name = r.emp_name
AND r.cpp = @cpp
AND r.ppy = @ppy))
May 3, 2010 at 3:19 pm
Well no wonder I couldn't get it to work, I had it backwards! Your answer works perfectly.
Thanks for all the help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply