Solved

SQL Create Table Error

Posted on 2013-06-29
3
322 Views
Last Modified: 2013-07-01
I have created a stored procedure to insert the results of three Views into a central table.
My statement is to Drop Table if it Exists and then I create a new table.
When I run the procedure I get the error that the object already exists.  Can someone look at the following code and tell what might be wrong?

One more thing..what can I add to only get distinct VisitID values?

USE [livedb]
GO
/****** Object:  StoredProcedure [PATIENTSMC\gpowers].[Proc_ValenceID3]    Script Date: 06/29/2013 11:22:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            Glen Powers
-- Create date: 6/29/13
-- Description:      Valid Valence VisitID
-- =============================================
ALTER PROCEDURE [PATIENTSMC\gpowers].[Proc_ValenceID3]
      -- Add the parameters for the stored procedure here
      
            
        
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      IF OBJECT_ID('[PATIENTSMC\gpowers].tblVisitID','U')
            is not null
            DROP TABLE [PATIENTSMC\gpowers].tblVisitID
      -- Create Valence VisitID Table---
      SELECT     TOP (100) PERCENT VisitID
            into [PATIENTSMC\gpowers].tblVisitID
      FROM         [PATIENTSMC\gpowers].VW_ValenceAdmPhys


      SELECT     TOP (100) PERCENT VisitID
            into [PATIENTSMC\gpowers].tblVisitID
      FROM         [PATIENTSMC\gpowers].VW_ValenceAttPhys


      SELECT     TOP (100) PERCENT VisitID
            into [PATIENTSMC\gpowers].tblVisitID
      FROM         [PATIENTSMC\gpowers].VW_ValenceSurgPhys

END



Thank you

Glen
0
Comment
Question by:GPSPOW
  • 2
3 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39287371
You can only use the "into" once, the error message is due to the second (and third) use of that approach because the table is created by the first use of "into".
SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.
http://msdn.microsoft.com/en-us/library/ms188029(v=sql.105).aspx

perhaps try this:
      SELECT     VisitID
            into [PATIENTSMC\gpowers].tblVisitID
      FROM         [PATIENTSMC\gpowers].VW_ValenceAdmPhys
      UNION ALL

      SELECT     VisitID
      FROM         [PATIENTSMC\gpowers].VW_ValenceAttPhys
      UNION ALL

      SELECT     VisitID
      FROM         [PATIENTSMC\gpowers].VW_ValenceSurgPhys

Open in new window

regarding TOP 100 PERCENT.. in case you were wondering
the optimizer recognizes that TOP 100 PERCENT qualifies all rows and does not need to be computed at all.  It gets removed from the query plan, and there is no other reason to do an intermediate sorting operation.  As such, the output isn't returned in any particular order.
from MSDN : TOP 100 Percent ORDER BY Considered Harmful.
0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39288804
And if you want only distinct VisitID values from all combined statements, wrap Portlet Paul's code using a SELECT DISTINCT statement. Like this:
SELECT DISTINCT VisitID FROM (
      SELECT     VisitID
            into [PATIENTSMC\gpowers].tblVisitID
      FROM         [PATIENTSMC\gpowers].VW_ValenceAdmPhys
      UNION ALL

      SELECT     VisitID
      FROM         [PATIENTSMC\gpowers].VW_ValenceAttPhys
      UNION ALL

      SELECT     VisitID
      FROM         [PATIENTSMC\gpowers].VW_ValenceSurgPhys
)a

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39288822
Hi,  edtechdba, good point. Are we OK?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now