Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure drop table Expand / Collapse
Author
Message
Posted Wednesday, January 08, 2014 11:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:21 PM
Points: 5, Visits: 10
I am attempting to create a stored procedure that checks for a table and drops the table if it exist then creates the table and then gets data from a view and inserts it into a table on intervals of time, I will say each day.

What I have may be way off but this is where I am at... don't laugh

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_PickHistoryReportTbl]

AS
BEGIN
IF OBJECT_ID('Tbl_PickHist','U') IS NOT NULL
DROP TABLE Tbl_PickHist
GO
CREATE TABLE dbo.Tbl_PickHist
(LastPick datetime,
ComponentItemNumber varchar(50),
IssuedQuantity float)
GO
INSERT INTO dbo.Tbl_PickHist (LastPick,ComponentItemNumber,IssuedQuantity)
select * from S_PickHistory2

So currently when I try to alter the procedure it tells me the table already exist and it says my syntax is wrong in a couple places... I am sure this is elementary for some...
Post #1529020
Posted Wednesday, January 08, 2014 11:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 12,741, Visits: 31,050
i don't userstand the logic of dropping it and adding it...why not delete, reseed any identities, and insert into again?

your error is due to the fact that
a stored proc cannot have the "GO" command in it;


the "GO" command is actually for SSMS,a nd is not a valid TSQL comand.

removing the GO removes the error:

ALTER PROCEDURE [dbo].[usp_PickHistoryReportTbl]
AS
BEGIN
DECLARE @cmd VARCHAR(MAX)
IF OBJECT_ID('Tbl_PickHist','U') IS NOT NULL
BEGIN
DROP TABLE Tbl_PickHist
CREATE TABLE dbo.Tbl_PickHist
(LastPick datetime,
ComponentItemNumber varchar(50),
IssuedQuantity float)
INSERT INTO dbo.Tbl_PickHist (LastPick,ComponentItemNumber,IssuedQuantity)
SELECT * FROM S_PickHistory2
END --IF
END --PROC



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1529027
Posted Wednesday, January 08, 2014 12:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:21 PM
Points: 5, Visits: 10
I thought that was a normal method... I don't know another answer..
Is that better as far as best practice?
This tells me incorrect syntax near MAX
I have SQL 2000 ... should I use VARCHAR(8000)?
Post #1529034
Posted Wednesday, January 08, 2014 12:21 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:27 PM
Points: 2,763, Visits: 5,899
You actually don't need the following line
DECLARE @cmd VARCHAR(MAX)

As said, you would be better deleting the information from the table. You could use TRUNCATE TABLE for performance, but it has its limitations.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1529042
Posted Wednesday, January 08, 2014 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:21 PM
Points: 5, Visits: 10
Thank you!!!
I will look at changing it....
How can I cause it to perform every day? Is that just via a task in windows?
Post #1529052
Posted Wednesday, January 08, 2014 12:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:27 PM
Points: 2,763, Visits: 5,899
You could set up a job in the SQL Server Agent.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1529054
Posted Wednesday, January 08, 2014 1:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:51 AM
Points: 5,986, Visits: 6,930
Instead of dropping the table, simply TRUNCATE it. That'll reset all your seeds and the like and you won't have to redefine the schema constantly, which fries dependencies and a few other concerns.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1529067
Posted Wednesday, January 08, 2014 1:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:21 PM
Points: 5, Visits: 10


ALTER PROCEDURE [dbo].[usp_PickHistoryReportTbl]
AS
BEGIN
TRUNCATE TABLE Tbl_PickHist;
INSERT INTO dbo.Tbl_PickHist (LastPick,ComponentItemNumber,IssuedQuantity)
SELECT * FROM S_PickHistory2
END

This works
Post #1529081
Posted Wednesday, January 08, 2014 2:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:21 PM
Points: 5, Visits: 10
Thanks guys for all your help
Post #1529114
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse