Validate rows in 2 tables and write log

Question

Post reply

Validate rows in 2 tables and write log

User7766

SSCommitted

Points: 1969
More actions
August 27, 2012 at 5:18 am

#280790

Hi,
I'm having 2 tables with old and new project numbers, If project number in Table A matches with Project Number in Table B, Then i need to update the Table A project number with its equivalent New project number located in Table B.
For that i;ve written the below query which worked fine, But now my requirement has been changed to:
I need to iterate through all the rows in Table A and Table B, If match found i need to INSERT a record in a new table which should store Project number(Varchar), IsProjNumConverted (Bit (0,1))
How can i do this using CASE statement>?
UPDATE
dbo.Incident_Info
SET
dbo.Incident_Info .Proj_ID = x.INVC_PROJ_ID
FROM
dbo.Incident_Info i
left outer join Repository.dbo.PROJ_ID_XREF x on x.PROJ_ID=i.Proj_ID
Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sandeep rawat Ten Centuries Points: 1317 More actions · Answer 1

use merge statement to achieve the required .

For more detail

http://technet.microsoft.com/en-us/library/bb510625.aspx

or

http://blog.sqlauthority.com/2010/06/08/sql-server-merge-operations-insert-update-delete-in-single-execution/

rVadim Hall of Fame Points: 3969 More actions · Answer 2

So, you need to compare TableA and TableB and only if match is found insert into TableC ? Match on what? Old and Old? New and New? Both?

How do you determine value of the IsProjNumConverted flag?

--Vadim R.

thermanson Mr or Mrs. 500 Points: 523 More actions · Answer 3

I haven't tested this since there was no test data supplied, but I think this should work.

Insert into MergeTable (Project number, IsProjNumConverted)

Select Coalesce(x.proj_id,i.proj_id) as projID,

Case

When x.proj_id is not null then 1

else 0

End Converted

FROM dbo.Incident_Info i

left outer join Repository.dbo.PROJ_ID_XREF x on x.PROJ_ID=i.Proj_ID

User7766 SSCommitted Points: 1969 More actions · Answer 4

thermanson (8/27/2012)
I haven't tested this since there was no test data supplied, but I think this should work.
Insert into MergeTable (Project number, IsProjNumConverted)
Select Coalesce(x.proj_id,i.proj_id) as projID,
Case
When x.proj_id is not null then 1
else 0
End Converted
FROM dbo.Incident_Info i
left outer join Repository.dbo.PROJ_ID_XREF x on x.PROJ_ID=i.Proj_ID

Hi,

The query is working fine, But i need to display the project number even though there is no match found with project id in table b. The result set is coming like below when i execute your query,

01779.124.034Yes

12682.004.006Yes

NULLNo

12682.004.006Yes

NULLNo

Only for matched records i can see the project number, But i also need to see Project Number for NO case also. How can i acheive this?

Thanks

User7766 SSCommitted Points: 1969 More actions · Answer 5

I need to write both the cases,

If match found in table A and table B, Then i need to write the output like

Table A ProjNumber Table B ProjNumber IsMatchFound

1234 1234 Y

1234 0000 N

I need to trace for which project numbers, there was no match found..

Please help me out..

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

If you can't or won't correct your design as suggested by Joe, use the MERGE statement as suggested by Sandeep.

John