insert a new row(s) into sql table A from table B

  • Hello,

    I am using SQL Server 2014. I have two table the first one is Table_A and Table_B.

    I want to copy row(s) from Table_B into Table_A based on a condition. The first condition is a sequence number "OYHSSQA" and the second is Type "OYNMTP" I want to have the row from Table_B copy to Table_A. The sequence number "OYHSSQA" will appear twice, but the Type "OYNMTP" will have a unique letter ("A" or "S") I want to have the combine condition for example:

    From Table_B

    OYHSSQA OYNMTP

    1578 A

    1578 S

    If Table_A does not have both rows then I want Table_B to insert the rows into Table_A.

    I want both rows to appear in Table_A, below is my SQL query, but I unable to get any results. Any tips will be appreciated. Thank you in advance

    SELECT OYHSSQ, OYNMTP, OYNAME, OYADD1, OYADD2, OYCITY, OYSTAT, OYZIP4, OYCNTC, OYPHAR, OYPHXX, OYPH04, OYPHEX, OYFL18, OYADD3, OYCTID, OYCTCD, OYADD4, OYPHNA

    FROM TABLE_B

    WHERE NOT EXISTS (SELECT OYHSSQ, OYNMTP, OYNAME, OYADD1, OYADD2, OYCITY, OYSTAT, OYZIP4, OYCNTC, OYPHAR, OYPHXX, OYPH04, OYPHEX, OYFL18, OYADD3, OYCTID, OYCTCD, OYADD4, OYPHNA

    FROM Table_A

    WHERE TABLE_B .OYHSSQ = Table_A.OYHSSQ AND TABLE_B .OYNMTP = Table_A.OYNMTP)

  • If I understood correctly then this should do the job

    😎

    SELECT

    TB.OYHSSQ

    ,TB.OYNMTP

    ,TB.OYNAME

    ,TB.OYADD1

    ,TB.OYADD2

    ,TB.OYCITY

    ,TB.OYSTAT

    ,TB.OYZIP4

    ,TB.OYCNTC

    ,TB.OYPHAR

    ,TB.OYPHXX

    ,TB.OYPH04

    ,TB.OYPHEX

    ,TB.OYFL18

    ,TB.OYADD3

    ,TB.OYCTID

    ,TB.OYCTCD

    ,TB.OYADD4

    ,TB.OYPHNA

    FROM TABLE_B TB

    LEFT OUTER JOIN Table_A TA

    ON TB.OYHSSQ = TA.OYHSSQ

    AND TB.OYNMTP = TA.OYNMTP

    WHERE TA.OYHSSQ IS NULL;

    Make certain you have an index on Table_A(OYHSSQ,OYNMTP)

  • Thanks Eirikur Eiriksson I will give it a try. I appreciate your help.

  • alex_martinez (10/23/2016)


    Thanks Eirikur Eiriksson I will give it a try. I appreciate your help.

    You are most welcome, and please ping back if you have any problems

    😎

  • I believe, there is also nothing wrong in query you built. Why is it not working?

    SELECT

    OYHSSQ,

    OYNMTP,

    OYNAME,

    OYADD1,

    OYADD2,

    OYCITY,

    OYSTAT,

    OYZIP4,

    OYCNTC,

    OYPHAR,

    OYPHXX,

    OYPH04,

    OYPHEX,

    OYFL18,

    OYADD3,

    OYCTID,

    OYCTCD,

    OYADD4,

    OYPHNA

    FROM TABLE_B

    WHERE NOT EXISTS (

    SELECT

    OYHSSQ, OYNMTP, OYNAME, OYADD1, OYADD2,

    OYCITY, OYSTAT, OYZIP4, OYCNTC, OYPHAR,

    OYPHXX, OYPH04, OYPHEX, OYFL18, OYADD3,

    OYCTID, OYCTCD, OYADD4, OYPHNA

    FROM Table_A

    WHERE TABLE_B .OYHSSQ = Table_A.OYHSSQ AND TABLE_B .OYNMTP = Table_A.OYNMTP

    )

    Regards
    VG

  • >> I want to copy row(s) from Table_B into Table_A based on a condition. The first condition is a sequence number "OYHSSQA" and the second is Type "OYNMTP" I want to have the row from Table_B copy to Table_A. The sequence number "OYHSSQA" will appear twice, but the Type "OYNMTP" will have a unique letter ('A' or 'S') I want to have the combine condition for example: <<

    Did you read the instructions for posting on the form? Where is your DDL? It is very hard to program from a narrative, especially a bad one. Why do you think those column names are clear, precise and meaningful?

    But more than that the whole goal of databases (not just RDBMS!) is to reduce redundancy, not increase it the way you are doing. This condition should be computed at query time or in a view. What you are doing is a way we used to do things with punch cards (yes, I am that old). Make a copy of the card and put it in the other deck. I see you use short names for columns, just like we did with Fortran I , which could only hold six letters in a name. You do not follow ISO 11179 rules. And there even in upper case letters, just like the punch cards.

    Post some DDL, as per form rules, and will try to really help you otherwise were just going to have to start guessing at what you meant. A sure sign of this problem is when someone begins a reply with "If I understand you correctly, then.."

    Another problem you have is you do not understand how the EXISTS() predicate works. Why did you put a column list in the select clause of this predicate? Think about it. 😉

    RDBMS is based on logic, and in logic. The foundation is a principal called the Law of Identity ("to be is to be something in particular; to be nothing in particular or anything in general is to be nothing at all."). This means there is no such thing as a generic "type", generic "name", etc. this is enforced by the ISO 11179 naming rules.

    You describe a constraint in a narrative, instead of enforcing it in code. An SQL programmer would have written in his DDL with UNIQUE(oyhssqa, oynmtp).

    If you have this unique this constraint, then you can write a simple view to find the rows that have both {'A', 'S'} in a simple view.

    CREATE VIEW Foobar

    AS

    SELECT * -- needs real column list

    FROM Punchcards

    GROUP BY oyhssqa -- oyster_highschool_square_a? I cannot figure out your data element names

    HAVING COUNT(*) = 2;

    If Table_A does not have both rows then I want Table_B to insert the rows into Table_A.

    I want both rows to appear in Table_A, below is my SQL query, but I unable to get any results. Any tips will be appreciated. Thank you in advance

    SELECT OYHSSQ, OYNMTP, OYNAME, OYADD1, OYADD2, OYCITY, OYSTAT, OYZIP4, OYCNTC, OYPHAR, OYPHXX, OYPH04, OYPHEX, OYFL18, OYADD3, OYCTID, OYCTCD, OYADD4, OYPHNA

    FROM TABLE_B

    WHERE NOT EXISTS

    (SELECT OYHSSQ, OYNMTP, OYNAME, OYADD1, OYADD2, OYCITY, OYSTAT, OYZIP4, OYCNTC, OYPHAR, OYPHXX, OYPH04, OYPHEX, OYFL18, OYADD3, OYCTID, OYCTCD, OYADD4, OYPHNA

    FROM Table_A

    WHERE TABLE_B .OYHSSQ = Table_A.OYHSSQ

    AND TABLE_B.OYNMTP = Table_A.OYNMTP)

    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

  • CELKO

    I am just amazed in how long you took the time to write this post, like what I am doing NOW.

    Old man like you who are playing the "Post Police" I just don't like your attitude.

    Funny part about all this is I worked with a lot of senior programmers I enjoy working with and are great mentors.

    They are nothing like you! Next time you see my reply just move on. I don't need to have you reply period! or give me a lecture.

    "Seriously" go pick on a junior staff member "if you haven't already", people like you pick on the little guys too much "really".

    Just move on go on to another post and whine to the next person.

    Help and comments like yours I don't need or nobody needs. Don't mask how you trying to help! I know your type.

    There was only two data columns I was really interested "OYHSSQA", which I explained it's a sequence number and the other "OYNMTP" is a unique lettering column. I don't think I need to explain anymore to you, especially when some else guessed it correctly.

    A top notch person like yourself with all your experiences should able to guess. If you don't have enough data from a post move on.

    Also, The kind person who helped me guessed "correctly" I was able to use it and guess what it works! and that's all I need from a forum. Not a whining reply. I will make sure I will submit a DLL. Fortunately I really don't use this forum too much and probably not in the near future.

    Seriously take a Stress Pill.

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

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