Solved

SQL Create Table Error

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Task 11 44
Convert VBA UDF to SQl SERVER UDF 4 52
Need some alteration to below mention query 2 13
Need age at date of document 5 21
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

696 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