Verifying that table is empty 2

[swhitten]

I have a stored procedure that does an insert into an existing table. However, I want to be sure that table is empty before I insert into it. I can't just drop the table each time, because it is being used in replication.

I started my stored procedure with a delete from, but I would really like an extra safety measure to ensure that it is empty before the insert into runs. How can I do that?

Thanks in advance for your help!
Sherry

Here's the first part of my stored proc:

DELETE FROM rfs1.dbo.gmsolrpt
INSERT INTO GMSOLRPT
SELECT TOP 100 PERCENT . . .

 

[RobertT687]

I wouldn't worry too much about there being any data in the table. The unqualified DELETE should take care of everything. If, however, some referential integrity constraint prevents the delete from working you could try something like:

IF (SELECT COUNT(*) FROM rfs1.dbo.gmsolrpt) > 0
begin
RAISERROR ('Job expects the table to be empty.', 16, 1)
RETURN
end

or something like that.

 

[psprague]

I think this optimizes a bit better than count(*).

IF EXISTS(SELECT * FROM mytable)

or the negative:

IF NOT EXISTS(SELECT * FROM mytable)

 

[swhitten]

Thank you both for your responses. psprague, please clarify your response a bit for me. I am NOT a programmer - just a Crystal reports designer who needs to move some data around to write reports off of it

What do I do after the if exists or if not exists?

Thank you!
Sherry

 

[psprague]

If there is no fk restraints on the table then simply deleting blindly from the table might not a bad option (as written in your example) butI have not ever done any performance testing on a 'do nothing' delete statement. If you wanted to 'test' the table prior to the delete then:

IF EXISTS(SELECT * FROM mytable)
BEGIN
DELETE mytable --or do some other work
END

INSERT INTO mytable....

 

[swhitten]

Here's what I have now:

DELETE FROM rfs1.dbo.gmsolrpt

IF EXISTS(SELECT * FROM rfs1.dbo.gmsolrpt)

BEGIN
DELETE FROM rfs1.dbo.gmsolrpt
END

INSERT INTO GMSOLRPT

SELECT TOP 100 PERCENT . . .

I think I can probably get rid of the first delete statement (redundant), but does it hurt anything?

You guys are wonderful! Thanks!

 

[psprague]

That is indeed redundant and not needed. The IF EXISTS statement would always return a false (if there are no concurency issues i.e. other processes inserting into the table) I'd remove the first one.

 

[swhitten]

Thanks again! I am up and running

 

[SonOfEmidec1100]

Another point of view, I wouldnt bother with IF EXISTS, just go for it using

TRUNCATE TABLE rfs1.dbo.gmsolrpt

much faster than DELETE FROM

 

[swhitten]

Thank you once again. I have changed my stored proc to include the truncate table. Excellent suggestion.

 

[SQLSister]

Who is going to use the stored procedure?
Truncate table cannot be executed by the average user. From Books online:
"TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable."

Also truncate table actions are not logged (this is why it is faster and why only admin users can use it) and any triggers associated withthe table will not fire.

 

[swhitten]

I may be getting out of my league here, but I will try to answer your questions because you raise a very good point. What I've done is write some views to pull together information from several different tables for reporting purposes. I am then writing this stored procedure to write the data into one big table that all the reports are pulling from. This one big table will be replicated down to laptops when they connect to the network. They will then have the table available to them (as of the last replication) for running reports locally when they're not on the network.

I plan to have the stored proc executed by a sql job that is scheduled to run once a night. So the gmsolrpt table on the server will have the data from 1:00 a.m. (not quite real time, but close enough for what they're doing.) Then when the laptop users log on, they will get the table replicated down to them, and they can run the reports locally.

Sql security and user rights is not my strong suit, so I'm really not sure what rights are used to run scheduled jobs or if those rights can be changed.

Does this help clarify? What additional recommendations do you have for me?

Thanks!
Sherry

 

[SQLSister]

OK if it is running from a scheduled job you are probably OK using truncate table because normally the SQL Server agent usess an adminsitrative login of some sort. I just wanted you to understand that if your users were doing this from the user interface, you would probably have problems.