RichNH
asked on
Why aren't MS-SQL tables dropped?
I have an interesting issue which is perplexing me. I have included the code below. A basic description of the issue is this. I am running a T-SQL MS SQL Server 2008 script. The first time through it works fine, then it doesn't work because apparently the statements that are supposed to DROP two tables at the very beginning didn't do that. I don't know why (although I have my suspicions) and would like to figure out how to get them to work. I'll point out too, that if I execute them by themselves, they work fine.
So...
1) The script checks to see if the tables exist and if they do, it is supposed to drop them (which it doesn't).
2) The script creates table RJZ_SL_Temp from scratch.
3) The script populates the table RJZ_SL_Temp.
4) The script then creates the table RJZ_SL_Deliverable by pulling data from RJZ_SL_Temp
5) The script then ALTERS the table RJZ_SL_Deliverable by DROPping some columns that were used in step 4.
Runs fine when the two created tables don't exist. If I run the commands to check for and DROP the two tables as a standalone run, they work fine. But if I run the entire script again without dropping the two tables manually, I get a number of
"Invalid column name 'ExtractableEmailVendorFee dName'." messages. These all stem from the second to last SQL Statement where I'm counting the vendor feed names.
Essentially, SQL is looking for one of the columns that was dropped. For some reason, the deliverable table isn't being dropped when the script is run twice in a row so the CREATE isn't working and the old modified tables remain. If I drop the tables manually, then it works fine.
The code (parred back to make reading easier):
IF OBJECT_ID ('RJZ_SL_Temp','U') IS NOT NULL
DROP TABLE RJZ_SL_Temp;
IF OBJECT_ID ('RJZ_SL_Deliverable','U') IS NOT NULL
DROP TABLE RJZ_SL_Deliverable;
CREATE TABLE RJZ_SL_Temp
(
KeyID INT NOT NULL
,DedupID INT NOT NULL
,DistinctID VARCHAR(61) NULL
,EmployeeCount INT
,FirstName VARCHAR (100) NULL
,MiddleName VARCHAR (100) NULL
,LastName VARCHAR (100) NULL
,Prefix VARCHAR (50) NULL
,Suffix VARCHAR(50) NULL
,ExecutiveTitle VARCHAR (500) NULL
,OSLevelID int NULL
,ExtractableEmailVendorFee dID INT NULL
,EMail VARCHAR(255) NULL
,ExtractableEmailVendorFee dName VARCHAR(30) NULL
);
INSERT INTO RJZ_SL_Temp
SELECT DISTINCT
c.KeyID
,f.DedupID
,f.DistinctID
,c.Employees
,f.FirstName
,f.MiddleName
,f.LastName
,f.Prefix
,f.Suffix
,f.ExecutiveTitle
,F.OSLevelID
,f.ExtractableEmailVendorF eedID
,f.Email
,F.ExtractableEmailVendorF eedName
FROM PSExtract.dbo.vwCompany AS c
INNER JOIN PSExtract.dbo.vwExecutiveA ndExecutiv eFunction AS f
ON c.KeyID = f.KeyID
WHERE
c.ContentSetID = 100
AND c.MSACode = 38060 --Phoenix, AZ
AND f.OSFunctionID IN (330,333,355) --Information,Network,Tele com
AND f.OSLevelID < 61 /* Restrict to directors or higher */
AND F.ExtractableEmailVendorFe edID IS NOT NULL;
-- Second query
/* Populate RJZ_SL_Deliverable with data */
SELECT
A.KeyID, LTRIM(RTRIM(A.CompanyName) ) AS CompanyName, Address1 AS Address1, A.Address2 AS Address2, A.Address3 AS Address3, City AS City,
A.StateOrProvinceAbbrev AS StateOrProvinceAbbrev, A.PostalCode AS PostalCode, A.County AS County, CountryName AS CountryName,B.FirstName ,B.MiddleName ,B.LastName ,
B.Prefix ,B.Suffix ,B.ExecutiveTitle,B.Email, B.Extracta bleEmailVe ndorFeedNa me,B.Disti nctID
INTO RJZ_SL_Deliverable
FROM PSExtract.dbo.vwCompany AS A
INNER JOIN (SELECT TOP 250 *
FROM RJZ_SL_Temp Employee
WHERE Employee.DistinctID IN
(SELECT TOP 5 DistinctID
FROM RJZ_SL_Temp Top5Emp
WHERE Top5Emp.KeyID = Employee.KeyID
ORDER BY OSLevelID ASC--, DistinctID ASC
)
ORDER BY EmployeeCount Desc
)AS B
ON A.KeyID =B.KeyID AND A.ContentSetID = 100
Order By A.Employees DESC, B.DistinctID;
/* Get email counts by vendor feed name */
SELECT A.ExtractableEmailVendorFe edName, COUNT(A.ExtractableEmailVe ndorFeedNa me) as Vendor_Count
FROM RJZ_SL_Deliverable AS A
group by A.ExtractableEmailVendorFe edName
Order by A.ExtractableEmailVendorFe edName;
/* Drop non-deliverable columns from deliverable */
ALTER TABLE RJZ_SL_Deliverable
DROP COLUMN DistinctID,ExtractableEmai lVendorFee dName;
So...
1) The script checks to see if the tables exist and if they do, it is supposed to drop them (which it doesn't).
2) The script creates table RJZ_SL_Temp from scratch.
3) The script populates the table RJZ_SL_Temp.
4) The script then creates the table RJZ_SL_Deliverable by pulling data from RJZ_SL_Temp
5) The script then ALTERS the table RJZ_SL_Deliverable by DROPping some columns that were used in step 4.
Runs fine when the two created tables don't exist. If I run the commands to check for and DROP the two tables as a standalone run, they work fine. But if I run the entire script again without dropping the two tables manually, I get a number of
"Invalid column name 'ExtractableEmailVendorFee
Essentially, SQL is looking for one of the columns that was dropped. For some reason, the deliverable table isn't being dropped when the script is run twice in a row so the CREATE isn't working and the old modified tables remain. If I drop the tables manually, then it works fine.
The code (parred back to make reading easier):
IF OBJECT_ID ('RJZ_SL_Temp','U') IS NOT NULL
DROP TABLE RJZ_SL_Temp;
IF OBJECT_ID ('RJZ_SL_Deliverable','U')
DROP TABLE RJZ_SL_Deliverable;
CREATE TABLE RJZ_SL_Temp
(
KeyID INT NOT NULL
,DedupID INT NOT NULL
,DistinctID VARCHAR(61) NULL
,EmployeeCount INT
,FirstName VARCHAR (100) NULL
,MiddleName VARCHAR (100) NULL
,LastName VARCHAR (100) NULL
,Prefix VARCHAR (50) NULL
,Suffix VARCHAR(50) NULL
,ExecutiveTitle VARCHAR (500) NULL
,OSLevelID int NULL
,ExtractableEmailVendorFee
,EMail VARCHAR(255) NULL
,ExtractableEmailVendorFee
);
INSERT INTO RJZ_SL_Temp
SELECT DISTINCT
c.KeyID
,f.DedupID
,f.DistinctID
,c.Employees
,f.FirstName
,f.MiddleName
,f.LastName
,f.Prefix
,f.Suffix
,f.ExecutiveTitle
,F.OSLevelID
,f.ExtractableEmailVendorF
,f.Email
,F.ExtractableEmailVendorF
FROM PSExtract.dbo.vwCompany AS c
INNER JOIN PSExtract.dbo.vwExecutiveA
ON c.KeyID = f.KeyID
WHERE
c.ContentSetID = 100
AND c.MSACode = 38060 --Phoenix, AZ
AND f.OSFunctionID IN (330,333,355) --Information,Network,Tele
AND f.OSLevelID < 61 /* Restrict to directors or higher */
AND F.ExtractableEmailVendorFe
-- Second query
/* Populate RJZ_SL_Deliverable with data */
SELECT
A.KeyID, LTRIM(RTRIM(A.CompanyName)
A.StateOrProvinceAbbrev AS StateOrProvinceAbbrev, A.PostalCode AS PostalCode, A.County AS County, CountryName AS CountryName,B.FirstName ,B.MiddleName ,B.LastName ,
B.Prefix ,B.Suffix ,B.ExecutiveTitle,B.Email,
INTO RJZ_SL_Deliverable
FROM PSExtract.dbo.vwCompany AS A
INNER JOIN (SELECT TOP 250 *
FROM RJZ_SL_Temp Employee
WHERE Employee.DistinctID IN
(SELECT TOP 5 DistinctID
FROM RJZ_SL_Temp Top5Emp
WHERE Top5Emp.KeyID = Employee.KeyID
ORDER BY OSLevelID ASC--, DistinctID ASC
)
ORDER BY EmployeeCount Desc
)AS B
ON A.KeyID =B.KeyID AND A.ContentSetID = 100
Order By A.Employees DESC, B.DistinctID;
/* Get email counts by vendor feed name */
SELECT A.ExtractableEmailVendorFe
FROM RJZ_SL_Deliverable AS A
group by A.ExtractableEmailVendorFe
Order by A.ExtractableEmailVendorFe
/* Drop non-deliverable columns from deliverable */
ALTER TABLE RJZ_SL_Deliverable
DROP COLUMN DistinctID,ExtractableEmai
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, I'll be, I didn't even know that the GO command existed. Thanks, it solved the problem. Reading up on it it appears that it is the equivalent of the DoEvents command, kinda. Thanks.
ASKER
Is there a VB "DoEvents" equivalent in T-SQL?