SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Clear table if populated, if not leave it alone
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

latture
Starting Member

24 Posts

Posted - 02/12/2013 :  10:26:39  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/12/2013 :  10:29:54  Show Profile  Reply with Quote

CREATE yourproc
...
AS
..
IF EXISTS ( SELECT 1 FROM Table)
  DELETE FROM Table

...
GO


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

Go to Top of Page

latture
Starting Member

24 Posts

Posted - 02/12/2013 :  10:36:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 02/12/2013 :  10:45:58  Show Profile  Reply with Quote
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 - 02/12/2013 :  11:01:06  Show Profile  Reply with Quote
Ahh. Sweet. Thanks to both of you. This is just what I needed. :)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 02/12/2013 :  11:10:30  Show Profile  Reply with Quote
You are welcome - glad to help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/12/2013 :  11:21:53  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000