Insert into Select with Conditions

Question

Post reply

Insert into Select with Conditions

gjoelson 29755

SSC Eights!

Points: 848
More actions
September 10, 2019 at 10:05 pm

#3678931

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 2

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: 246960 More actions · Answer 3

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 4

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: 246960 More actions · Answer 5

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 6

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: 246960 More actions · Answer 7

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 8

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: 76985 More actions · Answer 9

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: 76985 More actions · Answer 10

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 SSC Eights! Points: 848 More actions · Answer 11

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: 76985 More actions · Answer 12

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 SSC Eights! Points: 848 More actions · Answer 13

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: 246960 More actions · Answer 14

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 years, 2 months ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin