CODEWITHSUNDEEP
sql-server
ewanc999
ewanc999

Reputation: 13

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

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?

Upvotes: 0

Views: 2633

Answers (3)

Peter Smith
Peter Smith

Reputation: 5552

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

Upvotes: 0

Eliot
Eliot

Reputation: 623

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.

Upvotes: 1

forpas
forpas

Reputation: 164204

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.

Upvotes: 2

Related Questions