Ms Sql

LinuxGold · 22-Aug-2005, 10:56 AM #1

In MS SQL, how do I drop table if it exists without having it telling me error?

I tried this way:
SET NOCOUNT ON
IF EXISTS (SELECT * FROM #TMP)
BEGIN

DROP TABLE #TMP;

END
GO
SET NOCOUNT OFF
It halts when #TMP do not exist.

coderitr · 22-Aug-2005, 12:39 PM #2

If it exists it will be referenced by a row in sysobjects. I'm not that advanced in SQL but if you can query "select count(*) from sysobjects where name = [value]" and if that returns > 0 then the table exists.

LinuxGold · 22-Aug-2005, 12:59 PM #3

Quote:

Originally Posted by coderitr

If it exists it will be referenced by a row in sysobjects. I'm not that advanced in SQL but if you can query "select count(*) from sysobjects where name = [value]" and if that returns > 0 then the table exists.

Code:

IF (SELECT COUNT(*) FROM #TMP)>0 
BEGIN
	DROP TABLE #TMP;
END
GO
IF (SELECT COUNT(*) FROM #FTMP)>0
BEGIN
	DROP TABLE #FTMP
END

Didn't work.

coderitr · 22-Aug-2005, 01:44 PM #4

Code:

use Test_database

If (select Count(*) From Sysobjects Where Name = 'test_table') > 0 
Begin
  Drop Table Test_table
End
Go

LinuxGold · 23-Aug-2005, 07:30 AM #5

Thanks for your input! It works great if I use [Trainee] but not #Trainee. The problem is I work with a lot of temporary tables as "#foo". Is there a way I can do it using #Trainee instead? I will study Sysobjects as it is my first time to see it in action.

Code:

If (
	SELECT 
		COUNT(*) 
	FROM 
		Sysobjects 
	WHERE 
		Name = '#Trainee'
) > 0 
BEGIN
	DROP TABLE 
		#Trainee
END
Go
SELECT * 
INTO #Trainee
From Associates 
WHERE Active='1' 
AND Trainee='1';

Thanks again and really appreciate your input.

Quote:

Originally Posted by coderitr

Code:

use Test_database

If (select Count(*) From Sysobjects Where Name = 'test_table') > 0 
Begin
  Drop Table Test_table
End
Go

fabulous · 26-Aug-2005, 06:01 AM #6

i don't know about mysql but sql is universal.
why don't you
drop table [table name]
why do you wanna complecate your life?

LinuxGold · 26-Aug-2005, 08:02 AM #7

Quote:

Originally Posted by fabulous

i don't know about mysql but sql is universal.
why don't you
drop table [table name]
why do you wanna complecate your life?

This isn't MySQL question. And not all SQL work the same. I'm trying to create STORED PROCEDURE that create many temporary tables. Sometimes when I test my stored procedure, it left tables hanging inside the server when my sproc errors out. It is tiring to add drop table #foo and remove that line every time when I test my sproc. It might even error out in production and render it useless. The reason why I prefer #foo over foo table is because I do not want to make tables that other users might use. Here is the example code:

Code:

ALTER PROCEDURE PMQC_Wkly_Rpt   
 @week_id INT  
AS  
DECLARE   
--@week_id INT,
 @qty_one INT,  
 @qty_two INT,  
 @qty_three INT,  
 @qty_four INT,  
 @qty_five INT,  
 @qty_six INT,  
 @moid INT,  
  --WEEK ID USED TO SELECT DATE RANGE FROM WK_RPT TABLE  
-- @week_id INT,  
  --START DATE  
 @mindate SMALLDATETIME,  
  --END DATE  
 @maxdate SMALLDATETIME  
  --TOTAL QTY SAMPLE LIMIT  
-- @limit INT  
--SET @week_id='30'
SET  
 @mindate=(SELECT startdate FROM wk_rpt WHERE WeekID=@week_id)  
SET  
 @maxdate=(SELECT enddate FROM wk_rpt WHERE WeekID=@week_ID)  
SET   
 @moid=(SELECT MonthID FROM mo_rpt WHERE startdate<=@mindate AND enddate>=@maxdate)  
   --FIXED FORECASTS  
-- @qty_gear INT,  
-- @qty_mar INT,  
-- @qty_dcd INT,  
-- @qty_bas INT,  
-- @qty_par INT,  
-- @qty_vs INT  
SET @qty_one=(SELECT VALUE FROM PMQC_Forecast WHERE MID=@moid AND BrandID='35')  
SET @qty_two=(SELECT CAST(VALUE AS INT) FROM PMQC_Forecast WHERE MID=@moid AND BrandID='4')  
SET @qty_three=(SELECT CAST(VALUE AS INT) FROM PMQC_Forecast WHERE MID=@moid AND BrandID='26')  
SET @qty_four=(SELECT CAST(VALUE AS INT) FROM PMQC_Forecast WHERE MID=@moid AND BrandID='1') 
SET @qty_five=(SELECT CAST(VALUE AS INT) FROM PMQC_Forecast WHERE MID=@moid AND BrandID='8')  
SET @qty_six=(SELECT CAST(VALUE AS INT) FROM PMQC_Forecast WHERE MID=@moid AND BrandID='7')  
--SET @week_id='30'  
SET ROWCOUNT 0  
  --Ensures that all tables are dropped before proceeding with procedure  
SET NOCOUNT ON  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[PMQC]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[PMQC]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[FRWEB]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[FRWEB]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[EPMTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[EPMTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[PMTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[PMTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[ONWE]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[ONWE]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[OBRBTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[OBRBTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[BRBTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[BRBTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  ID = object_id(N'[QcData].[BTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[BTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  ID = object_id(N'[QcData].[RWEB]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[RWEB]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  ID = object_id(N'[QcData].[WEB]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[WEB]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  ID = object_id(N'[QcData].[TMPP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[TMPP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[TMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[TMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[STMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[STMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[ETMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[ETMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[FTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[FTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[GEAR]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[GEAR]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[MAR]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[MAR]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[DCD]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[DCD]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[BAS]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[BAS]  
  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[PAR]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[PAR]  
  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[VIR]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[VIR]  
SET ROWCOUNT 0  
SELECT    
 NewID() AS TMP_RID,  
 Q.ID AS QID,  
 Q.Batch AS Q_B,  
 B.[order] AS B_O,  
 B.comments AS B_C,  
 ET.Description AS ETD,  
 ET.ID AS ETI,  
 Br.Brand AS BRB,  
 Br.ID AS BRI,  
 Q.ReceiveDate AS QRD,  
 Q.KeyDate AS K_D  
INTO  
 [QcData].[TMPP]  
FROM    
 QA Q  
LEFT JOIN   
 Batch B   
 ON B.QAID=Q.ID    
LEFT JOIN   
 QAErrors QE   
 ON QE.ID=B.QEID    
LEFT JOIN   
 ErrorTypes ET   
 ON ET.ID=QE.ErrorTypeID   
LEFT JOIN   
 Brands Br   
 ON Br.ID=Q.BrandID  
LEFT JOIN  
 Associates A  
 ON  
 A.ID=Q.OperatorID  
WHERE   
 KeyDate   
  BETWEEN   
   @mindate  
  AND   
   @maxdate  
AND  
 A.Trainee='0'  
ORDER BY   
 NewID();  
SELECT   
 IDENTITY(int,1,1) AS ID, --INT  
 QID,    --INT  
 Q_B,    --NVARCHAR(9)  
 B_O,    --NVARCHAR(9)  
 B_C,    --NVARCHAR(255)  
 ETD,    --NVARCHAR(255)  
 ETI,    --INT  
 BRB,    --NVARCHAR(50)  
 BRI,    --NVARCHAR(50)  
 QRD,    --smalldatetime  
 K_D    --smalldatetime  
INTO  
 [QcData].[TMP]   
FROM  
 [QcData].[TMPP];  
SET ROWCOUNT 0  
DECLARE @tot REAL  
CREATE TABLE [QcData].[FTMP] (  
 ID INT not null,  
 QID INT not null,  
 Q_B NVARCHAR(9) not null,  
 B_O NVARCHAR(9) not null,  
 B_C nvarchar(255),  
 ETD nvarchar(255),  
 ETI INT,  
 BRB nvarchar(50),  
 BRI nvarchar(50),  
 QRD smalldatetime,  
 K_D smalldatetime);  
SELECT DISTINCT B_O INTO [QcData].[STMP] FROM [QcData].[TMP] WHERE ETI is not null AND ETI<>'19' AND B_C not like 'PER %';  
SELECT IDENTITY(INT,1,1) AS EID,B_O INTO [QcData].[ETMP] FROM [QcData].[STMP];  
DECLARE @EMAX int  
SET @EMAX=(SELECT MAX(EID) FROM [QcData].[ETMP])  
DECLARE @ECtr INT  
SELECT @ECtr='0'  
WHILE (@ECtr<@EMAX)  
BEGIN  
 SELECT @ECtr=@ECtr+'1'  
 INSERT INTO   
  [QcData].[FTMP]   
 SELECT TOP 1   
  *   
 FROM   
  [QcData].[TMP]   
 WHERE   
  [QcData].[TMP].ETI IS NOT NULL   
 AND   
  [QcData].[TMP].B_O=(  
    SELECT   
    B_O   
   FROM   
    [QcData].[ETMP]   
   WHERE   
    EID=@Ectr  
  );  
END  
 
  
SELECT  
 ETD,  
 BRB,  
 count(*) AS TOT  
INTO  
 RWEB  
FROM  
 FTMP  
GROUP BY  
 ETD,  
 BRB;  
SELECT   
 P.*,  
 P.Bas+P.Corp+P.Merit+P.Par+P.Vir+P.Mar+P.DCD+P.[G-5] AS Total  
INTO  
 [QcData].[WEB]  
FROM  
 (  
 SELECT  
  CASE WHEN (Grouping(ETD)=1) THEN 'Grand Total' ELSE ETD END AS ETD,  
  SUM(CASE BRB WHEN 'BrandOne' THEN TOT ELSE 0 END) AS One,  
  SUM(CASE BRB WHEN 'BrandTwo' THEN TOT ELSE 0 END) AS Two,  
  SUM(CASE BRB WHEN 'BrandThree' THEN TOT ELSE 0 END) AS Three,  
  SUM(CASE BRB WHEN 'BrandFour' THEN TOT ELSE 0 END) AS Four,  
  SUM(CASE BRB WHEN 'BrandFive' THEN TOT ELSE 0 END) AS Five,  
  SUM(CASE BRB WHEN 'BrandSix' THEN TOT ELSE 0 END) AS Six,  
  SUM(CASE BRB WHEN 'BrandSeven' THEN TOT ELSE 0 END) AS Seven,  
  SUM(CASE BRB WHEN 'BrandEight' THEN TOT ELSE 0 END) AS Eight
 FROM  
  RWEB  
 GROUP BY  
  ETD  
 WITH ROLLUP  
) AS P;  

SELECT   
 DISTINCT B_O,  
 TMP_RID,  
 BRB,  
 K_D,  
 ETD  
INTO   
 [QcData].[BTMP]   
FROM   
 TMPP  
ORDER BY  
 TMP_RID;  
  
--SELECT * FROM TMPP;  
CREATE TABLE   
 [QcData].[BRBTMP] (  
  B_O nvarchar(9) not null,  
  BRB nvarchar(50) not null,  
  K_D smalldatetime not null,  
  ETD nvarchar(50)  
 );  
  
SET rowcount @qty_gear
INSERT INTO [QcData].[BRBTMP] SELECT B_O,BRB,K_D,ETD FROM [QcData].[BTMP] WHERE BRB='One';  
SET rowcount 0  
SET rowcount @qty_mar  
INSERT INTO [QcData].[BRBTMP] SELECT B_O,BRB,K_D,ETD FROM [QcData].[BTMP] WHERE BRB='Two';  
SET rowcount 0  
SET rowcount @qty_dcd  
INSERT INTO [QcData].[BRBTMP] SELECT B_O,BRB,K_D,ETD FROM [QcData].[BTMP] WHERE BRB='Three';  
SET rowcount 0  
SET rowcount @qty_bas  
INSERT INTO [QcData].[BRBTMP] SELECT B_O,BRB,K_D,ETD FROM [QcData].[BTMP] WHERE BRB='Four';  
SET rowcount 0  
SET rowcount @qty_par  
INSERT INTO [QcData].[BRBTMP] SELECT B_O,BRB,K_D,ETD FROM [QcData].[BTMP] WHERE BRB='Five';  
SET rowcount 0  
SET rowcount @qty_vs  
INSERT INTO [QcData].[BRBTMP] SELECT B_O,BRB,K_D,ETD FROM [QcData].[BTMP] WHERE BRB='Six';  
SET rowcount 0  
  
  --Sorts by day by day   
SELECT  
 BRB,  
 K_D,  
 count(*) AS TOT  
INTO  
 [QcData].[OBRBTMP]  
FROM  
 BRBTMP  
GROUP BY  
 K_D,  
 BRB;  
SELECT  
 B.*,  
 B.Sunday + B.Monday + B.Tuesday + B.Wednesday + B.Thursday + B.Friday + B.Saturday AS 'Brand Total'  
INTO  
 [QcData].[ONWE]  
FROM (  
 SELECT  
  CASE WHEN (Grouping(BRB)=1) THEN 'Grand Total' ELSE BRB END AS Brand,  
  SUM(CASE DatePart(dw,K_D) WHEN 1 THEN TOT ELSE '' END) AS Sunday,  
  SUM(CASE DatePart(dw,K_D) WHEN 2 THEN TOT ELSE '' END) AS Monday,  
  SUM(CASE DatePart(dw,K_D) WHEN 3 THEN TOT ELSE '' END) AS Tuesday,  
  SUM(CASE DatePart(dw,K_D) WHEN 4 THEN TOT ELSE '' END) AS Wednesday,  
  SUM(CASE DatePart(dw,K_D) WHEN 5 THEN TOT ELSE '' END) AS Thursday,  
  SUM(CASE DatePart(dw,K_D) WHEN 6 THEN TOT ELSE '' END) AS Friday,  
  SUM(CASE DatePart(dw,K_D) WHEN 7 THEN TOT ELSE '' END) AS Saturday  
 FROM   
  OBRBTMP   
 GROUP BY  
  BRB  
 WITH ROLLUP  
) AS B;  
  
SELECT  
 E.BRB,  
 COUNT(E.B_O) AS TOT  
INTO  
 PMTMP  
FROM  
 (  
 SELECT   
  B_O,  
  BRB   
 FROM   
  BRBTMP  
) AS E  
GROUP BY E.BRB;  
SELECT BRB, sum(TOT) AS TOT  
INTO FRWEB  
FROM RWEB   
GROUP BY BRB;  
SELECT   
 A.BRB AS Brands,  
 A.TOT AS 'Total Orders',  
 CASE WHEN B.TOT IS NOT NULL THEN B.TOT ELSE '' END AS 'Failing Orders',  
 CASE WHEN A.TOT-B.TOT IS NOT NULL THEN A.TOT-B.TOT ELSE A.TOT END AS 'Passing Orders',  
 CASE WHEN CAST(((CAST(A.TOT AS REAL)-CAST(B.TOT AS REAL))/CAST(A.TOT AS REAL))*100 AS Decimal(6,1)) IS NOT NULL   
  THEN CAST(((CAST(A.TOT AS REAL)-CAST(B.TOT AS REAL))/CAST(A.TOT AS REAL))*100 AS Decimal(6,1))  
  ELSE '100.0'  
  END AS 'QC Average'  
INTO  
 PMQC  
FROM PMTMP A  
LEFT JOIN FRWEB B ON B.BRB=A.BRB;  
SET NOCOUNT OFF  
  --Weekly Error By Brand (WEB)  
SELECT * FROM WEB;  
  --Orders needed for week ending (ONWE)  
SELECT * FROM ONWE;  
  --PM Quality Control Weekly Report (PMQC)  
SELECT * FROM PMQC;  
SET NOCOUNT ON;
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[PMQC]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[PMQC]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[FRWEB]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[FRWEB]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[EPMTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[EPMTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[PMTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[PMTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[ONWE]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[ONWE]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[OBRBTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[OBRBTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[BRBTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[BRBTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  ID = object_id(N'[QcData].[BTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[BTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  ID = object_id(N'[QcData].[RWEB]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[RWEB]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  ID = object_id(N'[QcData].[WEB]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[WEB]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  ID = object_id(N'[QcData].[TMPP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE [QcData].[TMPP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[TMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[TMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[STMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[STMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[ETMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[ETMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[FTMP]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[FTMP]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[GEAR]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[GEAR]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[MAR]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[MAR]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[DCD]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[DCD]  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[BAS]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[BAS]  
  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[PAR]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[PAR]  
  
IF EXISTS (  
 SELECT   
  *   
 FROM   
  dbo.sysobjects   
 WHERE   
  id = object_id(N'[QcData].[VIR]')   
 AND   
  OBJECTPROPERTY(id, N'IsUserTable') = 1  
)  
DROP TABLE   
 [QcData].[VIR]

fabulous · 04-Sep-2005, 03:13 PM #8

i haven't gone through the hole thing but they are a hole lot of mistakes
onless sql server is far different from mysql
why is @week_id an integer instade of date
well check through correct it and post it again

LinuxGold · 06-Sep-2005, 07:26 AM #9

Quote:

Originally Posted by fabulous

i haven't gone through the hole thing but they are a hole lot of mistakes
onless sql server is far different from mysql
why is @week_id an integer instade of date
well check through correct it and post it again

@week_id is not date, it is the week number of year from 1 to 52.

It is working perfectly as intended.

I just want to use temporary table ##foo instead of creating actual table foo.

drop table if exists works great in MySQL, and I'm using MS SQL which doesn't work the same.

I've been reducing the use of temporary tables and now only have 2 temporary tables so far.

BTW "hole" is "whole".

fabulous · 09-Sep-2005, 07:47 AM **#10**

i don't understand what you want to do
there are three temporary tables in sql
the inserted, deleted, and updated
which are created when u insert, delete or update
please explain in detail what you want to do

LinuxGold · 09-Sep-2005, 07:57 AM **#11**

SELECT * INTO ##FOO FROM FEE

SELECT SOMETHING FROM ##FOO

SELECT SOMETHINGELSE FROM ##FOO

DROP TABLE ##FOO

IF something occured to "SELECT SOMETHING ELSE FROM ##FOO" it leaves ##FOO hanging.

IF I add:
IF Exists (SELECT Top 1 * FROM ##FOO)
BEGIN
DROP TABLE ##FOO
END

It whines out "##FOO" Invalid Object Name ##FOO

How do I get around to it that it drop table only if it exists and if it's invalid, skip to the next step.

Similiar to MySQL:

DROP TABLE IF EXISTS ##FOO

regardless if ##FOO is invalid or not.

fabulous · 18-Sep-2005, 03:12 PM **#12**

y don't you do
if exists(select * from foo)
begin
...........
end
y do you wanna add top 1?

Search
Search in:
Show Threads Show Posts
Advanced Search

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)