Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Validate rows in 2 tables and write log Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 5:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 27, 2015 2:47 AM
Points: 39, Visits: 156
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,
Post #1350320
Posted Monday, August 27, 2012 7:43 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Saturday, June 20, 2015 6:40 AM
Points: 725, Visits: 370
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/
Post #1350388
Posted Monday, August 27, 2012 2:20 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 29, 2015 4:08 PM
Points: 1,031, Visits: 2,268
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?
Post #1350579
Posted Monday, August 27, 2012 2:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 1:16 PM
Points: 43, Visits: 287
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

Post #1350596
Posted Wednesday, August 29, 2012 3:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 27, 2015 2:47 AM
Points: 39, Visits: 156
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.034 Yes
12682.004.006 Yes
NULL No
12682.004.006 Yes
NULL No
NULL No
NULL No
NULL No

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
Post #1351490
Posted Wednesday, August 29, 2012 3:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 27, 2015 2:47 AM
Points: 39, Visits: 156
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
Post #1351493
Posted Wednesday, August 29, 2012 4:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 5,734, Visits: 11,073
If you can't or won't correct your design as suggested by Joe, use the MERGE statement as suggested by Sandeep.

John
Post #1351502
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse