Professional Web Applications Themes

Combing 2 SQL Statements into 1 - Microsoft SQL / MS SQL Server

Hello All In the Code Below I have 2 SQL Statements is there a way I can combine the both statements into 1 so I only have to execute it once Thanks Stuart IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'VIEW_MyTestItems') DROP VIEW VIEW_MyTestItems CREATE VIEW dbo.VIEW_MyTestItems AS SELECT ItemID,Data, RecordDate FROM TestTable_1 WHERE ItemID=37 UNION SELECT ItemID, Data, RecordDate FROM TestTable_2 WHERE ItemID=72 UNION SELECT ItemID, Data, RecordDate FROM TestTable_3 WHERE ItemID=71...

  1. #1

    Default Combing 2 SQL Statements into 1

    Hello All

    In the Code Below I have 2 SQL Statements is there a way I can combine the
    both statements
    into 1 so I only have to execute it once

    Thanks
    Stuart


    IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_NAME = 'VIEW_MyTestItems')
    DROP VIEW VIEW_MyTestItems

    CREATE VIEW dbo.VIEW_MyTestItems AS
    SELECT ItemID,Data, RecordDate FROM TestTable_1 WHERE ItemID=37
    UNION
    SELECT ItemID, Data, RecordDate FROM TestTable_2 WHERE ItemID=72
    UNION
    SELECT ItemID, Data, RecordDate FROM TestTable_3 WHERE ItemID=71




    Stuart Guest

  2. #2

    Default Re: Combing 2 SQL Statements into 1


    Well mostly no, but a little yes.
    No could use ALTER VIEW
    but realize that assumes the said view already exists.
    If you are not fine with that assumption, then nothing has been gained.

    I wonder:
    Did you really mean to use UNION instead of UNION ALL?

    Bye,
    Delbert Glass

    "Stuart Shay" <com> wrote in message
    news:#phx.gbl... 


    Delbert Guest

  3. #3

    Default Re: Combing 2 SQL Statements into 1

    Stuart,

    Here is a single statement that does what you ask
    (tested with Northwind)

    IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_NAME = 'VVV')

    BEGIN
    DROP VIEW VVV
    EXEC('
    CREATE VIEW VVV AS
    SELECT OrderID FROM Northwind..Orders WHERE CustomerID = ''WOLZA''
    ')
    -- or just exec('alter view ...
    END ELSE
    EXEC('
    CREATE VIEW VVV AS
    SELECT OrderID FROM Northwind..Orders WHERE CustomerID = ''WOLZA''
    ')

    -- Steve Kass
    -- Drew University
    -- Ref: 0C9097D9-82D7-4576-BC9D-46E16660B807


    Stuart Shay wrote:
     

    Steve Guest

Similar Threads

  1. Combing two simple select statements. There must be a way?
    By ukr_bend@yahoo.com in forum MySQL
    Replies: 5
    Last Post: March 24th, 06:10 PM
  2. Problem combing multiple lists into one
    By mourning2night in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 17th, 09:30 PM
  3. Combing PDF/Moving Doc Names to Bookmarks
    By wynn_williamson@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 3
    Last Post: May 13th, 10:34 AM
  4. #17997 [Ver]: got wanning when combing switch & reference
    By helly@php.net in forum PHP Development
    Replies: 0
    Last Post: August 10th, 11:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139