SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sub query for insert returns more than one record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Frosty615
Starting Member

13 Posts

Posted - 03/25/2013 :  10:42:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 03/25/2013 :  11:18:47  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/25/2013 :  11:24:09  Show Profile  Reply with Quote
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 - 03/25/2013 :  11:37:49  Show Profile  Reply with Quote
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

Edited by - Frosty615 on 03/25/2013 11:39:11
Go to Top of Page

Frosty615
Starting Member

13 Posts

Posted - 03/25/2013 :  11:40:54  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/25/2013 :  12:44:17  Show Profile  Reply with Quote
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 - 03/29/2013 :  04:29:31  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/29/2013 :  14:05:11  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000