Insert into Select with Conditions

  • gjoelson 29755

    Mr or Mrs. 500

    Points: 587

    Hello,

    I think my requirement is fairly straight forward, but cant seem to get the syntax right or find a solution.

    Im, trying to do a Insert into Select , that will allow duplicates only if a field value in a row is null.

    hope that makes sense. - thanks

    Greg

  • Phil Parkin

    SSC Guru

    Points: 243596

    Without sample data, I can't provide a solution.

    But I can suggest an approach. Build a subquery which selects only those rows which satisfy your criteria and then

    INSERT (cols)
    SELECT (cols from subquery)

     

     

     

    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

    Im using a subquery NOT EXISTS but this only stops duplicates

    Insert into  [RailCarCycle] ([Railcarid],[StartDate])
    select [RailCarid],[LoadDate]
    from [RailCarCycleYard]

    where not exists( select NULL from [RailCarCycle] where [RailCarCycle].[Railcarid] = [RailCarCycleyard].[Railcarid])

    The table contains Railcar ID's (unique)   from being previously used but then status is tagged completed.

    I want to allow insert of duplicates as long a enddate field value in a row is not null or Statusid = 2

    insert

     

  • Phil Parkin

    SSC Guru

    Points: 243596

    Please check this post for an example of how you need to post your question in order to get a working solution in response.

    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

    I looked at you example of how to post a question.........I've provided the code sample that I'm using , I added an image of the table.  I've described my scenario and asked for help.

    I'm not sure what else I need to do - pls advise ?

     

     

  • Phil Parkin

    SSC Guru

    Points: 243596

    I cannot cut & paste from your post into SSMS in order to create sample tables containing sample data.

    If I could do this, I (or someone else) would be in a position to write a query for you.

    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

    Hope this helps....

    Here is the insert I was trying

    Insert into  [RailCarCycle] ([Railcarid],[StartDate])
    select [RailCarid],[LoadDate]
    from [RailCarCycleYard]
    where [RailCarCycle].[Railcarid] = [RailCarCycleyard].[Railcarid] and [RailCarCycle].[EndDate] is not Null and [RailCarCycle].[Statusid] =2

     

    /****** Object:  Table [dbo].[RailCarCycle]    Script Date: 9/11/2019 10:39:12 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[RailCarCycle](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [RailcarID] [nchar](30) NULL,
    [StartDate] [date] NULL,
    [EndDate] [date] NULL,
    [StatusID] [int] NULL,
    PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

     

    INSERT INTO [dbo].[RailCarCycle]
    ([RailcarID]
    ,[StartDate]
    ,[EndDate]
    ,[StatusID])
    VALUES
    (TILX333975 ,
    ,2019-08-01
    ,2019-09-08
    ,1)
    GO

     

    let me know what else you needs

     

    thanks

     

  • Phil Parkin

    SSC Guru

    Points: 243596

    What is required is the DDL for RailCarCycleYard, along with INSERT statements to generate sample data there.

    Then I will be able to run and adjust your query myself ...

    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

    Here you go...sorry about this Im a bit green (but learning)

    USE [SSSUTIL]
    GO

    INSERT INTO [dbo].[RailCarCycleYard]
    ([RailcarID]
    ,[LoadDate]
    ,[StatusID])
    VALUES
    (<RailcarID, nchar(30),>
    ,<LoadDate, date,>
    ,<StatusID, int,>)
    GO
    /****** Object:  Table [dbo].[RailCarCycleYard]    Script Date: 9/11/2019 11:24:08 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[RailCarCycleYard](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [RailcarID] [nchar](30) NULL,
    [LoadDate] [date] NULL,
    [StatusID] [int] NULL,
    PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    thanks

  • drew.allen

    SSC Guru

    Points: 76525

    gjoelson 29755 wrote:

    Hope this helps....

    Here is the insert I was trying

    Insert into  [RailCarCycle] ([Railcarid],[StartDate])
    select [RailCarid],[LoadDate]
    from [RailCarCycleYard]
    where [RailCarCycle].[Railcarid] = [RailCarCycleyard].[Railcarid] and [RailCarCycle].[EndDate] is not Null and [RailCarCycle].[Statusid] =2

    You did not provide a definition and data for RailCarCycleYard.  We cannot run this query.

    Your WHERE clause references a table (RailCarCycle) that is not included in the FROM clause.

    Your SELECT clause references an ambiguous field. RailCarID is in both RailCarCycle and RailCarCycleYard.  The database engine doesn't know which one to use (even though the WHERE clause ensures that they must be the same).  You should ALWAYS qualify your field names.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen

    SSC Guru

    Points: 76525

    As a style note: LOSE THE BRACKETS.  They clutter up the code without adding anything.  Obviously there are some cases where you must use the brackets, but those cases are few and far between with good database design.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • gjoelson 29755

    Mr or Mrs. 500

    Points: 587

    my bad..here is the insert again.

    I though I was qualifying the field name by prefixing it with the table , I originally tried an alias but that gave me a error on the first table.

    USE [SSSUTIL]
    GO

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

  • drew.allen

    SSC Guru

    Points: 76525

    gjoelson 29755 wrote:

    my bad..here is the insert again.

    I though I was qualifying the field name by prefixing it with the table , I originally tried an alias but that gave me a error on the first table.

    USE [SSSUTIL]
    GO

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

    You should always test your code before posting it.  Running this gives "Invalid column name 'TILX333975'."  On further testing you would have found that 2019-08-01 evaluates to 2010 and then you get an error converting the number 2010 to DATE.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • gjoelson 29755

    Mr or Mrs. 500

    Points: 587

    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

     

  • Phil Parkin

    SSC Guru

    Points: 243596

    Here's a full set-up query, using temp tables instead of perms.

    DROP TABLE IF EXISTS #RailCarCycle;

    CREATE TABLE #RailCarCycle
    (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
    ,RailcarID NCHAR(30) NULL
    ,StartDate DATE NULL
    ,EndDate DATE NULL
    ,StatusID INT NULL
    );

    DROP TABLE IF EXISTS #RailCarCycleYard;

    CREATE TABLE #RailCarCycleYard
    (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
    ,RailcarID NCHAR(30) NULL
    ,LoadDate DATE NULL
    ,StatusID INT NULL
    );

    INSERT #RailCarCycleYard
    (
    RailcarID
    ,LoadDate
    ,StatusID
    )
    VALUES
    ('TILX333975', '2019-08-01', 1);

    SELECT *
    FROM #RailCarCycle rcc;

    SELECT *
    FROM #RailCarCycleYard rccy;

    But with just one row of data, I'm not sure how you are expecting us to solve anything. Can you include additional rows of data to exemplify the dupes problem you are facing?

    --Edit: Please excuse the non-2012 IF EXISTS syntax. I can barely remember how to do it the old way any more.

    • This reply was modified 5 days, 11 hours ago by  Phil Parkin.

    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.

Viewing 15 posts - 1 through 15 (of 21 total)

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