how to insert sp_helptext output into a table?

hi Freinds,
SQL 2000
How can I insert the output of sp_helptext mytriggername into a table?
Sp_helptext out put is a table ! I need to collect the rows into a custom
table.
Thanks in advance,
PatCREATE TABLE #trigger
(
[text] VARCHAR(8000)
)
INSERT #trigger EXEC sp_helptext insContactEmailFormatNull
SELECT * FROM #trigger
DROP TABLE #trigger
Of course, you could have silly triggers that go more than 8000 characters
without a carriage return. In that case I would suggest CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
A
"Patrick" <patriarck@.gmail.com> wrote in message
news:%23OW3DK1oFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi Freinds,
> SQL 2000
> How can I insert the output of sp_helptext mytriggername into a table?
> Sp_helptext out put is a table ! I need to collect the rows into a custom
> table.
> Thanks in advance,
> Pat
>|||> ... CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
I'd suggest a permanent table for that.
ML

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 right justifies leading zeros

I have a business rule in my environment where I need to insert right justified leading zeros in the column. For example if the value to be inserted is 12 than it should be inserted as 0000012. How can I do this

Chintan.

Here it is:

SELECT RIGHT('000000' + CAST(12 as SYSNAME), 7)

Thanks,
Zuomin
|||

The "leading zeroes" issue might be more of a display issue than a storage issue. Is your column a character field, integer field, or what exactly?

If your field is character (or varchar) then you can use concatenation and the RIGHT function to pre-pend your string with zero characters; however, if your field is integer understand that it is stored as a binary encoded integer and the zeroes are understood.

You can do something like zuomin has suggested for display purposes. Also, if you MUST carry this as a zero-filled key, you can store it in the table as a computed column -- again as a CHAR field similar to zuomin's suggestion.

|||

the rule applies to about 9600 rows so....what can be the best solution

Chintan

|||

as of now the columns are in Varchar, but this whole table is getting converted to Flatfile of fixed width and than it is submitted to client. In this case wot shud I allocate the column as Varchar or Integer taking flatfile into consideration.

|||

OK, but what is the datatype of your column?

|||

Varchar.

|||

I would suggest using something like zuomin's RIGHT solution.

|||

A bit of editorial: I have never really liked doing this. This makes it possible to have many different '1' records. Now it is possible to have record '1', '01', '001', etc as valid primary keys to this table. And I have seen this problem manifest several times. For me, I would rather store the integer field and have the application do the formatting.

|||

the problem here is 1. there is no primary keys

2. there is no application which will insert leading zeros

3. it is goin as a simple text files

so now if i use

SELECT RIGHT('000000' + CAST(trtyc as SYSNAME), 7)

this is just a select statement, but i need to insert leading zeros into the db so tht whenever the data goes in to flat files instead of showing 12 it will show 0000012

it might some kinda update statment

Chintan

|||

UPDATE yourTable SET yourVarCharColumn= RIGHT('000000'+yourVarCharColumn,7)

Without WHERE condtion, you will update all your records.

How to insert resultset from SP into a table? or use Select * From

I tried this (and other variations) in Query Analyzer - which did not work.
select * from (exec stp_Report_MonthlyCountHistory_Summary '24', '%',
'3/13/06', '3/13/06')
Is there a way to Select/insert the resultset from an SP? How to do this?
Thanks,
RichRich,
Create a temporary or permanent table with the same structure as the result
of the sp. Use:
insert into #t1
exec dbo.usp_p1 ...
AMB
"Rich" wrote:

> I tried this (and other variations) in Query Analyzer - which did not work
.
> select * from (exec stp_Report_MonthlyCountHistory_Summary '24', '%',
> '3/13/06', '3/13/06')
> Is there a way to Select/insert the resultset from an SP? How to do this?
> Thanks,
> Rich
>

How to insert resultset from SP into a table? or use Select *

Thank you. Yes, I did see that this is the only way to do it. My objective
is that an SP which I did not create and don't want to modify returns a
dataset that I need to order. So I want to read/write that data into a temp
table so that I can order it.
"Alejandro Mesa" wrote:
> Rich,
> Create a temporary or permanent table with the same structure as the resul
t
> of the sp. Use:
> insert into #t1
> exec dbo.usp_p1 ...
>
> AMB
> "Rich" wrote:
>Rich,
If you can order the resultset at the client side, then do it and you will
be liberating some load from your sql server.
AMB
"Rich" wrote:
> Thank you. Yes, I did see that this is the only way to do it. My objecti
ve
> is that an SP which I did not create and don't want to modify returns a
> dataset that I need to order. So I want to read/write that data into a te
mp
> table so that I can order it.
> "Alejandro Mesa" wrote:
>sql

how to insert records where PK is being violated on some records?

Hello,
I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
tbl2.ID is a PK. The problem is that tbl2 already contains some of the
records from tbl1 whcih is causing a PK violation. what tsql statement can
perform the insert without violating PK?
Insert Into tbl2(ID, fld2, fld3)
Select ID, fld2, fld3 from tbl1
where tbl1.ID Not In (Select ID from tbl2)
Any suggestions appreciated.
Thanks,
RichBegin by writing a query that only selects those rows from [tbl1] that do
not exist in [tbl2]. This can be done using a LEFT JOIN on ID. Once done,
you may then insert this result into [tbl2]. This may perform better than
the NOT IN or NOT EXISTS method.
Insert Into
tbl2 (ID, fld2, fld3)
Select
T1.ID,
T1.fld2,
T1.fld3
from tbl1
left join tbl2 as T2
on T2.ID = T1.ID
where
T2.ID is null
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:05766B06-11C8-45D1-BC2E-D1CD0C30576F@.microsoft.com...
> Hello,
> I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
> tbl2.ID is a PK. The problem is that tbl2 already contains some of the
> records from tbl1 whcih is causing a PK violation. what tsql statement
> can
> perform the insert without violating PK?
> Insert Into tbl2(ID, fld2, fld3)
> Select ID, fld2, fld3 from tbl1
> where tbl1.ID Not In (Select ID from tbl2)
> Any suggestions appreciated.
> Thanks,
> Rich|||Thank you all for your replies.
"Rich" wrote:

> Hello,
> I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
> tbl2.ID is a PK. The problem is that tbl2 already contains some of the
> records from tbl1 whcih is causing a PK violation. what tsql statement c
an
> perform the insert without violating PK?
> Insert Into tbl2(ID, fld2, fld3)
> Select ID, fld2, fld3 from tbl1
> where tbl1.ID Not In (Select ID from tbl2)
> Any suggestions appreciated.
> Thanks,
> Rich

how to insert records where PK is being violated on some recor

How about this
Insert Into tbl2(ID, fld1, fld2)
Select Id,fld1, fld2 from
tbl1 t1 join tbl3 t3 on t1.ID = t3.ID
Left Join ttbl2 t2 on t1.id = t2.id
where t2.id Is Null
Would this work also? I forgot that I left out another table.
"JT" wrote:

> Begin by writing a query that only selects those rows from [tbl1] that do
> not exist in [tbl2]. This can be done using a LEFT JOIN on ID. Once done,
> you may then insert this result into [tbl2]. This may perform better than
> the NOT IN or NOT EXISTS method.
> Insert Into
> tbl2 (ID, fld2, fld3)
> Select
> T1.ID,
> T1.fld2,
> T1.fld3
> from tbl1
> left join tbl2 as T2
> on T2.ID = T1.ID
> where
> T2.ID is null
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:05766B06-11C8-45D1-BC2E-D1CD0C30576F@.microsoft.com...
>
>It looks like it would.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:00FBAEDE-5940-48DA-806D-5EB176AD10D7@.microsoft.com...
> How about this
> Insert Into tbl2(ID, fld1, fld2)
> Select Id,fld1, fld2 from
> tbl1 t1 join tbl3 t3 on t1.ID = t3.ID
> Left Join ttbl2 t2 on t1.id = t2.id
> where t2.id Is Null
> Would this work also? I forgot that I left out another table.
> "JT" wrote:
>