how to move data
-
Hi
I have different people assigned to two same contracts (ex: 70 in contract1 and 50 in contract2 where contract1 and contract 2 are the same!) and i want to bring all people under one contract and delete the duplicate contract.
How do you move data around to achieve this task in sql?
Thanks
Vijay
-
Please post table definition, sample data and expected output.
-
Hi
The below 3 sql statements give the contracts and the people under those contracts.
1: The first statement gives the work contracts for the grade '310110' which contains two same contracts ('NCLA Galley') with different ids (which are the PWC_ID values).
2: The second statement gives out the people which are under one of the PWC_IDs (which is a INHERITED_PWC_ID)
3: The third statement is the same as above.
The problem is there are different people under those two contracts (50 in one and 30 in the other). I want to bring all the people under one contract and eliminate the other one (which is unnecessary).
1: select aView_PERSON_W_C.PWC_ID,Parent_W_C.ENTERPRISE_CONTRACT_NAME,aView_PERSON_W_C.PAY_PERIOD,
Parent_W_C.CONTRACT_HRS_PER_WK,aView_PERSON_W_C.VALID_FROM_DATE,aView_PERSON_W_C.VALID_TO_DATE,
aView_PERSON_W_C.ID_NUMBER,aview_person_w_c.WC_ABSTRACT_TYPE
from aView_PERSON_W_C
Inner Join aView_PERSON_W_C parent_w_c on aView_PERSON_W_C.INHERITED_PWC_ID=Parent_W_C.PWC_ID
where aview_person_w_c.WC_ABSTRACT_TYPE='Grade' and (aview_person_w_c.GRADE_SHT_TITLE in ('310110'))
2: set transaction isolation level read uncommitted
select special_person_w_c.PWC_ID,special_grade.displayText as Grade,
isnull(Title_rank,'')+' '+ isnull(Initials,'') + ' ' + Surname as Name ,
special_person_w_c.grade_sht_title
from special_person_w_c
Left Join AVIEW_PERSON on special_person_w_c.ID_NUMBER=AVIEW_PERSON.ID_NUMBER
Left Join special_GRADE on special_person_w_c.GRADE_SHT_TITLE=special_GRADE.GRADE_SHT_TITLE
where ( special_person_w_c.INHERITED_PWC_ID ='005072.2004318025210380.053')
set transaction isolation level read committed
3: set transaction isolation level read uncommitted
select special_person_w_c.PWC_ID,special_grade.displayText as Grade,
isnull(Title_rank,'')+' '+ isnull(Initials,'') + ' ' + Surname as Name ,
special_person_w_c.grade_sht_title
from special_person_w_c
Left Join AVIEW_PERSON on special_person_w_c.ID_NUMBER=AVIEW_PERSON.ID_NUMBER
Left Join special_GRADE on special_person_w_c.GRADE_SHT_TITLE=special_GRADE.GRADE_SHT_TITLE
where ( special_person_w_c.INHERITED_PWC_ID ='003134.2005140255590420.118')
set transaction isolation level read committed
thanks
Vijay
-
someone pls help!!!!!!
do i have to use insert ... select?
thanks
-
step 1: create a mirror table with NO key.
step 2: insert distinct records into new table
INSERT INTO t2
SELECT DISTINCT key1, f2, f3
FROM t1
step 3: query to find dupes/repeats with conflicting data
SELECT key1
FROM t2
HAVING count(*) > 1
GROUP BY key1
step 4: work the data
if there are too many conflicts, come back and we'll discuss different methods of chosing one record per key.
-
Just a quick blurb on the sql
select min(key1), person_id, 'contract1_id', ...yada...
INTO #t2 --(from above)
FROM contract_connect_table
where contract_id in ('contract1_id', 'contract2_id')
GO
Delete from contract_connect_table
GO
insert into contract_connect_table
select * from #t2
-
Try something like this:
declare @Contract table (ContractID int, ContractName varchar(255))
insert @Contract values (1, 'NCLA Galley')
insert @Contract values (2, 'NCLA Galley')
insert @Contract values (3, 'Some Other Contract')
declare @Person table (PersonID int, PersonName varchar(255), ContractID int)
insert @Person values (1, 'Jeff', 1)
insert @Person values (2, 'Ed', 1)
insert @Person values (3, 'Pete', 2)
insert @Person values (4, 'Dave', 3)
update p
set p.ContractID = new.ContractID
from @Person p
inner join @Contract c
on p.ContractID = c.ContractID
inner join (select p.ContractName, min(p.contractID) ContractID
from @Contract p
inner join @Contract s
on p.ContractName = s.ContractName and p.ContractID <> s.ContractID
group by p.ContractName) new
on c.ContractName = new.ContractName
delete c
from @Contract c
inner join (select p.ContractName, min(p.contractID) ContractID
from @Contract p
inner join @Contract s
on p.ContractName = s.ContractName and p.ContractID <> s.ContractID
group by p.ContractName) new
on c.ContractName = new.ContractName and c.ContractID <> new.ContractID
select * from @Contract
select * from @Person
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply