Solved

SQL Create Table Error

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 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