Insert into Select with multiple row from subquery

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 2000 Forums

Transact-SQL (2000)

Insert into Select with multiple row from subquery

Author

Topic

raghavlyon
Starting Member

5 Posts

Posted - 2006-05-20 : 14:24:32

In my database I have three tables, out which one table e_data contains a column LoanNumber which is primary key and it is a foriegn key to timetracker table. timetracker table have folderid as a primary key. last table events is having a foriegn key which is folderid refrence to timetracker table.
table column
e_data loannumber (pk)
timetracker folderid (pk), loannumber (fk)
events eventid (pk), dates, notes, folderid (fk)
timetracker will have only one loannumber, which means loannumber cannot appear in two rows
events can repeat folderid in many rows.

I need to display the loannumber from e_data and notes from events table all in single row, ie one loanumber in one column and all notes against the folderid will be concatenated and displyed.

There is one more table comments where i am suppose to insert the data.
table column
comments id (pk and identity (1,1)), loannumber, allnotes
previously i was writing stored proceudre and getting the data with the cursors running but i was asked to write a query to disply this. I was trying my best but couldn't make it.

my query is:
Insert Into Comments (LoanNumber, eNotes) Select e_data.loannumber,
(Select events.notes from events Where events.folderid=timetracker.folderid)
From timetracker
Inner Join loannumber On e_data.loannumber = timetracker.loannumber

when i am running this query i am getting an 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 am sure there must be a some way which i do not know. i will be very thankful if anyone can guide me getting the right query done.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-20 : 22:18:32

Try this

Insert Into Comments (LoanNumber, eNotes) 
Select 	e_data.loannumber,events.notes
From 	timetracker Inner Join loannumber e_data
	On e_data.loannumber = timetracker.loannumber
	Inner Join events
	on	events.folderid = timetracker.folderid

raghavlyon
Starting Member

5 Posts

Posted - 2006-05-21 : 02:33:32

Thanks, but this query is repeating loannumber in each row.
I want all notes to be concatenated and inserted into one row against the loannumber which mean one loannumber in one column and all concatenated notes in another column of same row.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-21 : 03:46:19

I dont know whether i understood your problem properly it will be great if you can post some sample data with expected output..

Just check out the solutions if it works for you.



Create Function GetConcate(@loannumber int)
Returns Varchar(8000)
As 
BEgin 
Declare @qry varchar(8000)
Set @qry = ''
Select @qry = @qry + events.Notes + ','From timetracker Inner Join loannumber e_data
	On e_data.loannumber = timetracker.loannumber
	Inner Join events
	on events.folderid = timetracker.folderid Where e_data.loannumber = @loannumber
Set @qry = left(@qry,len(@qry)-1)
return @qry 

Insert Into Comments (LoanNumber, eNotes)
Select TimeTacker.LoanNumber,Dbo.GetConcate(TimeTacker.LoanNumber) 
From TimeTracker Inner Join 
Events On Events.FolderID = TimeTacker.FolderID
Group by TimeTacker.LoanNumber
End 

Over here i created the function which will return the concentating in the single row.

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-21 : 04:58:31

also refer to here http://www.sqlteam.com/item.asp?ItemID=11021

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources