?
Solved

SQL Create Table Error

Posted on 2013-06-29
3
Medium Priority
?
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 49

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 49

Accepted Solution

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

801 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