March 13, 2014 at 2:24 pm
Is this close to the correct syntax for a stored procedure for deleting all the data from a particular table... or is there a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE TruncateTmpBank
AS
BEGIN
USE [DTCBrec]
GO
DROP TABLE [HBIRES\DaCampb1].[tmpBank]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [HBIRES\DaCampb1].[tmpBank](
[Store] [int] NULL,
[TransactionDate] [datetime] NULL,
[Amount] [float] NULL,
[ConcatenateBank] [nvarchar](255) NULL
) ON [PRIMARY]
GO
END
GO
March 13, 2014 at 2:30 pm
briancampbellmcad (3/13/2014)
Is this close to the correct syntax for a stored procedure for deleting all the data from a particular table... or is there a better way?SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE TruncateTmpBank
AS
BEGIN
USE [DTCBrec]
GO
DROP TABLE [HBIRES\DaCampb1].[tmpBank]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [HBIRES\DaCampb1].[tmpBank](
[Store] [int] NULL,
[TransactionDate] [datetime] NULL,
[Amount] [float] NULL,
[ConcatenateBank] [nvarchar](255) NULL
) ON [PRIMARY]
GO
END
GO
Why not just truncate the table. That will be better than dropping and recreating. Keep in mind that when you drop and recreate you lose all permissions.
Just have the entire contents of the proc be this.
TRUNCATE TABLE [HBIRES\DaCampb1].[tmpBank]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2014 at 2:37 pm
Thanks for simplifying and also pointing out the permissions risks... BTW can a SP in SQL Server be called from VBA code in MS-Access/ Like "EXEC TruncateTmpBank"?
March 13, 2014 at 2:43 pm
briancampbellmcad (3/13/2014)
Thanks for simplifying and also pointing out the permissions risks... BTW can a SP in SQL Server be called from VBA code in MS-Access/ Like "EXEC TruncateTmpBank"?
Glad that helps.
Yes you can execute stored procs from VBA code. It is just like any other programming language in that regard. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply