Solved

SQL Create Table Error

Posted on 2013-06-29
3
328 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

920 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

15 Experts available now in Live!

Get 1:1 Help Now