Insert into Select with Conditions

  • gjoelson 29755

    Mr or Mrs. 500

    Points: 587

    I've included 2 rows below, but not sure that's helpful.

    So maybe an explanation will help more. I'm not sure you will approve of the overall design as a best practice but here it is...

    The Railcarcycle table receives inserts from 2 other tables sources i.e  RailcarYard and RailcarUPD

    The insert coming from RailcarYard creates the first record in the table, RailcarUPD  finds the same record and does an update.

    Im looking to run an SP a few times a day on the RailcarYard that inserts new records into RailcarCycle accepting duplicate 'RailcarID' except if the same RailcarID  has an  'EndDate' is Null or 'StatusID' = 1

     

    hope this helps ?

    USE [SSSUTIL]
    GO

    INSERT INTO [dbo].[RailCarCycleYard]
    ([RailcarID]
    ,[LoadDate]
    ,[StatusID])
    VALUES
    ('TILX333975','2019-08-01',1), ('TILX34475','2019-08-01',1)
    GO

     

    Just to add...Ive added 3 new records of which only the last one should insert, because the first 2 don't meet criteria as per above.

     

    USE [SSSUTIL]
    GO

    INSERT INTO [dbo].[RailCarCycle]
    ([RailcarID]
    ,[StartDate]
    ,[StatusID])
    VALUES
    ('TILX333975','2019-08-01',1), ('TILX34475','2019-08-01',1), ('TILX338499', '2019-08-12',1)
    GO
  • drew.allen

    SSC Guru

    Points: 76595

    gjoelson 29755 wrote:

    It was missing parenthesis on the values...this worked.

    USE [SSSUTIL]
    GO

    INSERT INTO [dbo].[RailCarCycleYard]
    ([RailcarID]
    ,[LoadDate]
    ,[StatusID])
    VALUES
    ('TILX333975'
    ,'2019-08-01'
    ,1)
    GO

    The point is that it should have been tested before being posted.  I knew what the problem was, but other people may not have been able to see it quickly.

    Drew

    PS: ( and ) are parentheses. ' is a (single) quote.  It was missing quotes, not parentheses.

    PPS: Other dialects use parentheses as a more general term and include []{}<> in the term.  I prefer to use the terms (square) brackets for [], braces (or curly brackets) for {} and angle brackets for <>.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • jcelko212 32090

    SSCrazy Eights

    Points: 8855

    The first and we ought to do is correct your DDL. Identity is a table property and not a column, and by definition can never be a key in a logical model. The rest of your column names are incomplete; the start and end dates of what? The postfixes "_status" and "_id" are what ISO 11179 and the metadata committee call attribute properties. Think of them as adjectives on a noun. So you got a list of adjectives with no noun. A key by definition cannot be null, so in your original schema, there's no way to have a valid relational key. There is no attempt to keep any kind of data integrity in your posting. That's why we have datatypes and check constraints.

    Here's a quick correction on what you posted. Remember that 80 to 90% of the work in SQL is done in the DDL.

    CREATE TABLE RailCar_Cycles -- plural name to show it is a set

    (railcar_id CHAR(30) NOT NULL,

    cycle_start_date DATE NOT NULL,

    cycle_end_date DATE,

    CHECK (cycle_start_date<= cycle_end_date)

    foobar_status CHAR(1) NOT NULL

    CHECK (foobar_status IN ('1', ???),

    PRIMARY KEY (railcar_id, cycle_start_date, cycle_end_date)

    );

    INSERT INTO RailCar_Cycles

    VALUES ('TILX333975', '2019-08-01', '2019-09-08','1');

    As a historic note, I see you put a comma in the front of each "card image" and your insertion statements. This is exactly how we formatted our punch card decks back in the 1960s because it made it easy to rearrange the deck and insert a new card. For the last few decades, however, programmers had software that pretty print program text when we push a button. The leading comma is a really bad idea for readability. I see you also forgot to put quote marks around dates and strings. Is there any reason you used in NCHAR for the railcar identifiers? Do you have a lot of Chinese characters in those names? As I remember the ISO standards the industry identifiers for virtually everything consist of a subset of Unicode characters with the Latin alphabet digits and a few punctuation marks. The reason for limiting thousands of industry identifiers to this subset of symbols was so that any alphabet or other writing system covered by Unicode would be able to write them. In particular, units of measure in the metric system were targeted. You also don't like using the; at the end of statements. That's another old-time Sybase convention that is being replaced with components to ANSI/ISO standard SQL requirements by Microsoft.

    Going back and forth between "load_date" and the ("start_date", "end_date") interval pairs in your posting is confusing. Can you clarify what you want to be done?

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • gjoelson 29755

    Mr or Mrs. 500

    Points: 587

    For real !  I think you guys are taking the Piss out of me.

    I appreciate the education and advise on ISO standards and best practises ,  Heaven knows I need to improve  all my basic sql skills, But I simply need help with a script and I've expressed my requirement as best a I humanly can multiple time. (im limited) but still haven't got any help.

    I have copied and re-pasted my description below.....

    The Railcarcycle table receives inserts from 2 other tables sources i.e  RailcarYard and RailcarUPD.  The insert coming from RailcarYard creates the first record in the table, RailcarUPD  finds the same record and does an update.

    Im looking to run an SP a few times a day on the RailcarYard that inserts new records into RailcarCycle accepting duplicate 'RailcarID' except if the same RailcarID  has an  'EndDate' is Null or 'StatusID' = 1

    Not holding my breath but thank you.

     

     

  • Phil Parkin

    SSC Guru

    Points: 243694

    Is this what you're trying to do?

    INSERT #RailCarCycle
    (
    RailcarID
    ,StartDate
    ,StatusID
    )
    SELECT rccy.RailcarID
    ,rccy.LoadDate
    ,rccy.StatusID
    FROM #RailCarCycleYard rccy
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM #RailCarCycle rcc
    WHERE rcc.RailcarID = rccy.RailcarID
    AND
    (
    rcc.EndDate IS NULL
    OR rcc.StatusID = 1
    )
    );

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • gjoelson 29755

    Mr or Mrs. 500

    Points: 587

    Hallelujah !!    Yes, that worked perfectly , thank you Phil.

     

     

     

Viewing 6 posts - 16 through 21 (of 21 total)

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