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 2008 Forums
 Transact-SQL (2008)
 Clear table if populated, if not leave it alone

Author  Topic 

latture
Starting Member

24 Posts

Posted - 2013-02-12 : 10:26:39
Hello,

I'm trying to find a way in an already existing procedure to truncate or delete a table if it is already populated and if not don't do anything but proceed with the procedure. Can someone direct me to how or show me how to get this accomplished?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-12 : 10:29:54
[code]
CREATE yourproc
...
AS
..
IF EXISTS ( SELECT 1 FROM Table)
DELETE FROM Table

...
GO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

latture
Starting Member

24 Posts

Posted - 2013-02-12 : 10:36:26
So bascially, if table exist go to first record from table if not delete table? The procedure already exist so I expect I won't have to worry about the CREATE yourproc. Would select 1 from table allow the procedure to move on or does it actually go to the record and not move on? "DELETE FROM Table" will remove all records in table? Sorry, I'm new this and would like an explanation so I can fully understand. :P
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-12 : 10:45:58
If the table is not empty, it will delete all the rows from the table. If it is empty, it will not execute the DELETE FROM Table statement. In either case, it will continue on to the statements following that.

DELETE FROM Table will delete all records from the table. Alternatively, you can use TRUNCATE Table (which is similar, but different - Truncate table is faster, but you cannot truncate if there are any foreign key constraints even if no referenced data exists etc.)

What Visakh posted assumes that the table exists, and that you want to work with that table either truncating it or deleting the data from that table.

If the table does not exist and you want to check for its existence, do it like this:
IF OBJECT_ID('TheTableName') IS NOT NULL
TRUNCATE TheTableName;
Go to Top of Page

latture
Starting Member

24 Posts

Posted - 2013-02-12 : 11:01:06
Ahh. Sweet. Thanks to both of you. This is just what I needed. :)
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-12 : 11:10:30
You are welcome - glad to help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-12 : 11:21:53
quote:
Originally posted by latture

Ahh. Sweet. Thanks to both of you. This is just what I needed. :)



welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -