Copy a row with one change?

Hello. I have a table that has 3 fields as a primary key - JobID (int),
PONumber (char 16) and PORevision (tinyint). I need to make a new row with
the same data except the PORevision field should be increased by 1. I have
tried a few things but nothing seems to work. I could do it in my vb.net
code but I am sure it would be better in the sql database.
Thanks,
Gerry
INSERT INTO YourTable (jobid, ponumber, porevision, other_col, ...)
SELECT jobid, ponumber, porevision+1, other_col, ...
FROM YourTable
WHERE ...
David Portas
SQL Server MVP

Copy a row with one change?

Hello. I have a table that has 3 fields as a primary key - JobID (int),
PONumber (char 16) and PORevision (tinyint). I need to make a new row with
the same data except the PORevision field should be increased by 1. I have
tried a few things but nothing seems to work. I could do it in my vb.net
code but I am sure it would be better in the sql database.
Thanks,
GerryINSERT INTO YourTable (jobid, ponumber, porevision, other_col, ...)
SELECT jobid, ponumber, porevision+1, other_col, ...
FROM YourTable
WHERE ...
David Portas
SQL Server MVP
--

Copy a row with one change?

Hello. I have a table that has 3 fields as a primary key - JobID (int),
PONumber (char 16) and PORevision (tinyint). I need to make a new row with
the same data except the PORevision field should be increased by 1. I have
tried a few things but nothing seems to work. I could do it in my vb.net
code but I am sure it would be better in the sql database.
Thanks,
GerryINSERT INTO YourTable (jobid, ponumber, porevision, other_col, ...)
SELECT jobid, ponumber, porevision+1, other_col, ...
FROM YourTable
WHERE ...
--
David Portas
SQL Server MVP
--|||Or you could make the PORevision field an identity and
the system would do it for you.
>--Original Message--
>Hello. I have a table that has 3 fields as a primary
key - JobID (int),
>PONumber (char 16) and PORevision (tinyint). I need to
make a new row with
>the same data except the PORevision field should be
increased by 1. I have
>tried a few things but nothing seems to work. I could
do it in my vb.net
>code but I am sure it would be better in the sql
database.
>Thanks,
>Gerry
>
>.
>

Copy a column content in another one only if empty

for MS SQL 2000
table [Users] :
[id_Users] [int] NOT NULL ,
[Name] [varchar] (25) NOT NULL,
[Alias] [varchar] (25) NULL
how can I copy the content of [Name] into the column [Alias] only if [Alias] is Empty ?
thank you--I ve consider null value also in this code.
update Users
set Alias=Name where Alias is null or ltrim(rtrim(Alias))=''|||wonderfull !

it works :-)

thanks a lot

Converting varchar to int (or numeric)

In one of my stored procedures, a varchar is input that is assumed to be an int, and I need to validate that it is an int before I CAST it as an int. I'm currently using ISNUMERIC() to eliminate non-numeric values, but how do I recover gracefully if values such as '7.5e3', '$334', or '45.9943' sneak through? They all pass the ISNUMERIC() test, but cause errors when being cast to int. Similar problems if casting to numeric, etc.You can use the case statement - for example:

declare @.test varchar(20)
select @.test='123.3'
select case when charindex('.',@.test) > 0 then cast(@.test as decimal(10,2))
when charindex('e',@.test) > 0 then cast...
else cast(@.test as int) end|||Thanks. That should work. On retrospect, it's obvious enough I should have come up with that myself!

converting varchar to int

Syntax error converting the varchar value '3.1.7.4.3.9.' to a column of data type int...

how to overcome this problem ..

Hello Raj,

What should '3.1.7.4.3.9.' be, ayou wanting to remove the .'s to have it be 317439? Try this:

=cInt(Replace(Fields!Field1.Value, ".", ""))

Jarret

|||when i run the query it throws the error... i have to handle the error in the data tab itself... can you help me how to find the exact field in which the irregular datatype is located ...is there any way to track down the issue...|||

It is clearly an SQL error which cannot be solved in SSRS using expressions.

I'm assuming that your dataset is formed by a stored procedure, not plain SQL text.

The error is because you are trying to put an invalid integer value into a table (temporary table or user table) column which is of INT datatype. If possible, get the SQL text of the stored procedure and check all insert/update statements and all the columns of INT datatype involved in it. Also check If there is an implicit conversion using CONVERT or CAST function.

Shyam

|||can you tell me the problem is with varchar or datetime... because i didnt find any varchar like '3.1.7.4.3.9.'|||

