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: Tuesday, October 14, 2014 10:57 PM
Points: 37, Visits: 151
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: Friday, October 10, 2014 2:48 AM
Points: 723, Visits: 352
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
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:16 PM
Points: 990, Visits: 2,223
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 Monday, August 27, 2012 3:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:27 PM
Points: 1,945, Visits: 3,068
>> INTEGER'm having 2 tables with old and new project numbers, .. <<

This design flaw is called attribute splitting. Your design is wrong dn we need to see DDL to fix it; posting DDL is minimal polite behavior in SQL forum. A good SQL programmer will have a single Projects table, that identifies each project as “old” or “new” instead.

>> If project number in Table A matches with Project Number in Table B, Then INTEGER need to update the Table A project number with its equivalent New project number located in Table B. <<

Why do you wish to Create redundancy? A valid RDBMS has each fact, one way, one place, one time. This is why we switched from file systems to DBMS.

The correct name for the table is not “Incident_Info”; it is a set and needs a plural or collective name and every table has information. Simply use “Incidents” instead. Then read ISO-11179 and learn the correct rules.

The term XREF is also a non_RDBMS structure from the 1970's network databases. We do not use it any more.

>> INSERT need to iterate through all the rows in Table A and Table B, If match found, need to INSERT a record [sic: rows are not records] in a new table which should store Project number(VARCHAR), IsProjNumConverted (Bit (0,1)) <<

We do not use BIT flags in SQL; that was Assembly language. Tag numbers like you would use for a project should not ever be a varying string. Where is the check digit?

To track the history of Projects we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when a Projects had a particular value. Here is the skeleton.

CREATE TABLE Projects_History
(project_id CHAR(9) NOT NULL,
start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME, --null means current
CHECK (start_date <= end_date),
project_status INTEGER NOT NULL,
PRIMARY KEY (project_id, start_date));

When the end_date is NULL, that state of being is still current. You use a simple query for the status on any particular date;

SELECT *
FROM Projects
WHERE @in_cal_date
BETWEEN start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

SQL Server Central is featuring a Stairways Series I wrote a few years ago. You need to study it and learn how to write a correct schema.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1350610
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: Tuesday, October 14, 2014 10:57 PM
Points: 37, Visits: 151
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: Tuesday, October 14, 2014 10:57 PM
Points: 37, Visits: 151
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: Today @ 5:15 AM
Points: 5,424, Visits: 10,083
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