none
how to update table

    Question

  • Environment: SQL Server 2008 R2
    Intro: StagingTable consists of three fields:  order_dt, SSN, Score. I also have another database object called dbo.badData which has the following order_dt , SSN, score, flagdata
    Problem: After identifying and flagging the data in the stagingTable, I failed to move or update the data in the badData table. I really dont want to use temp tables and use if statement if the object_id exists  drop table. I ran into many issues and I like to use an update statment where I update the table in the baddata and delete the invalid from the staging table for other uses.

    Code:

    select 
    order_dt, SSN, Score, 
    
    CASE WHEN ISDATE(order_dt) = 0 THEN 'Invalid date' 
    WHEN SSN like '%[^0-9]%' THEN 'Alphanumeric SSN'
    WHEN ssn = '' OR ssn is null THEN 'BLANK SSN'
    WHEN LEN(SSN)>9 THEN 'Invalid SSN'
    WHEN order_dt >= getdate() THEN 'Date Leap'
    When score is null THEN 'Invalid score'
    ELSE 'VALID' END as [FlagStatus]
    
    from StagingTable 
    


    Friday, April 11, 2014 9:57 PM

Answers

  • Try:

    ;WITH CTE AS (select 
    order_dt, SSN, Score, 
    
    CASE WHEN ISDATE(order_dt) = 0 THEN 'Invalid date' 
    WHEN SSN like '%[^0-9]%' THEN 'Alphanumeric SSN'
    WHEN ssn = '' OR ssn is null THEN 'BLANK SSN'
    WHEN LEN(SSN)>9 THEN 'Invalid SSN'
    WHEN order_dt >= getdate() THEN 'Date Leap'
    When score is null THEN 'Invalid score'
    ELSE 'VALID' END as [FlagStatus]
    
    from StagingTable )
    
    DELETE cte
    OUTPUT Deleted.order_dt, Deleted.SSN, deleted.Score, Deleted.FlagStatus INTO BadData (order_dt, SSN, Score, FlagData)
    WHERE FlagStatus <> ' VALID'
    So, this statement will remove bad rows from the Staging table while transferring these bad rows into the BadData table using OUTPUT clause of the DELETE command.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Sandra VO Saturday, April 12, 2014 12:03 AM
    Friday, April 11, 2014 10:04 PM
    Moderator

All replies

  • Try:

    ;WITH CTE AS (select 
    order_dt, SSN, Score, 
    
    CASE WHEN ISDATE(order_dt) = 0 THEN 'Invalid date' 
    WHEN SSN like '%[^0-9]%' THEN 'Alphanumeric SSN'
    WHEN ssn = '' OR ssn is null THEN 'BLANK SSN'
    WHEN LEN(SSN)>9 THEN 'Invalid SSN'
    WHEN order_dt >= getdate() THEN 'Date Leap'
    When score is null THEN 'Invalid score'
    ELSE 'VALID' END as [FlagStatus]
    
    from StagingTable )
    
    DELETE cte
    OUTPUT Deleted.order_dt, Deleted.SSN, deleted.Score, Deleted.FlagStatus INTO BadData (order_dt, SSN, Score, FlagData)
    WHERE FlagStatus <> ' VALID'
    So, this statement will remove bad rows from the Staging table while transferring these bad rows into the BadData table using OUTPUT clause of the DELETE command.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Sandra VO Saturday, April 12, 2014 12:03 AM
    Friday, April 11, 2014 10:04 PM
    Moderator
  • Thanks for the prompt response. There is red line under [dbo].[Invalid_StagingTable] and an error message Msg 8152, Level 16, State 14, Line 4
    String or binary data would be truncated.
    The statement has been terminated.
    I checked the data type in each field and there are identical to the stagingTable. any ideas
    Friday, April 11, 2014 10:29 PM
  • In order to check our query before we post it we need a DDL+DML!

    DDL = Data Definition Language. In our case that is, CREATE TABLE statements for your tables and other definitions that are needed to understand your tables structure and there for let us to test and reproduce the problem in our server. Without DDL no one can execute any query.

    How to get DDL: Right click on the table in Object Explorer and select script table as CREATE. Post these create table scripts here.

    DML = data manipulation language is a family of queries used for manipulating the data it self like: inserting, deleting and updating data. In our case we need some sample data in order to check the query and get result, so we need some indert query for sample data.

    If you post a "create query" for the tables and "insert query" with some sample, then we could help you without Assuming/Guessing. There is a reason that DDL is generally asked for and expected when discussing query problems - it helps to identify issues, clarify terminology and prevent incorrect assumptions.  Sample data also provides a common point of reference for the discussion. A script that can be used to illustrate or reproduce the issue you have, will encourage others to help.

    Please post DDL+DML :-)


    [Personal Site] [Blog] [Facebook]signature

    Friday, April 11, 2014 11:34 PM
    Moderator
  • What is the FlagStatus type and size? I think the error comes from that column, make it big enough (say, 200 chars).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, April 11, 2014 11:39 PM
    Moderator
  • you are smart and very intuitive. Thank you and I dont know how to thank. I have learned T-SQL techniques from you
    Saturday, April 12, 2014 12:02 AM