SQL Server storing multiple values in a variable

Question

Since a variable can only store a single value.This code throws an error.

Objective: If a certain value isnt there in the other table. Add that value in the table plus other columns.

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

Code is here as follows:

declare @something as varchar(1000)
set @something = (select col1 from d)
while @something not in (select distinct col1 from c)  
begin
   insert into c
   (x,y,z)
   values
   (@something, y, z)
   break
end

UPDATE: Found a solution, used an if statement instead.

Why do you have a BREAK in your WHILE that isn't conditional? This would only loop once, making the WHILE pointless (not that you need a WHILE anyway)l. — Thom A
– Thom A ♦, Commented Nov 27, 2019 at 11:27
SET will accept and assign a scalar (single) value from a query. While SELECT could accept multiple returned values. thats...why yor are getting this error — Akhileshkumar singh
– Akhileshkumar singh, Commented Nov 27, 2019 at 11:32

Seichelis · Accepted Answer · 2019-11-27 11:37:46Z

2

Remarks:

Your example is incorrect - you do not give information on y, z from where they are inserted
you refer to col1 in

while @something not in (select distinct col1 from c)
but then refer to this column as x in

insert into c    (x,y,z)

the reason for error is set @something = (select col1 from d) you can still use that one value if it is ok for you by changing the assignment to this

set @something = (select top 1 col1 from d)

Solution In general, your query is incorrect as you using row-by-row approach instead of set based. Which is a way to go in SQL.

insert into c(col1)--,y,z)
select distinct col1 --, y, z 
from d
where not exists (select col1 from c = d.col1)

if you have duplicates in d for col1 then you need to handle then with distinct. Anyway, the actual result/insert depends on your data and what is in other columns (y,z ...)

answered Nov 27, 2019 at 11:37

Seichelis

414 bronze badges

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

1 Comment

Gordon Linoff Over a year ago

@Swarley . . . The insert . . . select is the right way to do this.

Akhileshkumar singh · Accepted Answer · 2019-11-27 11:42:50Z

2

You're getting Error Just because of SET accept only single value...

SET will accept and assign a scalar (single) value from a query. While SELECT could accept multiple returned values. thats...why yor are getting this error

CREATE TABLE #Temp1
(Col VARCHAR(100))

insert into #Temp1(col1) select  col1 from c

INSERT INTO c(x,y,z) SELECT Col,y,z FROM #Temp1 WHERE Col <>(select distinct col1 from c) ---Here y,z you can pick from your self...it's not from temp table...

DROP TABLE #Temp1

Try this query...

answered Nov 27, 2019 at 11:42

Akhileshkumar singh

1,5322 gold badges10 silver badges21 bronze badges

1 Comment

Maxima Over a year ago

yup I created a temp table for the workaround.Thanks :)

cdrrr · Accepted Answer · 2019-11-27 11:22:38Z

1

Try to use a table variable instead:

DECLARE @MyTable TABLE (
-- specify the desired columns along with the data types
)

Then, use your script.

answered Nov 27, 2019 at 11:22

cdrrr

1,0844 gold badges17 silver badges50 bronze badges

1 Comment

Thom A Over a year ago

OP doesn't need a WHILE either; demonstrating a simple INSERT multi row statement would be great for them.

Ajeet Verma · Accepted Answer · 2019-11-27 14:10:29Z

0

Please try this query ,I hope it will work fine for you.

DECLARE @Something TABLE (Col VARCHAR(1000))
INSERT INTO @Something
SELECT col1
FROM d
WHERE Col NOT IN (
        SELECT DISTINCT col1
        FROM c
        )
BEGIN
    INSERT INTO c (x,y,z)
    VALUES
    SELECT Col,x,y
    FROM @Something
END

answered Nov 27, 2019 at 14:10

Ajeet Verma

1,1231 gold badge9 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL Server storing multiple values in a variable

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related