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 |
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
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 |
|
|
|
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_TrialsJorge Merlos |
|
|
|
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?? |
|
|
|
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 useTRUNCATE 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) |
|
|
|
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 |
|
|
|
|
|
|
|
|
Stored Procedure Help