Insert into using Variables

Question

I have the following SQL query:

Declare @Total_SysDown as int,
        @Login_SysDown as int



Set @Total_SysDown =    (SELECT SCHED_SYS_DOWN FROM AGT_SC AS S)
Set @Login_SysDown =    (SELECT SYS_DOWN FROM AGT_AC AS A)


Insert Into dbo.DATA(DATE,ID,LNAME,FNAME,Total_SysDown,Login_SysDown)
Select C.DATE,C.ID,E.Last_Name,E.First_Name,@Total_SysDown @Login_SysDown
From dbo.AGT as C Inner Join dbo.EMP as E ON C.ID = E.ID 
Group by C.ID,C.DATE,E.Last_Name,E.First_Name

This or just the variables with the Select statement gives me an error of Subquery returned than 1 value. From what I understand, this means that I should be inserting one record at a time, but I am unsure how to do this. Is there a while statement I should be putting in, or are my variables actually hindering me in the first place?

How are the values from AGT_SC and AGT_AC related to AGT or EMP? Is there an ID — Conrad Frix
– Conrad Frix, Commented Sep 8, 2011 at 20:23

marc_s · Accepted Answer · 2016-02-13 10:37:47Z

4

At least one of the queries:

(SELECT SCHED_SYS_DOWN FROM AGT_SC AS S)
(SELECT SYS_DOWN FROM AGT_AC AS A)

returns more than 1 row, so you cannot assign it to a scalar variable.

As a temporary solution you can do SELECT TOP 1 to make sure each query returns at most one row.

edited Feb 13, 2016 at 10:37

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 8, 2011 at 19:15

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

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

4 Comments

Icarus Over a year ago

+1. Agree with @a1ex07. If you do, SELECT TOP 1 .... will never fail, but I don't know if that's your intention.

Jessica Over a year ago

Alright, but if I want to insert more than one row, what must I do for the select to progressively go down the table rows?

a1ex07 Over a year ago

You can declare variables of type table (or create temp table), populate and use INSERT INTO ... SELECT. You may also avoid creating table variables, and use derived queries. Finally, you may use cursors (usually it's a bad choice, but sometimes unavoidable)

James Johnson Over a year ago

@JP: Your query will execute using TOP 1, but that doesn't necessarily mean that your query will be correct. Just keep that in mind...

PaulStock · Accepted Answer · 2011-09-08 19:16:12Z

1

I don't think the problem is with your INSERT statement at all.

Your problem is in the SET Statements. The SELECT SCHED_SYS_DOWN FROM AGT_SC AS S statement or the other statement is returning more than one value.

When you use SET you are assigning ONE value to the variable. Your SELECT is returning multiple values. Change your query to return only one row.

answered Sep 8, 2011 at 19:16

PaulStock

11.3k9 gold badges51 silver badges53 bronze badges

Comments

James Johnson · Accepted Answer · 2011-09-08 19:19:31Z

1

You're receiving this error because your subqueries return more than one record:

Set @Total_SysDown =    (SELECT SCHED_SYS_DOWN FROM AGT_SC AS S) 
Set @Login_SysDown =    (SELECT SYS_DOWN FROM AGT_AC AS A)

To use variables here, you will need to ensure that only one record is returned from the query, either by using a WHERE clause, TOP 1, or something else. I can't tell for sure by your example, but it sounds like you should be joining those tables to your SELECT query.

SELECT ...
FROM dbo.AGT agt
     INNER JOIN AGT_SC sc
         ON sc.<joining column> = <joining table>.<joining column>
     INNER JOIN AGT_AC ac
         ON ac.<joining column> =  <joining table>.<joining column>

answered Sep 8, 2011 at 19:19

James Johnson

46.1k8 gold badges75 silver badges111 bronze badges

5 Comments

Jessica Over a year ago

I tried joining, but I get the same error. Using Top 1, the query works(with obviously the same variable data for every row). I will try to figure out how to insert a where clause.

James Johnson Over a year ago

What are you trying to get from that table? A single value with matching AGT.ID?

Jessica Over a year ago

Pretty much. I want the date,id,names,and the value for that agent from the variable. I need to do this for every row from my source table.

James Johnson Over a year ago

What is the relationship between AGT and AGT_SC and AGT_AC? One-to-one or one-to-many? Basically, how is it supposed to know which Agent to select from the table?

Jessica Over a year ago

One-to-many. I have date and ID from both agt_sc and agt_ac which match AGT.

Collectives™ on Stack Overflow

Insert into using Variables

3 Answers 3

4 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related