Yes, it is definitely a varchar. You may have to look out for columns in the table that is being used as source to insert/update the value in the table which has a corresponding column of INT datatype.

Shyam

|||thank you shyam .. i will check it out...

Converting varchar to int

Hi,
I have a varchar(255) column where I may have data like this:
43294430949
adkk3400
1056
ff1d
10
302
15000043
I would like to write a SQL query that returns values between
10 and 500 (numeric)
If I just do this:
Select * from table where column between '10' and '500'
I would also get 15000043. That's incorrect
I also tried doing this:
Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
column) <=500
but it fails when I have characters in the column.
Do you guys know how I can do that?
ThanksFirst you need a solid function that can determine if the value is numeric.
For
that go here: http://www.aspfaq.com/show.asp?id=2390.
Select *
From #Test As T
Where IsNumeric(T.Data) = 1
And dbo.IsReallyInteger(T.Data) = 1
And Cast(T.Data As BigInt) Between 10 And 50
Why the two checks? If you only use IsReallyNumeric, SQL cannot determine wh
at
that function actually does and more specifically, whether it filters for va
lues
that will be castable to BigInt. Then why use IsReallyInteger in the first
place? The reason is that IsNumeric is faulty in its determination of numeri
c
values. Characters like "$" and "d' and other odd characters can return true
for
a IsNumeric.
HTH
Thomas
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int, colum
n)
> <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks|||Try,
Select *
from table
where
case
when c1 like '[0-9][0-9]' or c1 like '[0-9][0-9][0-9]' then cast(c1 as int)
else null
end between 10 and 500
AMB
"Star" wrote:

> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
> column) <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks
>|||You can write your query with a WHERE clause like:
WHERE CASE WHEN ISNUMERIC( col ) = 1
THEN CAST( col AS BIGINT )
END BETWEEN 10 AND 500 ;
Note that there are certain considerations with ISNUMERIC with characters
like e, d, $ etc. in which case you'd have to use PATINDEX to make sure the
values are numerically compatible. Also, is the converted value is beyond
the value limitations of INT or BIGINT or even DECIMAL values, then you'll
get an overflow error.
Anith|||We need more information:
1) How should the values such as 'adkk3400' be considered? Do you want this
to be 3400 numeric, or do you want to ignore rows with nun-numeric content?
2) For the numeric values, the 'int' data type would not work for your first
value '43294430949' - it is outside the rane of acceptable values. Would
'bigint' be OK?
So, for example, if you are ignoring rows with alphabetical characters, and
your data only contained numbers and letters, you could try something like
this (untested pseudo-code, since you did not provide DDL, sample data, or
expected results [http://www.aspfaq.com/etiquette.asp?id=5006]):
SELECT <Final Column List>
(SELECT UglyDataColumn, <Other Column List>
FROM MakeBelieveTable
WHERE UglyDataColumn NOT LIKE '%[A-Za-z]%') NUMONLY
WHERE CAST(NUMONLY.UglyDataColumn AS bigint) BETWEEN 10 AND 500
If this is not what you are looking for, you will need to provide better
specifications.
P.S. Out of curiosity, what type of information exactly is this
'UglyDataColumn' holding? That is a seriously bad assortment of values, and
I am guessing there are either some missing constraints on that column, or
the design is fundamentally flawed.
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
> column) <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks

converting the varchar value to int

All,
I am running this statement and getting the error below. I am assuming
that the issue is the CLCCHRGE.CCOUNT data type is Varchar and needs to
be converted using a CONVERT clause or a CASE statement but I am not
sure I am on the right path here and if I am then I am not sure of the
syntax.
SELECT CLCHRGE.CCOUNT, CLCHRGE.CPTPRT, CLCHRGE.XCDATE,
CLMSTER.PLNAME, CLMSTER.PFNAME, procdesc, dignosis
FROM CLCHRGE INNER JOIN
CLMATER ON CLCHRGE.CCOUNT = CLMSTER.CCOUNT
WHERE (CLCHRGE.XACDATE BETWEEN '2005-01-01' AND '2005-1-31')
AND (CLCHRGE.CPTPINT = '90801') AND CLCHRGE.CCOUNT in (608685, 477078,
608201, 204739)
order by xacdate, plname desc
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'C171913' to a column of data
type int.Try changing this
CLCHRGE.CCOUNT in (608685, 477078,
608201, 204739)
to this
CLCHRGE.CCOUNT in ('608685', '477078',
'608201', '204739')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Success!, Thank you for the tip!! You are a Lifesaver!

Converting string to int

Hi,

This probably is a basic question but I can't figure it out...

Because SQL Server's ISNUMERIC function allows some strange values to count as numeric (such as '\'), I want to create my own function that will only return an integer if the value is able to be converted (otherwise it will return 0). I have created the following function to do this:

CREATE FUNCTION [dbo].[fnConvertToInt]
(
@.str nvarchar(30)
)
RETURNS int
AS
BEGIN
DECLARE @.s int

BEGIN TRY
SET @.s = convert(int, @.str)
END TRY
BEGIN CATCH
SET @.s = 0
END CATCH
-- Return the result of the function
RETURN @.s
END

When I run this however, I get errors from having the TRY CATCH in a function:

Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.

How can I convert a string to an integer without getting an error? I am using SQL Server 2005.

Hi,

the isnumeric function is hazle, but you can implement some isreallynumeric, like aaron did this on his website:

http://www.aspfaq.com/show.asp?id=2390

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

converting stored procedure

CREATE PROCEDURE procCreateBasket

@.ShopperID int,

@.BasketID int OUTPUT

AS

INSERT INTO Basket(ShopperID)

VALUES (@.ShopperID)

SELECT @.BasketID = @.@.IDENTITY
I don't want to use this as a stored procedure I want to convert it as a string an use in that way in my asp.net application how can I do that?

Simply just take out the procedure declaration. For example, your string will be everything after the AS keyword.|||Adding to what master4eva said, you should be using SCOPE_IDENTITY()instead of @.@.IDENTITY. The former returns a more accurate value of theId just inserted as compared to @.@.IDENTITY. Check out books on line for more info.

converting sql_variant column type to int

Hi,
I've got a table which has got a sql_variant column (sql server instance is 2000). the current data in that column are all numbers so can be converted to int.
I want to change the column type from sql_variant to int for that column. I have tried

alter table mytable alter column mycolumn int

and it complains
Disallowed implicit conversion from data type sql_variant to data type int, table 'mydatabase.dbo.mytable ', column 'mycolumn '. Use the CONVERT function to run this query.

I can do this task using EM but what that does is creating a new table temp table with int column type transferring data , dropping the original table and renaming the temp table to initial name.

therefore I need T-SQL command
Thanks

Really, I think I would suggest the same thing that you describe the EM doing -- drop the constraints on the original table, create the new table, copy in the data and then drop the old table. Can we get second and third opinions here?|||problem: this is template for a table in production server, which can not be dropped and also there are many constraints that points to this table. so using EM is not an option.

|||I would start by scripting out the table from the EM and massaging the generated script. Again, I'm not sure that you are going to be able to get out of creating a new version of the table and I would like additional opinions on this.|||

See if this helps.

create table dbo.t1 (

c1 sql_variant null

)

go

insert into dbo.t1 values(1)

go

alter table dbo.t1

add c2 int

go

update dbo.t1

set c2 = cast(c1 as int)

go

alter table dbo.t1

drop column c1

go

exec sp_rename 'dbo.t1.c2', 'c1', 'COLUMN'

go

select *

from dbo.t1

go

drop table dbo.t1

go

If there are indexes or constraints referencing column [c1], you have to drop them first and recreating them after renaming new column.

AMB

|||good solution, but column order is important for me. I think I can got to syscolumn table and change the column order there but this won't work on 2005|||

Kolf,

> but column order is important for me

Sorry about it, that is the beauty about relational dbs, the position of the column is not important at all. If that is the case, there is not other option than creating a new table, move all data, drop constraints refrencing the table, drop old table, recreate constraints and indexes. That is what" Enterprise Manager" or "Management Studio" does.

AMB

Converting percent-decoded data, or characters to int

Say I have a percent encoded expression like www%2emysite%2ecom. An unencoded representation would be www.mysite.com.

I have written a tsql regex function to parse an input string and replace any legal percent-encoded substring with its ascii equivalent, returning the unencoded version of the input string. However I'm stuck on one crucial step: converting a percent-encoded substring (ex: '%2e') or its hexadecimal string representation ('0x2e') to an integer.

I've tried CONVERT(bigInt, '0x2e') but of course that isn't working.

What is the best way to go about this? I've been all over the sql server books online, search engines, and other forums. I can't get past this one step. Am I on the right track, or should I look at it a different way?

Without knowing more about the rest of your code, you can do this: exec ('select CONVERT(int, ' + '0x2e' + ') '), though not in a t-sql function (but I don't know how you are doing regex in a T-SQL function)

|||

you have to convert the varchar value to binary value..

You can't direcly assign this value from the varchar varibale/column, if you convert the varchar value it will create a binary equalent of the string..

I once used the following logic to overcome this issue,

Declare @.ValueString as Varchar(100)
Set @.ValueString = '0x2e'

Declare @.Value as VarBinary
Declare @.sql as NVarchar(1000)

Select @.sql = N'select @.val=' + @.ValueString
Exec sp_executesql @.sql, N'@.val varbinary output', @.Value output

Select char(convert(bigint, @.value))

--Result: .

Here we are assinging the Binary value directly to the VarBinary variable, with out using the convert function..But i am not sure how it will help you to create a generic function(since inside the funcation you can't able to call sp_executesql)..

|||Thank you.

I don't know how you are doing regex in a T-SQL function

My t-sql regex find function works via vbScript RegExp class. I'm sure it's not the most efficient ,but it does work very well.

I still haven't made any progress with the current issue. I don't understand why a character expression that is also a well-formed hexadecimal number can't be easily converted into an integer. Sigh...

converting newId()

Hi there!
Is there any way to convert generated newId() hexa into an int ?
for example, the first two char into an int which won't be greater
than 255
I've searched for 'convert' or 'cast' but i found nothing...
any idea?
thanks a lot
Vince.Why not just use RAND() ?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vince .>" <vincent@.<remove> wrote in message
news:qfp541pgb9rfon1nk7cblubersvl3ki0cc@.
4ax.com...
> Hi there!
> Is there any way to convert generated newId() hexa into an int ?
> for example, the first two char into an int which won't be greater
> than 255
> I've searched for 'convert' or 'cast' but i found nothing...
> any idea?
> thanks a lot
> Vince.|||If you just want to convert newid() to int, try:
select convert(int, convert(varbinary, newid()))
"Vince .>" wrote:

> Hi there!
> Is there any way to convert generated newId() hexa into an int ?
> for example, the first two char into an int which won't be greater
> than 255
> I've searched for 'convert' or 'cast' but i found nothing...
> any idea?
> thanks a lot
> Vince.
>

Converting negative integer from string to int

I'm having an issue using the data conversion task and trying to convert from a negative integer that is bounded by brackets e.g, (1) for -1. I keep getting an error that the value cannot be converted although I've tried all signed integer types.

Yeah that doesn't surprise me. You'll be able to do this with a Derived Column component. *I think* the following should do it (assuming the column is called yourCol:

(DT_I4)SUBSTRING(yourCol, 2, 1)

-Jamie

|||

If there are positive numbers in that column as well, you may need to place a conditional split before the derived column; and then, as Jamie suggested, use an expression (in a derived column transform) to remove the brackets and to multiply the resulting number by -1 so you get the negative sign.

|||

A Derived Column Tx would be my preferred option, as this could be done in a single transform. There is no need for the split either. The following expression should do the trick. It avoids the need for any conditional processing, are we +ve or -ve type stuff. The only thing I have not catered for is NULLs.

(DT_I4)REPLACE(REPLACE([Column], "(", "-"), ")", "")

|||Thanks Darren, I'm going to give that a try. It's bizarre because I have a DTS in SQL 2000 and it handles this issue without any problem or conversion issues.|||

whatthedilly wrote:

Thanks Darren, I'm going to give that a try. It's bizarre because I have a DTS in SQL 2000 and it handles this issue without any problem or conversion issues.

The fact that DTS does this is not a good thing. It is a very bad thing and SSIS was a conscious effort to move away from DTS's technique of effectively guessing how it should treat values. With SSIS you have to explicitly tell it how to treat values. You are in control. This removes the possibility of the engine making the wrong guess - as DTS was wont to do.

-Jamie

Converting nchar to int (or numeric/decimal)

Hi ,

I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).

I tried all the ways i can and still i get the same error..:

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E07. An OLE
DB record is available. Source: "Microsoft OLE DB Provider
for SQL Server" Hresult: 0x80040E07 Description: "Error
converting data type nvarchar to numeric.".

Can we actually do it...? any help would be appreciated.

thanks

ravi

you have non-numeric data in the source column that fails the conversion. Have you tried to configure the error output of the conversion transformation to re-direct error so the 'bad' rows are sent to a diffrent output where youc can inspect them?|||

Hi,

No ..No....Its Numeric in the source though the data type is nchar(3).

The thing is actually the column in the extract table has records as 110,150,160,170 and so on .....but its data type is nchar(3).The reason is this extract table comes from AS400 and while extracting As400 doesnt allow to send records if the column in the extract table on Sql Server is defined other than nchar...so have to go with nchar or nvarchar.

thanks

ravi

|||Try a derived column to cast the records. (Why not make them integers?)

(DT_I4)[Column]|||

Works fine for me:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'211'

INSERTINTO #temp SELECT'311'

SELECTSUM(cast(test asint))FROM #temp

DROPTABLE #temp

Adamus

|||

Adamus Turner wrote:

Works fine for me:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'211'

INSERTINTO #temp SELECT'311'

SELECTSUM(cast(test asint))FROM #temp

DROPTABLE #temp

Adamus

Yeah, but Ravi's source is AS400, not SQL Server. Though, the other option is to stage the data into SQL Server as it is in AS400, and then use a cast statement when pulling records from the staging table as Adamus has illustrated here.|||

Hey phil,

I extract the table from AS400 to extract_table on my extract database which is SQL Server DB , from there when i need to make some transformations then my column on the staging is decimal(3,0)....where as on the extract table it is nchar(3).

I tried similarly by declaring some variables and casting and converting ...it doensnt work..or am i doing some mistake...:

DECLARE @.X nchar(3)

DECLARE @.Y int

SET @.X ='GGG'

SELECT @.X

SET @.Y =Cast(@.X asint)

SELECT @.Y

thanks for all you views/immediate response's...

let me know if i am doing a mistake here..

thanks,

ravi

|||Then Adamus' example should apply.|||

Hi ,

I think i figured what it was i mean i have like around 200,000 records being pulled in to the extract there ....from which were few records which were nonnumeric in the nchar(3) column i.e apart from '100' , '510' ......there were records with 'MVR' which was causing all the problem... guess...

thanks for the quick responses...

thanks..!!

Ravi

|||

Then check isnumeric before the cast and set non-numerics to 0:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'AAA'

INSERTINTO #temp SELECT'311'

SELECTCASEISNUMERIC(test)WHEN 1 THENCAST(test asint)ELSE 0 END

FROM #temp

DROPTABLE #temp

Adamus

|||Just a tweak. You may want to set them to NULL, versus a 0. NULLs would be excluded from any math/aggregation totals you may be creating, such as an average. Using a 0 would allow that value to be used. So it's up to you.

In SSIS, you can use a conditional split to move numeric records to your destination table, and non-numerics to the trash, if you desire. Or, you can assign new values to the non-numerics. Up to you.

converting mysql 2 sql 2000

how do i go about converting the Mysql Script below to Sql Server Script ??

CREATE TABLE cat (
id int NOT NULL auto_increment,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);

INSERT INTO cat VALUES (1,'Not Categorised');

ALTER TABLE cat AUTO_INCREMENT = 5;

i want a script that:
creates the table
inserts a row of data and then
sets the increment field to start at 5

i tried using identity(1,1) and
SET IDENTITY_INSERT cat ON;
INSERT INTO cat VALUES (1,'Not Categorised');

but when i enter the data in i get the error below

An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

please can anyone help or advice

thanksOriginally posted by m.inckle
how do i go about converting the Mysql Script below to Sql Server Script ??

CREATE TABLE cat (
id int NOT NULL auto_increment,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);

INSERT INTO cat VALUES (1,'Not Categorised');

ALTER TABLE cat AUTO_INCREMENT = 5;

i want a script that:
creates the table
inserts a row of data and then
sets the increment field to start at 5

i tried using identity(1,1) and
SET IDENTITY_INSERT cat ON;
INSERT INTO cat VALUES (1,'Not Categorised');

but when i enter the data in i get the error below

An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

please can anyone help or advice

thanks
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.|||Originally posted by harshal_in
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.

is there any way to do this in a script ???|||Originally posted by harshal_in
create table as:
CREATE TABLE cat (
id int NOT NULL ,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
then insert the record:
INSERT INTO cat VALUES (1,'Not Categorised');

then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.

is there a way of altering an identity column in a table like below

ALTER TABLE cat ALTER COLUMN id INT NOT NULL identity(5,1);

i always get
Incorrect syntax near the keyword 'identity'.|||Originally posted by m.inckle
is there a way of altering an identity column in a table like below

ALTER TABLE cat ALTER COLUMN id INT NOT NULL identity(5,1);

i always get
Incorrect syntax near the keyword 'identity'.
no. as far as my knowledge goes you can't add identity to an existing column thru alter table, u have to add a new column for it.
so the easiest way is to use enterprise manager.