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.
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 NULLTRUNCATE TheTableName; |
|
|
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. :) |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-12 : 11:10:30
|
You are welcome - glad to help. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|