"avnrao" <avn@newsgroups.com> wrote in message
news:e14KpsKwEHA.1148@TK2MSFTNGP12.phx.gbl...
> use this..
>
> INSERT INTO #TEMP_TABLE(city,state)
> SELECT city, state
> FROM stores
> ORDER BY stor_id
>
> Av.
> http://avdotnet.rediffblogs.com
> http://www28.brinkster.com/avdotnet
>
> "Guadala Harry" <GMan@NoSpam.com> wrote in message
> news:ewvNpRKwEHA.4004@tk2msftngp13.phx.gbl...
> > You can duplicate this on your machine in about two seconds: To do so,
> fire
> > up QA and paste the following code and run it. As is, it will choke.
Then
> > comment out the ORDER BY line and run it again and it does not choke.
> >
> > USE PUBS
> > CREATE TABLE #TEMP_TABLE
> > (
> > RowNumber [int] IDENTITY,
> > City varchar(40),
> > State varchar(40)
> > )
> >
> > INSERT INTO #TEMP_TABLE
> > SELECT city, state
> > FROM stores
> > ORDER BY stor_id -- Comment this ORDER BY line out and it works.
> >
> > SELECT * FROM #TEMP_TABLE
> >
> > DROP TABLE #TEMP_TABLE
> >
> > I need to populate a temp table using a SELECT statement that has an
ORDER
> > BY Clause because (1) the data needs to appear in a specific order (as
> > specified by the ORDER BY clause in the populating SELECT), and (2) a
> > subsequent operation needs to find a series of ranges of arbitrary
lengths
> > within the set using an integer column that can be assumed to increment
> > through the range (thus the need for IDENTITY).
> >
> > The problem is that the temp table has an IDENTITY column - but when a
> temp
> > table both (1) has an IDENTITY property and (2) is being populated by a
> > SELECT statement that has ORDER BY, I get the following error:
> > [An explicit value for the identity column in table '#TEMP_TABLE' can
only
> > be specified when a column list is used and IDENTITY_INSERT is ON.]
> >
> > What can I do to get around it?
> >
> > Thanks!
> >
> > -GH
> >
> >
>
>

"Guadala Harry" <GMan@NoSpam.com> wrote in message
news:%23dF6W0KwEHA.1512@TK2MSFTNGP12.phx.gbl...
> Bingo! Thanks!
>
>
>
>
> "avnrao" <avn@newsgroups.com> wrote in message
> news:e14KpsKwEHA.1148@TK2MSFTNGP12.phx.gbl...
> > use this..
> >
> > INSERT INTO #TEMP_TABLE(city,state)
> > SELECT city, state
> > FROM stores
> > ORDER BY stor_id
> >
> > Av.
> > http://avdotnet.rediffblogs.com
> > http://www28.brinkster.com/avdotnet
> >
> > "Guadala Harry" <GMan@NoSpam.com> wrote in message
> > news:ewvNpRKwEHA.4004@tk2msftngp13.phx.gbl...
> > > You can duplicate this on your machine in about two seconds: To do so,
> > fire
> > > up QA and paste the following code and run it. As is, it will choke.
> Then
> > > comment out the ORDER BY line and run it again and it does not choke.
> > >
> > > USE PUBS
> > > CREATE TABLE #TEMP_TABLE
> > > (
> > > RowNumber [int] IDENTITY,
> > > City varchar(40),
> > > State varchar(40)
> > > )
> > >
> > > INSERT INTO #TEMP_TABLE
> > > SELECT city, state
> > > FROM stores
> > > ORDER BY stor_id -- Comment this ORDER BY line out and it works.
> > >
> > > SELECT * FROM #TEMP_TABLE
> > >
> > > DROP TABLE #TEMP_TABLE
> > >
> > > I need to populate a temp table using a SELECT statement that has an
> ORDER
> > > BY Clause because (1) the data needs to appear in a specific order (as
> > > specified by the ORDER BY clause in the populating SELECT), and (2) a
> > > subsequent operation needs to find a series of ranges of arbitrary
> lengths
> > > within the set using an integer column that can be assumed to
increment
> > > through the range (thus the need for IDENTITY).
> > >
> > > The problem is that the temp table has an IDENTITY column - but when a
> > temp
> > > table both (1) has an IDENTITY property and (2) is being populated by
a
> > > SELECT statement that has ORDER BY, I get the following error:
> > > [An explicit value for the identity column in table '#TEMP_TABLE' can
> only
> > > be specified when a column list is used and IDENTITY_INSERT is ON.]
> > >
> > > What can I do to get around it?
> > >
> > > Thanks!
> > >
> > > -GH
> > >
> > >
> >
> >
>
>

"Guadala Harry" <GMan@NoSpam.com> wrote in message
news:ewvNpRKwEHA.4004@tk2msftngp13.phx.gbl...
> You can duplicate this on your machine in about two seconds: To do so,
fire
> up QA and paste the following code and run it. As is, it will choke. Then
> comment out the ORDER BY line and run it again and it does not choke.
>
> USE PUBS
> CREATE TABLE #TEMP_TABLE
> (
> RowNumber [int] IDENTITY,
> City varchar(40),
> State varchar(40)
> )
>
> INSERT INTO #TEMP_TABLE
> SELECT city, state
> FROM stores
> ORDER BY stor_id -- Comment this ORDER BY line out and it works.
>
> SELECT * FROM #TEMP_TABLE
>
> DROP TABLE #TEMP_TABLE
>
> I need to populate a temp table using a SELECT statement that has an ORDER
> BY Clause because (1) the data needs to appear in a specific order (as
> specified by the ORDER BY clause in the populating SELECT), and (2) a
> subsequent operation needs to find a series of ranges of arbitrary lengths
> within the set using an integer column that can be assumed to increment
> through the range (thus the need for IDENTITY).
>
> The problem is that the temp table has an IDENTITY column - but when a
temp
> table both (1) has an IDENTITY property and (2) is being populated by a
> SELECT statement that has ORDER BY, I get the following error:
> [An explicit value for the identity column in table '#TEMP_TABLE' can only
> be specified when a column list is used and IDENTITY_INSERT is ON.]
>
> What can I do to get around it?
>
> Thanks!
>
> -GH
>
>