Add Identity Property to existing table

How can I add an Identity property using t-sql when the
table has already been created and the column already
exist? The column name is InvoiceItemID.
TIA,
Vic
You can't. Nor can Enterprise Manager so it creates a new table, copy over the data etc etc etc. I
suggest you do the operation in EM, and press the "save change script" button to see what SQL code
EM does for this operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Vic" <vduran@.specpro-inc.com> wrote in message news:0e2701c4f27f$30055240$a501280a@.phx.gbl...
> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
> TIA,
> Vic
|||> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
You can't add Identity property to an existing column. Yo can do it through
EM, but it will actually create a new table, copy all of the rows, drop the
old table and rename the new table to the old name in a single big
transaction.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Add Identity Property to existing table

How can I add an Identity property using t-sql when the
table has already been created and the column already
exist? The column name is InvoiceItemID.
TIA,
VicYou can't. Nor can Enterprise Manager so it creates a new table, copy over the data etc etc etc. I
suggest you do the operation in EM, and press the "save change script" button to see what SQL code
EM does for this operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Vic" <vduran@.specpro-inc.com> wrote in message news:0e2701c4f27f$30055240$a501280a@.phx.gbl...
> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
> TIA,
> Vic|||> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
You can't add Identity property to an existing column. Yo can do it through
EM, but it will actually create a new table, copy all of the rows, drop the
old table and rename the new table to the old name in a single big
transaction.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Add Identity Property to existing table

How can I add an Identity property using t-sql when the
table has already been created and the column already
exist? The column name is InvoiceItemID.
TIA,
VicYou can't. Nor can Enterprise Manager so it creates a new table, copy over t
he data etc etc etc. I
suggest you do the operation in EM, and press the "save change script" butto
n to see what SQL code
EM does for this operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Vic" <vduran@.specpro-inc.com> wrote in message news:0e2701c4f27f$30055240$a501280a@.phx.gbl.
.
> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
> TIA,
> Vic|||> How can I add an Identity property using t-sql when the
> table has already been created and the column already
> exist? The column name is InvoiceItemID.
You can't add Identity property to an existing column. Yo can do it through
EM, but it will actually create a new table, copy all of the rows, drop the
old table and rename the new table to the old name in a single big
transaction.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

add and subract time to a date

Hello all,
Lets say I have the current date and I would like to get the date
two weeks in advance. How would I do this in T-sql? The T-SQL should
take into account if the month changes. For example, if todays date
is 1/17/2003 the advanced date would be 2/17/2003.

Thanks,
BillyYou can use DATEADD function to accomplish this. Refer to SQL Server Books
Online for more details. For example :

SELECT DATEADD(m, 1, '20030117') ;

--
- Anith
( Please reply to newsgroups only )

Add a default value to an existing column

How can I add a default value to an existing column via T-SQL? I tried the
following but not successful:
Alter Table MyTbl Alter Column MyCol Int Default 0.8
TIANote that it will not update previously stored null values
Madhivanan|||The statement fails even if the table is empty (no record). I get the error
message:
"Incorrect syntax near the keyword 'Default'."
The statement I tried: Alter Table MyTbl Alter Column MyCol Int Default 0.8
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1111053136.539506.125770@.z14g2000cwz.googlegroups.com...
> Note that it will not update previously stored null values
> Madhivanan
>|||Hi
create table #test
(
col decimal(18,3)
)
alter table #test add constraint my_def default 18.3 for col
"krygim" <krygim@.hotmail.com> wrote in message
news:eS$fAhtKFHA.436@.TK2MSFTNGP09.phx.gbl...
> The statement fails even if the table is empty (no record). I get the
error
> message:
> "Incorrect syntax near the keyword 'Default'."
> The statement I tried: Alter Table MyTbl Alter Column MyCol Int Default
0.8
>
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1111053136.539506.125770@.z14g2000cwz.googlegroups.com...
>|||It works. Thanks Uri.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kQoOytKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Hi
> create table #test
> (
> col decimal(18,3)
> )
> alter table #test add constraint my_def default 18.3 for col
>
>
>
> "krygim" <krygim@.hotmail.com> wrote in message
> news:eS$fAhtKFHA.436@.TK2MSFTNGP09.phx.gbl...
> error
> 0.8
>

Actually, its a T-SQL question

I am trying to update a project using plaintext sql commands to stored procedures. I am currently working on

orderby = request.querystring("orderby")
if orderby = "" then orderby = "name"
sql="select * from products order by " & orderby"
rs.open sql,conn,3,3

(yes, that's classic ASP but its the t-sql that's a problem, not the asp)
orderby can be "name","code","qty","price"
The stored procedure I have is

CREATE PROCEDURE DBO.GetSortedProducts_sp
@.sort int
AS</P><P> SELECT
*
FROM
PRODUCTS
ORDER BY
case
when @.sort=1 then name --varchar(100)
when @.sort=2 then code --varchar(20)
when @.sort=3 then qty --int
when @.sort=4 then price --decimal 9(19,4)
end
GO

Only @.sort=3 and @.sort=4 work. For @.sort=1 and @.sort=2, I get the following error:
Server: Msg 8114, Level 16, State 5, Procedure GetSortedProducts_sp, Line 5
Error converting data type varchar to numeric.
I have included the column types in the stored procedure above for reference.
If I write out the stored procedure in Query Analyzer I get the following error:
Syntax error converting the varchar value 'Prod-1' to a column of data type int.
Note that if I use "select * from products order by code" then it works fine. Any help please?

|||My guess is when you are using CASE the columns should all be of same datatype.|||As Dinakar said, with the CASE construct you've used all options must result in the same datatype.
Try this approach instead:
SELECT
*
FROM
PRODUCTS
ORDER BY
case when @.sort=1 then name end,
case when @.sort=2 then code end,
case when @.sort=3 then qty end,
case when @.sort=4 then price end

|||interesting...thx