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
 General SQL Server Forums
 New to SQL Server Programming
 Stored Procedure Help

Author  Topic 

jorgemerlos
Starting Member

3 Posts

Posted - 2015-04-10 : 16:58:06
Hi, I am trying to create a stored procedure that Deletes Table if there is Data.

Also stored procedure will Insert new data into table.

I have already created table. This is part of my current stored procedure.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'srd.[PNMAC\jmerlos].FHLMC_Trials') AND type in (N'U'))
DELETE srd.[PNMAC\jmerlos].FHLMC_Trials

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-10 : 16:59:21
I didn't spot a question in your post.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jorgemerlos
Starting Member

3 Posts

Posted - 2015-04-10 : 17:01:51
Hi, I am trying to create a stored procedure that Deletes Table if there is Data. Also it will Insert new data into table. I have already create table. This is my current stored procedure.

Jorge Merlos
Go to Top of Page

jorgemerlos
Starting Member

3 Posts

Posted - 2015-04-10 : 17:20:09
Hi, I am trying to create a stored procedure that Deletes Table if there is Data.

Also stored procedure will Insert new data into table.

I have already created table. This is part of my current stored procedure.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'srd.[PNMAC\jmerlos].FHLMC_Trials') AND type in (N'U'))
DELETE srd.[PNMAC\jmerlos].FHLMC_Trials

Jorge Merlos
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-12 : 07:20:17
quote:
Originally posted by jorgemerlos

DELETE srd.[PNMAC\jmerlos].FHLMC_Trials



Probably should be:

DROP TABLE srd.[PNMAC\jmerlos].FHLMC_Trials

??
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-12 : 07:27:24
If you just want to "empty" the table then your code is fine, but it will log all the deleted data which may be slow, in which case you could use

TRUNCATE TABLE srd.[PNMAC\jmerlos].FHLMC_Trials

although if there are Foreign Keys on the table that won't work and you'll possibly have trouble with DELETE too in case that)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-12 : 10:50:49
[code]IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PNMAC\jmerlos' AND TABLE_NAME = 'FHLMC_Trials' AND TABLE_TYPE = 'BASE TABLE')
DELETE
FROM [PNMAC\jmerlos].[FHLMC_Trials];[/code]TRUNCATE TABLE will fail if there is a FK constraint.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -