how to insert several insert commands, triggers?

Hello, what i want is simple.

This is a simple forum, it has several topics (that the users can create), when a user create a topic, its stored in forum_topics. The user can then view the topic and post a response that is store in forum_answer, the user can also add this to his favorite list, forum_favorites is simple, contains a TopicID that refers to the topic, a username of the user that has the topic on his favorite list and a auto increment id to be able to delete specified topic favorites.

Now my question is: when a user posts a answer to Topic X, i want a predefined message to be sent to post_inbox for all the users that has Topic X in their favorite list.

How can i get MS SQL 2005 to get all the users from Topic X and then loop thru them and insert a new post into post_inbox?

Patrick

Hello,

to insert multiple rows you can use select instead of values in the insert statement. So there is no need for a loop. It would be something like this:

INSERT INTO post_inbox (TopicID, UserName, Message)SELECT ( TopicId,--Is always the ID of Topic X, because of the WHERE UserName,--Is every user that is subscribed to Topic X'Hey, Topix X has a new answer, check it out!'--Maybe a message in their inbox?)FROM forum_favoritesWHERE TopicId = 123--ID op Topic X

So the select will select all users subscribed to Topic X, and you use that as the values for the insert.

Good luck!

|||

Hi Pafo,

From your description, I understand that you need to give each one, whose favorite list has Topic X, a message when any one replies to Topic X.

This can be done in many ways.

1. In the page code, when some one posts a reply, we can invoke some method to inform the observers. This will be more flexible in the logic layer.
2. If you need to do this in the database, we can use Triggers or we can extend the reply stored procedure to do this directly.

In my opinion, option 1 will be better, since users can customize whether to receive this message on their own.

HTH. If anything is unclear, please feel free to mark it as Not Answered and post your reply. Thanks!

How to insert data into table

can some one help me, how can i insert data, using some commands,

scenario,

two server, srv01 and srv02, both have database sales and both a table saleshistory

pseudocode:

insert into srv02.sales.dbo.saleshistory select * from srv01.sales.dbo.saleshistory where id > srv02.sales.dbo.saleshistory.max(id)

In other words, you want to get all records from srv01 that are not on srv02, and insert them into srv02. Correct?

This will help you: http://www.sqlis.com/default.aspx?311

-Jamie

|||

i want to push data from srv01 to srv02, and your example is for same server with two tables, its ok , i have written a small ssis package and its really cooool...

any ways thanks Jamie... :)

|||

The location of the tables for the example is utterly irrelevant. The technique still works.

As long as you've got a solution anyway, that's the important thing!

-Jamie