Insert multiple rows into an SQL table using a select statement?

Question

I have a table that I need to insert multiple rows using data from two other tables I am trying the following

SET IDENTITY_INSERT [dbo].[JobTypeUplifts] ON

INSERT INTO [dbo].[JobTypeUplifts]
(
            [ID]
           ,[JobTypeID]
           ,[CustomerID]
           ,[MarkUpPerc]
           ,[PriceSQM]
           ,[Ref])
     VALUES
           (50
           ,(select ID from JobType where code like '%-d')
           ,(select ID from Customers)
           ,15
           ,0
           ,''

                     )
GO

But I get the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have multiple Job Types and multiple customers.

How do I overcome the problem?

What should the inserted value for the record be if the subquery select ID from JobType where code like '%-d' returns values 1, 10, 101, 493? That is what is happening, one of those subqueries is returning multiple records and the insert statement can't assume that the first returned value should be inserted. — Igor
– Igor, Commented Feb 11, 2020 at 21:44
So the question becomes what do you want to do? Just use the first value? If so then use TOP 1. Use all the values so multiple records are inserted? In that case you need to restructure the statement and use a SELECT instead of VALUES for the insert with proper joins. — Igor
– Igor, Commented Feb 11, 2020 at 21:45
search for looping through sql select result and move the select to outer loop arund the insert statement, — Ali Sheikhpour
– Ali Sheikhpour, Commented Feb 11, 2020 at 21:46
@AliSheikhpour looping should be avoided nearly all of the time. And inserts is absolutely not a time for loops. — Sean Lange
– Sean Lange, Commented Feb 11, 2020 at 21:53

forpas · Accepted Answer · 2020-02-11 21:45:46Z

2

Instead of VALUES use SELECT:

INSERT INTO [dbo].[JobTypeUplifts]
(
            [ID]
           ,[JobTypeID]
           ,[CustomerID]
           ,[MarkUpPerc]
           ,[PriceSQM]
           ,[Ref])
SELECT
           50
           ,j.ID
           ,c.ID
           ,15
           ,0
           ,''
FROM JobType j CROSS JOIN Customers c
WHERE j.code like '%-d'

This will return all the combinations of IDs from both tables JobType and Customers.

answered Feb 11, 2020 at 21:45

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ewanc999 Over a year ago

I have added the line SET IDENTITY_INSERT [dbo].[JobTypeUplifts] ON, but I know get an error 'Violation of PRIMARY KEY constraint 'PK__JobTypeUplifts'. Cannot insert duplicate key in object 'dbo.JobTypeUplifts'. The duplicate key value is (50)' 50 is then next available ID in the table JobTypeUplifts

Eliot · Accepted Answer · 2020-02-11 21:46:18Z

1

An easier syntax is the insert into ... select:

INSERT INTO [dbo].[JobTypeUplifts]
SELECT 50 AS ID
       ,JobType.ID AS  JobTypeID
       ,Customers.ID AS CustomerID
       ,15 as MarkUpPerc
       ,0 as PriceSQM
       ,'' as Ref
FROM (select ID from JobType where code like '%-d') as JobType
    ,(select ID from Customers) as Customers

GO

Hope that helps.

answered Feb 11, 2020 at 21:46

Eliot

6325 silver badges18 bronze badges

Comments

Peter Smith · Accepted Answer · 2020-02-11 21:48:52Z

A simple solution is to modify your query to something like this; you can only select a single value from each of the inner selects:

INSERT INTO [dbo].[JobTypeUplifts]
(
            [ID]
           ,[JobTypeID]
           ,[CustomerID]
           ,[MarkUpPerc]
           ,[PriceSQM]
           ,[Ref])
     VALUES
           (50
           ,(select TOP 1 ID from JobType where code like '%-d')
           ,(select TOP 1 ID from Customers)
           ,15
           ,0
           ,''
           )

However, if you have multiple job types and customers with that job type then you need to setup an initial query to select them all. Assuming that job type can be joined to customers then:

INSERT INTO [dbo].[JobTypeUplifts]
(
            [ID]
           ,[JobTypeID]
           ,[CustomerID]
           ,[MarkUpPerc]
           ,[PriceSQM]
           ,[Ref]
)
SELECT
    50,
    J.ID,
    C.ID,
    15,
    0,
    ''
FROM
    JobType  AS J
    JOIN
    Customer AS C
    ON J.CustomerID = C.CustomerID
WHERE
    J.code LIKE '%-d'

This will give you all the job types and customers for the specified code

Collectives™ on Stack Overflow

Insert multiple rows into an SQL table using a select statement?

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related