Validate rows in 2 tables and write log

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Validate rows in 2 tables and write log

Validate rows in 2 tables and write log

Rate Topic

Display Mode

Topic Options

Author

Message

maruthipuligandla

Posted Monday, August 27, 2012 5:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, November 17, 2012 2:00 AM
Points: 33, Visits: 121

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

sandeep rawat

Posted Monday, August 27, 2012 7:43 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:06 AM
Points: 681, Visits: 298

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

rVadim

Posted Monday, August 27, 2012 2:20 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, April 19, 2013 3:36 PM
Points: 887, Visits: 2,061

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

thermanson

Posted Monday, August 27, 2012 2:46 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:48 AM
Points: 40, Visits: 263

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

CELKO

Posted Monday, August 27, 2012 3:07 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

>> 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

maruthipuligandla

Posted Wednesday, August 29, 2012 3:43 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, November 17, 2012 2:00 AM
Points: 33, Visits: 121

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

maruthipuligandla

Posted Wednesday, August 29, 2012 3:51 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, November 17, 2012 2:00 AM
Points: 33, Visits: 121

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

John Mitchell-245523

Posted Wednesday, August 29, 2012 4:14 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 4,418, Visits: 7,156

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 »

Permissions