MS SQL scripting help

[pre1014]

Hello everyone, I'm looking for assistance with possibly a simple SQL script.

I currently have the script:
select employid, deducton from upr00500 group by employid, deducton order by employid, sum(sfrfedtx + shfrfica + shfrsttx + sfrlcltx) desc

This returns the results

employid deduction
ACKE0001 INS2
ACKE0001 MED
ACKE0001 401K
ACKE0001 EPU
ACKE0001 NEW
BARB0001 MED
BARB0001 401K
BARB0001 EPU
BARR0001 MED
BARR0001 401K

I need to update a different table (UPR00502) With those values. There is also a field called DEDNSQNC which needs to increment by 1 (starting with 1) and reset when the employid changes. EG

employid deduction dednsqnc
ACKE0001 INS2 1
ACKE0001 MED 2
ACKE0001 401K 3
ACKE0001 EPU 4
ACKE0001 NEW 5
BARB0001 MED 1
BARB0001 401K 2
BARB0001 EPU 3
BARR0001 MED 4
BARR0001 401K 5

Any help or suggestions would be greatly appreciated. Thanks!

 

[mikeblas]

It's fun to help with SQL questions, but without very specific information about the tables and the exact purpose of the query you want, it's pretty hard to do so productively.

You say that you want to update the target table -- with what? You're incrementing this value and resetting it during the update? Or you mean that the value increments through the GROUP BY EmployID ? What if rows don't exist in the target table? What's the key of the target table?

 

[Earp]

I think you're going to be stuck throwing a cursor at the problem. Lookup cursors, and the solution should become appearent.

 

[PPI Typhoon]

Yeah, anytime you have an action that needs to be performed on each record, a cursor will help out. Keep in mind though that cursors are technically "slow" and should only be used when necessary.

For migration scripting, I typically use cursors to print out insert statements that are used to populate the data from one db to another.

here's an example pulled from another site:


DECLARE @mycur CURSOR
DECLARE @test VARCHAR(10)

SET @mycur = CURSOR
FOR
SELECT nummer FROM mytable

OPEN @mycur

FETCH NEXT FROM @mycur INTO @test

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @test – sample statements
FETCH NEXT FROM @mycur INTO @test
END

CLOSE @mycur
DEALLOCATE @mycur

 

[pre1014]

Thanks for your help, I had been working with a cursor, but since I'm a total noob, I can't get it to work as needed. Basically both tables are identical. There is a primary key, but it is a row indicator that is automatically populated. The Employid and deducton are key fields in that one deducton cannot be assigned to an employid more than once.

The only fields in the UPR00502 are Employid, deducton, dednsqnc (which is the only column that needs to be updated) and another column which is not used called SPLITMTHD. There is also the DEX_ROW_ID primary key column which is automatically assigned.

I have been inserting the employid and deducton values into the upr00502 from the upr00500 so the UPR00502 table is populated already. The only thing needed is to update the DEDNSQNC with incrementing integers starting with one, and have the count start over with one for each employid. I have the current script (which does nothing because i'm a noob).

declare @HY char, @DEDN char, @SQN int, @x int
declare UpdateSeq cursor for
select employid, deducton, DEDNSQNC from upr00502
group by employid, deducton, DEDNSQNC
set nocount on
open UpdateSeq
fetch next from UpdateSeq into @HY, @DEDN, @SQN
while (@@fetch_status = 0)
begin
set @x=1
update upr00502 set DEDNSQNC=@x, @x=@x+1 where employid=@HY and deducton=@DEDN
fetch next from UpdateSeq into @HY,@DEDN,@SQN
end
deallocate UpdateSeq
set nocount off

 

[pre1014]

Hey guys, I got it working. I was being dumb and not defining the length of the char and DEDNSQNC was a smallint, not an int. The full script, let me know if you guys see anything that may be "technically" wrong with it:

drop table tempx
select employid, deducton into tempx from upr00500 group by employid, deducton order by employid, sum(sfrfedtx + shfrfica + shfrsttx + sfrlcltx) desc
delete upr00502
insert into upr00502 (employid, deducton)
select employid, deducton from tempx
declare @HY char(15), @ACT smallint, @x smallint
declare UpdateSeq cursor for
select employid,DEDNSQNC from upr00502
group by EMPLOYID,DEDNSQNC having count(*)>1
set nocount on
open UpdateSeq
fetch next from UpdateSeq into @HY, @ACT
while (@@fetch_status <> -1) begin
set @x=0
update upr00502 set DEDNSQNC=@x, @x=@x+1 where EMPLOYID=@HY
fetch next from UpdateSeq into @HY, @ACT
end
deallocate UpdateSeq
set nocount off