Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need to insert a value based on year_num (in SQL Server db) using computed column spec

Example:

if year is 2012 then inserting first row it will be 2012_1 next time 2012_2 ...and if year is 2013 then 1st time 2013_1, next 2013_2...
Posted
Comments
Ed Nutting 5-Mar-12 17:02pm    
Can you really not write a basic one probably two queries to do this... select stuff from database where yearnum contains current year, then insert new record where yearnum is cureent year_ + the total number of records returned by the first query. Possibly not the most efficient solution but one that should do.

Hope this helps,
Ed
saravananraju 6-Mar-12 2:35am    
hi Ed.. i tried but i didnt get the exact ans
Pete O'Hanlon 6-Mar-12 5:49am    
In a multi-user environment like a database, this is not always going to guarantee that you will get a unique key. Consider the case where you have two users who both query the database for the number of rows at the same time - they will both get the same number, then when they attempt the concatenation, this will give the same key.
Ed Nutting 6-Mar-12 12:35pm    
True but other than keeping a cached integer value for the current key num which will remove the multi-thread issue or doing as you suggest, using transactions, this issue cannot be solved. I would suggest that storing a static integer of the key count in memory on the web server would (given it's such a small thing, since OP appears to only be creating keys for current year, so only one int required), be more efficient than requesting and locking the database every time as you suggest - not that your suggestion isn't valid and yours is better if OP were trying to store larger amounts of/more detailed/longer persistence records. Anyway, I'll vote 5+ for your answer :) I'm not convinced by P.S. Vijay's - perhaps you might like to take a look at it?

Ed
Pete O'Hanlon 6-Mar-12 14:59pm    
I've had a look at his solution - it's actually quite a neat solution; something about it's niggling at me though - I suspect the niggle is based on the problems of keeping this in sync if the user decides that the date could be updated.

It May be Work

I Create A table T1
SQL
Create table t1 (mVal nvarchar(10), Col nvarchar(10), CreateDate datetime )

Write a udf mVal

SQL
Create Function mVal()
RETURNS nvarchar(10)
as
	Begin 
		Declare @val as nvarchar(10)
		set @val = (Select cast(year(getdate()) as nvarchar(10))+ '_' + cast(isnull(Count(*),0)+1 as nvarchar(10))  from t1 where year(CreateDate) = year(GetDate()))

		Return @val
	End


Open table T1 in design mode and Set default values for following fields

SQL
Col                 : mVal()
Create Date    : as getdate()


Allow NULL will be True.

When insert data in t1 table
SQL
insert into t1 (mval) values('XYZ')
insert into t1 (mval) values('ABC')


rest columns will fill automatically .

SQL
mVal	Col	CreateDate
--------------------------------------------------
XYZ	2012_1	2012-03-06 14:48:48.080
ABC	2012_2	2012-03-06 14:55:25.203
 
Share this answer
 
If you need to create a derived key such as this, then a common approach would be to have a second table. This table would consist of the year number and a count of the number of records already inserted for that year. So, it would look something like this:
SQL
CREATE TABLE [dbo].[YEAR_MANAGER]
(
    YEAR_PART int NOT NULL,
    COUNT_YEAR int NOT NULL
)
Now, this is where things get slightly complicated so please bear with me on this. First of all, you need to create a transaction to run the SQL in. We are going to rely on this to help us lock the year row while we are getting the value. If you are doing this dynamically (in other words, you are adding the years in only when you get files in that match, i.e. you aren't prepopulating the years), you need to do the following
SQL
INSERT INTO [dbo].[YEAR_MANAGER]
SELECT @Year, 0
FROM [dbo].[YEAR_MANAGER]
WHERE NOT EXISTS (SELECT COUNT(1) FROM [dbo].[YEAR_MANAGER] WHERE YEAR_PART = @Year)
This gives you a populated row for the year. Now, comes the clever part (but it's a bit complex, so please follow closely). At this part, we are going to update the row and then return it like this:
SQL
UPDATE [dbo].[YEAR_MANAGER]
SET COUNT_YEAR = COUNT_YEAR + 1
WHERE YEAR_PART = @YEAR;
SELECT YEAR_PART FROM [dbo].[YEAR_MANAGER] WHERE YEAR_PART = @YEAR
Effectively, what this snippet does is updates the count of the rows for that particular year. As we are inside a transaction, we are going to lock this row until we have done the row and returned it. Make sure that you have chosen an appropriate locking method for your update such as an exclusive lock. The reason we do the update first is to get the lock, once we have updated the row, we can select the value from it and use this; in other words, we get the value because we have successfully taken the lock out. From this, you can see that the reason we initialise the count to 0 is because we do the update before we select it.

I hope that his helps. I apologise in advance if the syntax isn't 100% exact here, as I've just knocked this example up in the editor here on CP.
 
Share this answer
 
SQL
UPDATE tableName
set columnName=EnteredYear+'_'+
    case when(convert(varchar,ide)<=9)
        then
            '00'+CONVERT(VARCHAR,ide)
        else
            '0'+CONVERT(VARCHAR,ide) end
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900