What is the syntax to drop a temporary table if exists ? I want to drop a
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:
> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
> > What is the syntax to drop a temporary table if exists ? I want to drop a
> > temp table ##temptable if exist before I try to create it.
> >
> > Thanks.
>
Showing posts with label temp. Show all posts
Showing posts with label temp. Show all posts
Wednesday, March 28, 2012
If exists for temp table
What is the syntax to drop a temporary table if exists ? I want to drop a
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:
> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
>
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:
> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
>
Wednesday, March 21, 2012
Identity vs. Identity(1,1)
Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identity,
is (1,1) the assumed default?
Message posted via http://www.droptable.com
Hi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via droptable.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>
|||Hi
When specifying the identity property the seed and increment values are
optional, with a default of 1 for each. Therefore your two tables will be
equivalent. See http://msdn2.microsoft.com/en-us/library/ms186775.aspx for
more
John
"cbrichards via droptable.com" wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identity,
is (1,1) the assumed default?
Message posted via http://www.droptable.com
Hi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via droptable.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>
|||Hi
When specifying the identity property the seed and increment values are
optional, with a default of 1 for each. Therefore your two tables will be
equivalent. See http://msdn2.microsoft.com/en-us/library/ms186775.aspx for
more
John
"cbrichards via droptable.com" wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>
Identity vs. Identity(1,1)
Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identit
y,
is (1,1) the assumed default?
Message posted via http://www.droptable.comHi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via droptable.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as ident
ity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>|||Hi
When specifying the identity property the seed and increment values are
optional, with a default of 1 for each. Therefore your two tables will be
equivalent. See http://msdn2.microsoft.com/en-us/library/ms186775.aspx for
more
John
"cbrichards via droptable.com" wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as ident
ity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>sql
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identit
y,
is (1,1) the assumed default?
Message posted via http://www.droptable.comHi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via droptable.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as ident
ity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>|||Hi
When specifying the identity property the seed and increment values are
optional, with a default of 1 for each. Therefore your two tables will be
equivalent. See http://msdn2.microsoft.com/en-us/library/ms186775.aspx for
more
John
"cbrichards via droptable.com" wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as ident
ity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>sql
Identity vs. Identity(1,1)
Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identity,
is (1,1) the assumed default?
--
Message posted via http://www.sqlmonster.comHi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via SQLMonster.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.sqlmonster.com|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.sqlmonster.com
>
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identity,
is (1,1) the assumed default?
--
Message posted via http://www.sqlmonster.comHi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via SQLMonster.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.sqlmonster.com|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.sqlmonster.com
>
Friday, March 9, 2012
identity in the resultset
i want to generate a column in the resultset
that works like an identity column.
one way i thought is creating a temp table with an identity column
and inserting resultset to that table and select again.
is there any other way that works serverside?
thanks...Using a temp table or a table variable *is* done on the server.
Is there a special reason for using identity? Are you trying to sort or rank
the rows in the result-set? If so, there are built-in functions in SQL2005
and several custom options for SQL2000.
ML|||Always specify SQL Server version you are using when posting questions.
With SQL 2005, you can use ROW_NUMBER() as described in the Books Online.
Another method with older versions is with a subquery that includes a unique
column(s) and the same criteria as the main table query:
SELECT MyData,
(SELECT COUNT(*) + 1 FROM MyTable t2 WHERE t2.Id < t1.Id) As
MyRowNumber,
FROM MyTable t1
ORDER BY Id
Personally, I'd assign the numbers in the client application.
Hope this helps.
Dan Guzman
SQL Server MVP
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OiAOowO9FHA.472@.TK2MSFTNGP15.phx.gbl...
>i want to generate a column in the resultset
> that works like an identity column.
> one way i thought is creating a temp table with an identity column
> and inserting resultset to that table and select again.
> is there any other way that works serverside?
> thanks...
>
>|||i wanted to say , is there any other way that works serverside too?
can you tell about custom options for SQL2000?
thanks
"ML" <ML@.discussions.microsoft.com> wrote in message
news:02F1360F-284E-4DCD-A8FF-A6EDD07825CD@.microsoft.com...
> Using a temp table or a table variable *is* done on the server.
> Is there a special reason for using identity? Are you trying to sort or
> rank
> the rows in the result-set? If so, there are built-in functions in SQL2005
> and several custom options for SQL2000.
>
> ML|||http://www.aspfaq.com/2427
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:uUIfc6O9FHA.3636@.TK2MSFTNGP09.phx.gbl...
>i wanted to say , is there any other way that works serverside too?
> can you tell about custom options for SQL2000?
> thanks
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:02F1360F-284E-4DCD-A8FF-A6EDD07825CD@.microsoft.com...
>|||
> Another method with older versions is with a subquery that includes a
> unique
> column(s) and the same criteria as the main table query:
> SELECT MyData,
> (SELECT COUNT(*) + 1 FROM MyTable t2 WHERE t2.Id < t1.Id) As
> MyRowNumber,
> FROM MyTable t1
> ORDER BY Id
thanks for that.|||Dan posted one, a few can be found here:
Row-numbering:
http://www.aspfaq.com/show.asp?id=2427
Paging:
http://www.aspfaq.com/show.asp?id=2120
ML
that works like an identity column.
one way i thought is creating a temp table with an identity column
and inserting resultset to that table and select again.
is there any other way that works serverside?
thanks...Using a temp table or a table variable *is* done on the server.
Is there a special reason for using identity? Are you trying to sort or rank
the rows in the result-set? If so, there are built-in functions in SQL2005
and several custom options for SQL2000.
ML|||Always specify SQL Server version you are using when posting questions.
With SQL 2005, you can use ROW_NUMBER() as described in the Books Online.
Another method with older versions is with a subquery that includes a unique
column(s) and the same criteria as the main table query:
SELECT MyData,
(SELECT COUNT(*) + 1 FROM MyTable t2 WHERE t2.Id < t1.Id) As
MyRowNumber,
FROM MyTable t1
ORDER BY Id
Personally, I'd assign the numbers in the client application.
Hope this helps.
Dan Guzman
SQL Server MVP
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OiAOowO9FHA.472@.TK2MSFTNGP15.phx.gbl...
>i want to generate a column in the resultset
> that works like an identity column.
> one way i thought is creating a temp table with an identity column
> and inserting resultset to that table and select again.
> is there any other way that works serverside?
> thanks...
>
>|||i wanted to say , is there any other way that works serverside too?
can you tell about custom options for SQL2000?
thanks
"ML" <ML@.discussions.microsoft.com> wrote in message
news:02F1360F-284E-4DCD-A8FF-A6EDD07825CD@.microsoft.com...
> Using a temp table or a table variable *is* done on the server.
> Is there a special reason for using identity? Are you trying to sort or
> rank
> the rows in the result-set? If so, there are built-in functions in SQL2005
> and several custom options for SQL2000.
>
> ML|||http://www.aspfaq.com/2427
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:uUIfc6O9FHA.3636@.TK2MSFTNGP09.phx.gbl...
>i wanted to say , is there any other way that works serverside too?
> can you tell about custom options for SQL2000?
> thanks
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:02F1360F-284E-4DCD-A8FF-A6EDD07825CD@.microsoft.com...
>|||
> Another method with older versions is with a subquery that includes a
> unique
> column(s) and the same criteria as the main table query:
> SELECT MyData,
> (SELECT COUNT(*) + 1 FROM MyTable t2 WHERE t2.Id < t1.Id) As
> MyRowNumber,
> FROM MyTable t1
> ORDER BY Id
thanks for that.|||Dan posted one, a few can be found here:
Row-numbering:
http://www.aspfaq.com/show.asp?id=2427
Paging:
http://www.aspfaq.com/show.asp?id=2120
ML
Friday, February 24, 2012
Identity column in temp table
Hi,
I am trying to create a temp table with an identity column. Here is the code that I am using...
SELECT UserId, IDENTITY(int, 1, 1) AS colId
INTO #User
FROM MyUserTable
WHERE UserId = 1
I am getting an error though.
Server: Msg 8108, Level 16, State 1, Line 9
Cannot add identity column, using the SELECT INTO statement, to table'#User', which already has column 'UserId' that inherits the identityproperty.
Is there any way to work around this?
Thanks for your help.
You'll have to explictly create the #User table with your 2 columns and then INSERT INTO it.
I am trying to create a temp table with an identity column. Here is the code that I am using...
SELECT UserId, IDENTITY(int, 1, 1) AS colId
INTO #User
FROM MyUserTable
WHERE UserId = 1
I am getting an error though.
Server: Msg 8108, Level 16, State 1, Line 9
Cannot add identity column, using the SELECT INTO statement, to table'#User', which already has column 'UserId' that inherits the identityproperty.
Is there any way to work around this?
Thanks for your help.
You'll have to explictly create the #User table with your 2 columns and then INSERT INTO it.
Subscribe to:
Posts (Atom)