insert multiple records with one statement

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 multiple records with one statement

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-09-07 : 14:40:12

hi
assume that we have table for users(user_id,user_name,pwd,...)
and we have a table named mails(mail_id,sender,reciever,message,isReaded) and mail_id is an identity field

scenario is each user can send email for another user and ther is no problem here, but there is a time which admin user wanna send an email to all users, how can i write sql command for that?
please help.
thanks

****<>****

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-07 : 14:40:56

How are you sending an email? Via Database Mail or from your application?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-09-07 : 14:43:38

hi
from my application
simply i wanna use a sqlcommand in asp.net
thanks

****<>****

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-09-07 : 14:44:51

i know how to use sqlcommand but i don't know what command text i have to use

****<>****

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-07 : 14:53:51

Shouldn't you just return a result set back to the application and then have the application loop through it to get the To: list to send one email?

I guess I'm confused as to why you want multiple rows inserted. Could you show us some sample data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-07 : 14:59:49

What do you have so far?

Are you asking for the syntax to send email?

Do you already have that?

Are you asking how to query a table and send the email many times?

You may have to use a CURSOR

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-09-07 : 15:07:48

consider those tables i said before

assume that we have two users,so the users table must be like this:
user_id user_name pwd
1 admin ....
2 u1 ....
3 u2 ....

scenario 1: user "u1" wanna send message "hello u2" to user "u2"
after sending email the mails table must be like this:
mail_id sender reciever message
1 u1 u2 hello u2

scenario 2: admin of the site wanna send the message "hello everybody" to all users
after that mails table must be like this:
mail_id sender reciever message
1 admin u1 hello everybody
2 admin u2 hello everybody

is there a way that does not include using loop in application?

****<>****

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-07 : 16:23:01

Please post the code you currently have that sends the mail

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

CSears
Starting Member

39 Posts

Posted - 2010-09-07 : 16:24:03

Maybe you are looking for something like this?


INSERT INTO MessageTable (Sender, Reciever, Message)
SELECT 'Sender', UserName, 'Message' FROM UsersTable WHERE UserName in (Recipients)

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-09-07 : 16:35:30

quote:
Originally posted by CSears

Maybe you are looking for something like this?

INSERT INTO MessageTable (Sender, Reciever, Message)
SELECT 'Sender', UserName, 'Message' FROM UsersTable WHERE UserName in (Recipients)

YES . U THE MAN
thanks a lot, it's working

****<>****

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-07 : 16:37:52

Well...hardly...how do you know they were sent?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-09-07 : 16:45:30

if u use a sqlCommand object in asp.net ,then after cmd.ExceuteNonQurery() method, it will return an int number which represents number of records affected

****<>****

Sachin.Nand

2937 Posts

Posted - 2010-09-08 : 01:00:21

quote:
Originally posted by mahdi87_gh

if u use a sqlCommand object in asp.net ,then after cmd.ExceuteNonQurery() method, it will return an int number which represents number of records affected

****<>****

Salaam.

But make sure you have set SET NOCOUNT OFF in your SP.
With NOCOUNT ON the command object will always show the value as -1.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-08 : 01:06:08

NOCOUNT OFF is the default.

NOCOUNT ON is preferred though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sachin.Nand

2937 Posts

Posted - 2010-09-08 : 01:15:33

quote:
Originally posted by tkizer

NOCOUNT OFF is the default.

NOCOUNT ON is preferred though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Developers usually tend to set it to ON for better performance.So I thought just in case if it is ON then he should set it to OFF as he needs the no of records affected.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-08 : 01:29:35

Shouldn't you get the count from a different method, such as @@ROWCOUNT or via a .NET method?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sachin.Nand

2937 Posts

Posted - 2010-09-08 : 01:49:27

Have a look at this.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(VS.71).aspx

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

So since the OP is using an INSERT statement setting it to OFF at SP level wont return anything to him by default.
And also I just thought with @@ROWCOUNT and any other method it will need additional code in .net & also adding an additional output parameter in the SP.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-08 : 10:28:59

quote:
Originally posted by Idera
Developers usually tend to set it to ON for better performance.

That might be .NET developers...I do know that in other languages, the the rows affected comes back as a result set...which is bad

I would be hesitant to change anything the way I code sprocs...

There should be NO performance GAIN or LOSS by using an OUTPUT Variable that defines:

Number of Rows affected

SQL Error Code

Sproc User defined Error Code

Sproc Defined Error Message

Location of Error in the Sproc

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources