Want to use Select query values with insert into .

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

SQL Server Development (2000)

Want to use Select query values with insert into .

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-03-03 : 14:28:22

I want to use the following: which gets all the usernames with a comma.


DECLARE @s VARCHAR(8000)
SELECT @s=''
SELECT @s=@s + [UserName] + ','
from TAB_ccsNetUsers where type = 'CN'
SELECT @s = left(@s, Datalength(@s)-1)
SELECT (@s) as UserNames;

And then want to use all of those usernames with comma for field sentto:


INSERT INTO TABLE_Emails
		(ActionNo,
		DueDate,
		SentTo)
Select  
	actionno, 
	CONVERT(varchar(10),DueDate,101) as DueDate,
	ALlusernamescommadelimit
from tab_ccsnetactions 
where modulerecordid = @ModuleID

Thank you very much for the information.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-03 : 16:30:42

Take the code for creating the comma-delimited string and drop it into a user-defined function. You can then reference it in the SELECT portion of your statement.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-06 : 00:11:33

Hi,
pass your comma separated string to the split function listed in the link and use in clause..
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

INSERT INTO TABLE_Emails
(ActionNo,
DueDate,
SentTo)
Select
actionno,
CONVERT(varchar(10),DueDate,101) as DueDate,
ALlusernamescommadelimit
from tab_ccsnetactions
where modulerecordid in (select data from dbo.split(@ModuleID,','))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-06 : 01:09:48

Dont store comma delimited values in your table. Just store them in multiple rows. Also you dont need to store it in seperate table. Use query with the function
Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-06 : 01:13:10

I think we need more details about what this guy is doing. His original post is not too clear.

reddymade
Posting Yak Master

165 Posts

Posted - 2006-03-06 : 10:08:35

Hello Madhivanan,


DECLARE @s VARCHAR(8000)
SELECT @s=''
SELECT @s=@s + [UserName] + ','
from TAB_ccsNetUsers where type = 'CN'
SELECT @s = left(@s, Datalength(@s)-1)
SELECT (@s) as UserNames;

I have the above one to get the usernames with commadelimit which will be specific to per record basis(getting usernames with comma delimit) for the insert into statement
I want use the above one with the following stored procedure: Can you please help:


CREATE PROCEDURE dbo.USP_SendEmailActions
		(@ModuleID			int)
 AS

INSERT INTO Tab_ccsNetEmailsum
		(toemail,
		body)
Select  u.Email, 
'SentTo: ' + All usernames with comma delimit + char(13)+char(13)+ 'Project: ' + pj.projno + char(13)+char(13)  + 'Contract: ' + ct.contractno + char(13) +char(13)
 + 'Due Date: ' + CONVERT(varchar(10),a.DueDate,101) as body
from tab_ccsnetactions a, tab_ccsnetprograms pg, tab_ccsnetprojects pj,
tab_ccsnetcontracts ct, TAB_ccsNetUsers u, tab_ccsnetcn m 
where a.ModuleRecordID  = m.cnid and
a.assignedto = u.UserID and
m.progid = pg.progid and 
m.projid = pj.projid and
m.contractid = ct.contractid and
a.modulerecordid = @ModuleID
GO

Thank you very much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-07 : 01:38:44

You can replace All usernames with comma delimit with the function that does concatenation part. Refer the link I posted. Also, you dont need to insert them in seperate table. Just use select statement as suggested in that link

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources