Identity, seed, increment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identity, seed, increment

Author

Topic

GB
Starting Member

22 Posts

Posted - 2005-08-25 : 10:03:45

Iam trying to add a column to an existing table that would be an "identifier". I called it "ReadingNumber" and selected Identity "Yes" and "Identity increment" as 1.
When I add it, it just gives the rows random numbers instead of by the order they were inserted into the database by.....is there a way to autonumber the columns correctly? I have a COLUMN called Date and also one called Time that have the date and Time, but the format is char.
Would I have to convert the date time columns into something SQL understands, sort them ASC or DESC and then do the Identity column add?
Thanks for any help

Edited

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-25 : 10:25:45

If you need the identity values for existing rows in a specific order then you might need to do something like this:


set nocount on

create table myTable 
	(strDate varchar(10)
	,strTime varchar(10))

create table myNewTable 
	(ReadingNumber int identity primary key
	,strDate varchar(10)
	,strTime varchar(10)
	,ConvertedDate datetime)
go

insert myTable
select '9/1/2000', '10:52:27.0' union
select '1/2/1999', '17:25:35.1' union
select '8/20/2004', '1:15:22.0'

select * from myTable

insert myNewTable
	(strDate
	,strTime
	,ConvertedDate)

select	strDate
	,strTime
	,ConvertedDate = convert(datetime, strDate + ' ' + strTime)
from	myTable 
order by convert(datetime, strDate + ' ' + strTime)
go

drop table myTable
go
exec sp_rename myNewTable, myTable
go

select * from myTable

go

drop table myTable

Be One with the Optimizer
TG

GB
Starting Member

22 Posts

Posted - 2005-08-25 : 10:38:55

Wow...
Iam new to sql so please bear with me.
From the looks of it, you are joining the date and time fields in another table then putting them back into the original table?
I just want to sort and number the entrys by their date and time insert....is this the simplist way to do it?
Thanks for your help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-25 : 10:44:29

If your objective to assign identity values in a specific order then I think you'll need to use the alternate table approach. If all you want to do is return the combined values in a Select statement in order of date then all you need to do is:


select	strDate
	,strTime
from	myTable 
order by convert(datetime, strDate + ' ' + strTime)

Be One with the Optimizer
TG

GB
Starting Member

22 Posts

Posted - 2005-08-25 : 10:55:59

OK - I tried the second method and get a "invalid syntax" at strDate....did you see my edit to my first post in this topic?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-25 : 11:00:48

GB, post the code that generated the syntax error and we'll get it sorted out.

by the way, here a link that one of the contributors to this site has put together that really helps get questions answered fast.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

GB
Starting Member

22 Posts

Posted - 2005-08-25 : 11:04:34

SELECT strDate, strTime
FROM Press_3
ORDER BY CONVERT(DATETIME strDate + ' ' + strTime)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-25 : 11:22:17

sorry, you'll also need to post an example of your data from Press_3 and the datatypes of the columns you are using. strDate and strTime are column names from my previous example. You'll need to use the column names from your table. (this is where that link above comes in handy

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-25 : 11:23:40

On a related note:

Since you're new to sql server you may be new to database develoment in general so let me
explain a few concepts. (just my opinions, if other readers dissagree I'm sure we'll
hear from them)

identity columns:
identity columns are mainly used as surrogate keys who's only purpose is to uniquely
identify a row. The value and sequence of the identity should be meaningless. Many
developers incorrectly use the identities to display things like row numbers in reports
or to order output. For either of these objectives there should be other columns in
the table to accomplish these tasks like a datetime value or a "lineNumber" column.

Dates:
Storing dates in sql server as any datatype other than datetime or smalldatetime would
be like storing numbers as: (one, two, three, four) They would sort alpabetically,
you couldn't perform mathmatical operations on them, they would be useless.

Some limitations on the way sql server allows for altering tables.
The ALTER TABLE ALTER COLUMN command is very powerfull but won't allow you to add
and remove identity the property of an existing column. For that reason, it's
necessary to either drop and re-add the column or the entire table when you want to
alter a column to add or remove the identity property.

Sorry for (not asked for) opinions, I hope they help...

Be One with the Optimizer
TG

GB
Starting Member

22 Posts

Posted - 2005-08-25 : 11:35:51

Any help is appreciated, thanks for taking the time!

GB
Starting Member

22 Posts

Posted - 2005-08-25 : 12:16:53

On dates and times ....Iam doing the inserts to this table from a product called wonderware.....and the strings are the only way I know of to do it...
Is there a way to make sql date/time stamp the insert?
Thanks
Gary

GB
Starting Member

22 Posts

Posted - 2005-08-25 : 12:23:54

Column names
2 ReadingNumber bigint 8 0
0 [Date] char 8 0
0 [Time] char 10 1
0 BlankID int 4 1
0 ProgramNo smallint 2 1
0 ActualProgramNo smallint 2 1
0 LotNumber real 4 1
0 PressLoad bigint 8 1
0 ProgramHighPSISP int 4 1
0 ActualHighPSISP int 4 1
0 ProgramHighPSITimeSP int 4 1
0 ActualHighPSITimeSP int 4 1
0 ProgramRampSP int 4 1
0 ActualRampSP int 4 1
0 ProgramLowPSISP int 4 1
0 ActualLowPSISP int 4 1
0 ProgramLowPSITimeSP int 4 1
0 ActualLowPSITimeSP int 4 1
0 ProgramTempSP int 4 1
0 ActualTempSP int 4 1
0 ActualPSISP int 4 1
0 ActualPSI int 4 1
0 ActualTemp int 4 1
0 TotalPressTimeSP numeric 5 1
0 ActualPressTimeSP numeric 5 1
0 HighPSIOverride char 3 1
0 HighPSITimeOverride char 3 1
0 RampTimeOverride char 3 1
0 LowPSIOverride char 3 1
0 LowPSITimeOverride char 3 1
0 TempOverride char 3 1
1 PressProgramNoOverride char 3 1

Sample data
2304 8/24/2005 15:10:10 2337 56 56 30927 2 2771 2771 300 300 60 60 1001 1001 60 60 285 285 2771 2746 280 420 420 No No No No No No No
2305 8/24/2005 15:14:10 2337 56 56 30927 2 2771 2771 300 300 60 60 1001 1001 60 60 285 285 2771 2769 286 420 420 No No No No No No No
2306 8/24/2005 15:03:10 2337 56 56 30927 1 2771 2771 300 300 60 60 1001 1001 60 60 285 285 2771 2777 284 420 420 No No No No No No No
2307 8/24/2005 15:03:40 2337 56 56 30927 1 2771 2771 300 300 60 60 1001 1001 60 60 285 285 2771 2766 285 420 420 No No No No No No No
2308 8/24/2005 15:15:10 2337 56 56 30927 2 2771 2771 300 300 60 60 1001 1001 60 60 285 285 1561 1956 286 420 420 No No No No No No No
2309 8/24/2005 15:16:10 2337 56 56 30927 2 2771 2771 300 300 60 60 1001 1001 60 60 285 285 1001 1338 286 420 420 No No No No No No No
2310 8/24/2005 15:04:10 2337 56 56 30927 1 2771 2771 300 300 60 60 1001 1001 60 60 285 285 2771 2770 286 420 420 No No No No No No No
2311 8/24/2005 15:05:10 2337 56 56 30927 1 2771 2771 300 300 60 60 1001 1001 60 60 285 285 2771 2770 286 420 420 No No No No No No No

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-25 : 13:09:39

>>Is there a way to make sql date/time stamp the insert?
You can insert the current datetime value usingthe getdate() function ie:
insert myTable (myDateTimeColumn)
values (getdate())
see Books Online which is the Help that ships with sql server

>>OK - I tried the second method and get a "invalid syntax" at strDate
Since your column names are [Date] and [time]
(which by the way are really bad names for columns) you need:

SELECT [Date], [Time]
FROM Press_3
ORDER BY CONVERT(DATETIME [Date] + ' ' + [Time])

Be One with the Optimizer
TG

GB
Starting Member

22 Posts

Posted - 2005-08-25 : 13:19:21

Why are they bad names?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-25 : 13:30:46

Because they are totaly non-descriptive of what business concept they represent. Plus, they are probably key words.

Bad object names include: (code,id,group,date,From,To)
Good object names could be: (zipCode, CompanyID, InsuranceGroupDesc, CreatedDate, StartBytePosition, EndBytePosition)

Be One with the Optimizer
TG

GB
Starting Member

22 Posts

Posted - 2005-08-25 : 13:34:49

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources