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

RE: [SQLQueriesNoCode] Select Into question

Expand Messages
  • 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 1 of 7 , Sep 28, 2011
    • 0 Attachment
      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 2 of 7 , Oct 10, 2011
      • 0 Attachment
        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.