| Author |
Topic |
|
coagulance
Yak Posting Veteran
78 Posts |
 Posted - 2008-04-23 : 17:18:55
|
| Would like to have Identity Inserted wrt a date columnEg:ProdDate ID Details============================2008.04.01 1 afafafaf2008.04.01 2 GAFSGHFGF2008.04.02 1 GAGJAGSDH2008.04.02 2 QYTYTT2008.04.03 3 QYTWRRTAt present it is not an Auto increment , I check for the Maximum ID and add One to the next Inserted record. Is there any simpler method? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-23 : 17:26:46
|
| SELECT ProdDate, ROW_NUMBER() OVER(PARTITION BY ProdDate ORDER BY Details ASC) AS 'ID', DetailsFROM YourTableTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
coagulance
Yak Posting Veteran
78 Posts |
Posted - 2008-04-23 : 17:36:11
|
| Hi Thanks,I would like to have this in my Insert Statement Like I just Give the ProdDate and Details , ID should be an Auto Increment wrt ProdDate and Should Start from 1 when the ProdDate Changes |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-04-23 : 23:26:25
|
| Why insert it into the table? Just get the row number as your ID when you select it out as Tara suggests.You might find it helpful to add an actual ID if the date isn't fine grained enough. If so, just add an identity and include it in the ORDER BY is her query. |
|
|
|
coagulance
Yak Posting Veteran
78 Posts |
Posted - 2008-04-23 : 23:32:29
|
| Would like to have ID as a Primary key along with ProdDay. Hence this requirement. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-23 : 23:40:20
|
quote:
Originally posted by coagulance
Would like to have ID as a Primary key along with ProdDay. Hence this requirement.
then its better to add an identity column to your table. Inserting records will automatically increment the value of this column. |
|
|
|
coagulance
Yak Posting Veteran
78 Posts |
Posted - 2008-04-24 : 00:03:24
|
| As explained earlier, I would like to reset this Identity when the ProdDay Starts so that my ID for the Day will not be a vague number. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-24 : 00:11:30
|
| Then you have to manage it. SQL Server will not do this for you.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-24 : 00:32:28
|
| use DBCC CHECKIDENT() if you want to reset the identity column value. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-24 : 02:22:48
|
| DBCC CHECKIDENT() only works when you have an identity column. coagulance can't use an identity column.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-24 : 02:32:24
|
quote:
Originally posted by tkizer
DBCC CHECKIDENT() only works when you have an identity column. coagulance can't use an identity column.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
I'm confused  wasnt OPs query to add an ID column to his table whose value needed to be reset each day? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-24 : 11:10:27
|
| What's the point of using an identity column if you are going to reset it all of the time?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-24 : 11:23:04
|
quote:
Originally posted by tkizer
What's the point of using an identity column if you are going to reset it all of the time?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
Yup thats true  . Sorry was thinking that OP was trying for a temporary table which is used for some intermediate operations and has to clear after day. Didnt happen to me then that same is achieved by using ROW_NUMBER(). my bad.. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-24 : 12:29:10
|
| Well your solution solves the problem at first glance, but then how are you going to manage DBCC CHECKIDENT once per day? If you run it from a job, then there is a chance that rows will get inserted with identity values of the previous day. If you run it from the application, then you've got to provide elevated permissions to the account plus you have to prevent everyone from running any writes until it's been reset. It would be a big management issue.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Insert Identity depending on the value of a Column