Insert using select - SQL Server Forums

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

Transact-SQL (2005)

Insert using select

Author

Topic

navs
Starting Member

13 Posts

Posted - 2007-07-21 : 09:49:29

How to insert using a select statement?

What is wrong with the following code? What must I change?
---------------------------------------------------------------------
Declare @User_ID uniqueidentifier
INSERT INTO tblMessage values (newid(),@User_ID,
'subja',
'contenta',
1,
'5/5/2007',
u.User_ID,
null,
null)
SELECT u.User_ID
from tblUser u where (Login_ID = 'yash')
-----------------------------------------------------------------
I want to insert into table tblMessage a newid,
newid,
receiver_id,
subject,
message,
direction(a bit value),
sender_id,
null, null
-----------------------------------------------------------------
Both sender_id, and receiver_id are available in tblUser as User_ID. I can retrieve them since I have their login id's available, sender is smacks, and receiver is yash
-----------------------------------------------------------------

Hope it is clear, please help
Thanks
Navs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 10:00:23

INSERT INTO tblMessage
SELECT newid(),
@User_ID,
'subja',
'contenta',
1,
'5/5/2007',
x.User_ID,
null,
null
from tblUser as x where x.Login_ID = 'yash'

Peter Larsson
Helsingborg, Sweden

navs
Starting Member

13 Posts

Posted - 2007-07-21 : 12:27:02

Oh, that works, thanks Peter. One further addition needed to it.

I want the second parameter to be got from the tblUser as well. Basically the second param is the receiver_id and the 7th parameter is the sender_id. The Login_ID of both are available as strings to pass to the query. What and how should I modify?

Thanks
Navs

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-21 : 12:37:34

Try this:

INSERT INTO tblMessage (column list here)
SELECT newid(),
@User_ID,
'subja',
'contenta',
1,
'5/5/2007',
x.User_ID,
null,
null
from tblUser as x where x.Login_ID = 'yash'

navs
Starting Member

13 Posts

Posted - 2007-07-21 : 12:52:15

Peter's code itself works properly but its need one further addition I need help in.

I want the second parameter to be got from the tblUser as well. Basically the second param is the receiver_id and the 7th parameter is the sender_id. The Login_IDs of both are available as strings to pass to the query. What and how should I modify?

Thanks
Navs

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-21 : 18:01:28

Do you have schema of that table?

navs
Starting Member

13 Posts

Posted - 2007-07-22 : 01:56:12

I don't know what a "schema" is. But here is what I need.

teachers must be able to send emails to an individual student, a group or a class. Multiple students will be part of groups, so the teacher can only send to either a single student, a group or a class.

Now I tried a lot of things and with your help and Peter's I managed to write the code for this stored procedure. The table tblMessage can take null values for all three fields, receiver_id, project_id and group_id. What I need to do in the stored proc is this:

1 - Check which of the three is not null and pass null values for the other two.

How can I do this?
Navs

Here is the code I have that has no errors, but expects values for all three of these fields.

INSERT INTO tblMessage SELECT
newid(),
x.User_ID,
@subject,
@content,
'True',
@date,
w.User_ID,
y.Project_ID,
z.Group_ID
from tblUser as w, tblUser as x, tblProject as y , tblGroup as z
where
w.Login_ID = @from
and
x.Login_ID = @to
and
y.Project_Name = @project_name
and
z.Group_Name = @group_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources