Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sub query for insert returns more than one record

Author  Topic 

Frosty615
Starting Member

13 Posts

Posted - 2013-03-25 : 10:42:00
Hello all,
I've got two tables and I want to insert into another table one column from each

I.e.
SignaturesRequired
SIgnatureID INT

PersonalDetails
pdStaffid INT

Each table holds multiple values....
I have this:

INSERT INTO PersonalDetails_Signatures
(SignatureID, Staffid, SigYear)
(SELECT SignatureID, (SELECT PDStaffID FROM PersonalDetails WHERE Deactivated = 0 AND Deleted = 0),
DATEPART(YEAR, GETDATE()) FROM SignaturesRequired WHERE Deactivated = 0)

But the statement fails 'Subquery returned more than 1 value'

How do I resolve this?


Cheers
Frosty

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-25 : 11:18:47
The error message is saying that the inner select (SELECT PDStaffID FROM ...) is returning more than one row.

How is PersonalDetails table related to SignaturesRequired table? There has to be a column in the SignaturesRequired that can be used to join to PersonalDetails table. If there is then you would join on that column - for example like this:
INSERT INTO PersonalDetails_Signatures
(SignatureID, Staffid, SigYear)
SELECT s.SignatureID, p.PDStaffID,DATEPART(YEAR, GETDATE())
FROM SignaturesRequired s inner join PersonalDetails p
on p.JoinColumn = s.JoinColumn
WHERE s.Deactivated = 0 and p.Deactivated = 0 AND p.Deleted = 0
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-25 : 11:24:09
the error is obvious. The below query returns multiple records which cant be used inside a subquery.


SELECT PDStaffID FROM PersonalDetails WHERE Deactivated = 0 AND Deleted = 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Frosty615
Starting Member

13 Posts

Posted - 2013-03-25 : 11:37:49
Sorry I should have elaborated a bit more in my original post
Members of staff have to sign publications every quarter
so, I have 3 tables
One stores PersonalDetails (PDStaffid INT is the primary key)
One stores the publications they need to sign SignaturesRequired (SignatureID INT is the primary key)
and the other stores if the individual has signed the publication
Staffid, SignatureID, HasSignedQuarter1 BIT, HasSignedQuarter2 BIT, HasSignedQuarter3 BIT, HasSignedQuarter4 BIT

Once a year, I need to add a record for each individual for each publication (via a SP)
So if I had four individuals setup and four publications the data that should be inserted is:

Staffid, SignatureID, HasSignedQuarter1 BIT, HasSignedQuarter2 BIT, HasSignedQuarter3 BIT, HasSignedQuarter4 BIT
0 0 0 0 0 0
0 1 0 0 0 0
0 2 0 0 0 0
0 3 0 0 0 0
1 0 0 0 0 0
1 1 0 0 0 0
1 2 0 0 0 0
1 3 0 0 0 0
2 0 0 0 0 0
etc etc

I hope this helps

Frosty

*Edit, sorry, the forum unformatted the table above
Go to Top of Page

Frosty615
Starting Member

13 Posts

Posted - 2013-03-25 : 11:40:54
quote:
Originally posted by visakh16

the error is obvious. The below query returns multiple records which cant be used inside a subquery.


SELECT PDStaffID FROM PersonalDetails WHERE Deactivated = 0 AND Deleted = 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Yes, apologies, like I said, I should have elaborated more in my OP. I understand the error and why it is happening, what I need help with is making the statement do what I want it to

Frosty
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-25 : 12:44:17
quote:
Originally posted by Frosty615

Sorry I should have elaborated a bit more in my original post
Members of staff have to sign publications every quarter
so, I have 3 tables
One stores PersonalDetails (PDStaffid INT is the primary key)
One stores the publications they need to sign SignaturesRequired (SignatureID INT is the primary key)
and the other stores if the individual has signed the publication
Staffid, SignatureID, HasSignedQuarter1 BIT, HasSignedQuarter2 BIT, HasSignedQuarter3 BIT, HasSignedQuarter4 BIT

Once a year, I need to add a record for each individual for each publication (via a SP)
So if I had four individuals setup and four publications the data that should be inserted is:

Staffid, SignatureID, HasSignedQuarter1 BIT, HasSignedQuarter2 BIT, HasSignedQuarter3 BIT, HasSignedQuarter4 BIT
0 0 0 0 0 0
0 1 0 0 0 0
0 2 0 0 0 0
0 3 0 0 0 0
1 0 0 0 0 0
1 1 0 0 0 0
1 2 0 0 0 0
1 3 0 0 0 0
2 0 0 0 0 0
etc etc

I hope this helps

Frosty

*Edit, sorry, the forum unformatted the table above


I think what you need is a cross join

something like

INSERT ThirdTableNameHere
SELECT p.StaffID,s.SignatureID,0,0,0,0
FROM Signatureequired s
CROSS JOIN PersonDetails p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Frosty615
Starting Member

13 Posts

Posted - 2013-03-29 : 04:29:31
Sorry for the late response - thank you, the cross join was the ticket!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-29 : 14:05:11
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -