Drop and create procedure

I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?

Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...

ThanksWhat is the trigger for?

There's no way I know of to disable the trigger...but what's the big deal with

DROP TRIGGER

EXEC Sproc

CREATE TRIGGER

The only thing is, whatever the trigger is for, it's there for a reason, and wouldn't dropping cause you any data integrity issues?|||Actually, it can be done in SQL 2000. I have never tried it, but there is ALTER TABLE syntax for enabling/disabling a trigger.

As Brett pointed out, though, you want to be sure that not only your process but any other process accessing the table will not be adversely affected by the sudden disabling of the trigger.|||No Sheet!

Still, once I've put a trigger in place, I've never had a need (or want) to remove it.

You might want to consider some alternatives

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT
AS
BEGIN
UPDATE t SET t.Col2 = t.Col2 * 2
FROM myTable99 t JOIN inserted i ON t.Col1 = i.Col1
END
GO

INSERT INTO myTable99(Col2) SELECT 1

SELECT * FROM myTable99
GO

ALTER TABLE myTable99
DISABLE TRIGGER myTrigger99
GO

INSERT INTO myTable99(Col2) SELECT 1

SELECT * FROM myTable99
GO

ALTER TABLE myTable99
ENABLE TRIGGER myTrigger99
GO
INSERT INTO myTable99(Col2) SELECT 2

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO

Drop all indexs with a stored procedure

I want to call a script/sproc at the beginning of a nightly ETL process that
will drop all indexes in a user-defined database.
At the end of the ETL process I will call a stored procedure (I already
have) to recreate the indexes.here's an undoc trick:
exec master..xp_execresultset N'select
''drop index ''+quotename(o.name)+''.''+quo_tename(i.name)
from sysindexes i join sysobjects o on i.id=o.id
where o.type=''U''
and i.indid > 0 and i.indid < 255
and (i.status & (32|64|2048|4096))=0
',N'Your_db_name_goes_here'
For more info on this trick:
http://rac4sql.net/xp_execresu_ltset.asp
-oj
"LP" <LP@.discussions.microsoft.com> wrote in message
news:FCD018BC-D7E2-446B-A49B-CF99FC3F160B@.microsoft.com...
>I want to call a script/sproc at the beginning of a nightly ETL process
>that
> will drop all indexes in a user-defined database.
> At the end of the ETL process I will call a stored procedure (I already
> have) to recreate the indexes.
>

Drop & Create sProc

I generated SQL Script to drop and recreate some tables in my database
that I will want to perform monthly. I would like to put the script into a
sproc, however when I try to compile it I receive an error that the tables
and indexes already exist in my database.
Is there any way around this other than dropping all the tables before I
compile
the sproc?
Thanks,
MarcEncapsulate the CREATE / DROP statements within an IF ?
IF OBJECT_ID('[YourObject]','U') IS NOT NULL
BEGIN
DROP ....
CREATE ...
END
"Marc Miller" wrote:

> I generated SQL Script to drop and recreate some tables in my database
> that I will want to perform monthly. I would like to put the script into
a
> sproc, however when I try to compile it I receive an error that the tables
> and indexes already exist in my database.
> Is there any way around this other than dropping all the tables before I
> compile
> the sproc?
> Thanks,
> Marc
>
>|||Marc Miller (mm1284@.hotmail.com) writes:
> I generated SQL Script to drop and recreate some tables in my database
> that I will want to perform monthly. I would like to put the script
> into a sproc, however when I try to compile it I receive an error that
> the tables and indexes already exist in my database.
> Is there any way around this other than dropping all the tables before I
> compile the sproc?
Unless you are running SQL 6.5, you should not get that error.
I suspect that you have something that looks like:
CREATE PROCEDURE yoursp AS
CREATE TABLE abc
CREATE INDEX abc_ix ON abc(def)
go
CREATE TABLE xyz
CREATE INDEX xyz_ix ON xyz(www)
go
This is a script that first creates a procedure, and then creates a table.
This is because the script includes "go" which is an instruction to
the query tool that this is where the batch ends. "go" is not an SQL
command.
Thus, you would have to remove the "go" to include all code in the
stored procedure. However, you may then find that run into other
problems, because you have commands that must be in separate batches.
I would suggest that you store the script as a separate file. This is
would you should with stored procedures as well. The database should
be seen as a binary repository.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx