Loading ...
Sorry, an error occurred while loading the content.
 

Re: [SQLQueriesNoCode] Select Into question

Expand Messages
  • Charles Carroll
    ... Did not know of that Insert Into subquery syntax Thanks!!!!! Will try it. I am not a very good T-SQL programmer so need basic help sometimes, although I am
    Message 1 of 7 , Sep 28, 2011
      On Wed, Sep 28, 2011 at 7:44 AM, John Warner <john@...> wrote:

      > **
      >
      >
      > So
      >
      > If Not exists Table
      > select into table from source table
      >
      > else
      > Insert into (Select * From Source table)
      >

      Did not know of that Insert Into subquery syntax

      Thanks!!!!! Will try it.

      I am not a very good T-SQL programmer so need basic help sometimes,
      although I am decent at Database design and Data Warehousing ETL stuff and
      query optimization, and good at the .NET code that interacts with SQL
      Server.


      [Non-text portions of this message have been removed]
    • John Warner
      Double check the syntax as I don t have a reference handy. I know I got the INSERT INTO wrong it would be INSERT INTO table (SELECT fields FROM source) John
      Message 2 of 7 , Sep 28, 2011
        Double check the syntax as I don't have a reference handy. I know I got
        the INSERT INTO wrong it would be
        INSERT INTO table (SELECT fields FROM source)

        John Warner


        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
        > Sent: Wednesday, September 28, 2011 7:48 AM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: Re: [SQLQueriesNoCode] Select Into question
        >
        > On Wed, Sep 28, 2011 at 7:44 AM, John Warner <john@...> wrote:
        >
        > > **
        > >
        > >
        > > So
        > >
        > > If Not exists Table
        > > select into table from source table
        > >
        > > else
        > > Insert into (Select * From Source table)
        > >
        >
        > Did not know of that Insert Into subquery syntax
        >
        > Thanks!!!!! Will try it.
        >
        > I am not a very good T-SQL programmer so need basic help sometimes,
        > although I am decent at Database design and Data Warehousing ETL stuff
        and
        > query optimization, and good at the .NET code that interacts with SQL
        > Server.
        >
        >
        > [Non-text portions of this message have been removed]
        >
        >
        >
        > ------------------------------------
        >
        > Yahoo! Groups Links
        >
        >
        >
      • Charles Carroll
        http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/dc579d3c-9212-4d40-b85f-c52a22c30bd6/ seems to imply (and other articles I looked at the
        Message 3 of 7 , Oct 10, 2011
          http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/dc579d3c-9212-4d40-b85f-c52a22c30bd6/
          seems to imply (and other articles I looked at the same)
          that you can't just test for existence of the table without querying the
          undocumented/'maybe will change' system tables.

          Are they wrong? Is there a one-liner or do I have to resort to their
          tactics?

          On Wed, Sep 28, 2011 at 7:44 AM, John Warner <john@...> wrote:

          > **
          >
          >
          > So
          >
          > If Not exists Table
          > select into table from source table
          >
          > else
          > Insert into (Select * From Source table)
          >
          > ???
          >
          >
          > John Warner
          >
          > > -----Original Message-----
          > > From: SQLQueriesNoCode@yahoogroups.com
          > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
          > > Sent: Wednesday, September 28, 2011 7:39 AM
          > > To: SQLQueriesNoCode@yahoogroups.com
          > > Subject: Re: [SQLQueriesNoCode] Select Into question
          >
          > >
          > > Well the table I am selecting into may get 'blown away' i.e. dropped by
          > > other parts of the system, in which case I need to just SELECT INTO but
          > if
          > > it exists I of course just would append to what is there.
          > >
          > > On Wed, Sep 28, 2011 at 7:31 AM, John Warner <john@...> wrote:
          > >
          > > > **
          >
          > > >
          > > >
          > > > I am confused here (hardly an unusual situation) if the table exists
          > > > create a new one, if the table exists append ?? What is the difference
          > to
          > > > exists and exists that allows you to decide to create a new table or
          > > > append?
          > > >
          > > > John Warner
          > > >
          > > > > -----Original Message-----
          > > > > From: SQLQueriesNoCode@yahoogroups.com
          > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles
          > > Carroll
          > > > > Sent: Tuesday, September 27, 2011 11:03 PM
          > > > > To: SQLQueriesNoCode@yahoogroups.com
          > > > > Subject: [SQLQueriesNoCode] Select Into question
          > > >
          > > > >
          > > > > The query below does a SELECT INTO Agg_AR_LogRawMsgSummary
          > > > >
          > > > > I want it to make a new table if Agg_AR_LogRawMsgSummary exists (it
          > > > > does that now)
          > > > > OR
          > > > > append the data to that table if it already exists (that I don't
          > know
          > > > > how to do) most of the time it does...
          > > > >
          > > > > http://msdn.microsoft.com/en-us/library/ms188029.aspx showed me some
          > > > > tricks but not the trick I need.
          > > > >
          > > > >
          > > > >
          > > > >
          > > >
          > **************************************************************************
          > > > ****************
          > > > > *****************
          > > > > Below is the Sproc
          > > > >
          > > >
          > **************************************************************************
          > > > ****************
          > > > > *****************
          > > > >
          > > > >
          > > > > USE [AutoStore_LogExt]
          > > > > GO
          > > > > SET ANSI_NULLS ON
          > > > > GO
          > > > > SET QUOTED_IDENTIFIER ON
          > > > > GO
          > > > > -- =============================================
          > > > > -- Author: Charles Carroll
          > > > > -- Create date: 3/2011
          > > > > -- Description:
          > > > > -- =============================================
          > > > > ALTER PROCEDURE [dbo].[AG_CRUD_SummarizeLogRawMessages]
          > > > > AS
          > > > > BEGIN
          > > > > SET NOCOUNT ON;
          > > > >
          > > > >
          > > > > DECLARE @MaxID AS bigint;
          > > > >
          > > > > /* set scope of next ops */
          > > > > select @MaxID=MAX(LogRaw_ID)
          > > > > FROM Raw_Log;
          > > > >
          > > > > /* make transient table in case someone needs each message */
          > > > > Select *
          > > > > into AR_Transient_Raw_Msgs
          > > > > from raw_Log
          > > > > where LogValue is not null AND [LogRaw_ID]<=@MaxID
          > > > >
          > > > > /* create agreggate table */
          > > > > select
          > > > > LogValue
          > > > > , count(*) as frequency,
          > > > > MIN(OccurredWhen) AS First,
          > > > > MAX(OccurredWhen) AS Last,
          > > > > MAX(LogRaw_ID) AS LogRaw_ID_Last
          > > > >
          > > > > Into Agg_AR_LogRawMsgSummary
          > > > >
          > > > > from AutoStore_LogExt.dbo.Raw_Log
          > > > >
          > > > > where LogValue is not null AND [LogRaw_ID]<=@MaxID
          > > > >
          > > > > group by LogValue
          > > > >
          > > > > having COUNT(*) >1
          > > > >
          > > > > order by frequency desc
          > > > >
          > > > > /* Kill the original rows */
          > > > > delete from Raw_Log
          > > > > where LogValue is not null AND LogRaw_ID<=@MaxID
          > > > >
          > > > > END
          > > > >
          > > > >
          > > > > ------------------------------------
          > > > >
          > > > > Yahoo! Groups Links
          > > > >
          > > > >
          > > > >
          > > >
          > > >
          > > >
          > >
          > >
          > > [Non-text portions of this message have been removed]
          > >
          > >
          > >
          > > ------------------------------------
          > >
          > > Yahoo! Groups Links
          > >
          > >
          > >
          >
          >
          >


          [Non-text portions of this message have been removed]
        Your message has been successfully submitted and would be delivered to recipients shortly.