Auto-generate a column value - 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

Auto-generate a column value

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-01 : 09:49:00

Hi,

I have a table called dbo.test. This table is built with two columns, namely; col_id and col_name..

Now, I want col_id to be auto-populated when a new col_name is added.

But the col_id - should be of formulae xyyyy

where x = 2

and yyyy = will be incremental

Therefore, the first 3 records will be like ;

col_id Col_name
20001 A
20002 B
20003 C
......
......

and so on

Now, is there a way SQL can auto-populate teh col_id whenever a col_name is added?

Thank you

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-01 : 10:46:52

create table test (col_id int identity(20001,1) ...)

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-01 : 10:50:51

Could I create this as a view and then call it at Computed Column Specification?

Also, note I want this to be automated - because I will have hundreds of col_id Thanks

Thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-01 : 12:19:07

Anyone who can help ?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-01 : 12:24:22

quote:
Originally posted by dr223

Anyone who can help ?

Thanks

create the table as I showed you in my previous post

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-02 : 05:38:52

The table is alreday created - I want to populate it with the first column as highlighted. Note: in Blog#1 thats just an example. The REAL table has 10 fields and will be populated with records periodically.

So the Create table is not an option..

Any ideas?

Thanks

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-10-02 : 07:37:21

If you can alter the table you can add an identity and a computed column: Is that an option

CREATE TABLE [dbo].[SillyTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[COL_ID] AS ('2'+right('000'+CONVERT([varchar](4),[ID]),(4))),
[Col_Name] [varchar](15) NULL
) ON [PRIMARY]

GO

INSERT INTO SillyTable VALUES('red'),('yellow'),('green'),('blue'),('orange'),('purple'),('banana'),('apple'),('grape'),('apple'),('pear')

SELECT * FROM SillyTable

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-02 : 09:16:52

Very good approach - but unfortunately this is not a good option for me - the table will be massive with thousands of records populated.

I want it to be more seamless - where as new records are added to the table - the col_uid is automatically added as explained in blog #

So Column I = col_id as sson as a record is added it takes 20001, then 20002, 20003, ..........29999

Thats what I want to achieve.. I dont want to create a table (the table does exist alreday) ... I want to have a code to populate automatically col_uid per record..

Any ideas please..

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-02 : 09:57:31

ALTER TABLE mytable
ADD IdColumn int identity(20001,1)

this will add the new column and update the table, populating the column with new values in the range you ned

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-02 : 10:07:42

Ok - I think am not clear..

I have a column col_id in my table already created - I dont want to create the column.. .I just want to populate it with incremental data in the format xyyyy

where x is always 2 and yyyy start from 0001, 0002...9999

So, for the first record col_id will be 20001, 20002, 2003..... 29999

So, I was thinking of having a formula in the (Computed Column Specification) for the column col_id

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-02 : 10:23:20

Ok, so

ALTER TABLE mytable
ALTER COLUMN IdColumn int identity(20001,1)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-02 : 10:26:15

sorry...that won't work

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-02 : 10:28:48

You'll need to drop the id column and re-add it

alter table mytable
drop column IdColumn
go
alter table mytable
ADD IdColumn int identity(20001,1)

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-02 : 10:43:27

so lets assume there are alreday records existing there with col_ids - and what i want to achieve is a continuation of col_id...

For example... the last col_id for record 99 is 20099.. Then next record should start from 20100... without droping the col_id is it possible?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-02 : 10:48:32

If we're assuming that the existing col_ids are all filled in, try this:

1. create a new table like the current one, but with col_id defined as an identity(20001,1)
2. SET IDENTITY_INSERT newtable ON
3. insert into new table, selecting all values from old table
4. SET IDENTITY_INSERT newtable OFF

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-02 : 11:59:29

2 questions>.

a) what do u mean define col_id as identity(20001,1)
b) if this will be on-going process of adding records on daily basis will the above method not be applicable

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-02 : 12:07:00

a) create table ...(col_id int identity(20001,1) ...)
b) once the identity column is defined, SQL server will do it automagically

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-02 : 12:10:31

Sorry - where should I add

col_id int identity (20001,1) - data type of the column (col_id) ?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-02 : 12:31:41

in the create table statement, as i indicated

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-03 : 05:23:58

Is it possible to do the same when you Right click the table and Go to the Design mode?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-03 : 09:01:43

yes, though fwif i find the create table command eaiser to use.

Note that we are talking about creating a new table with the desired characteristics, then copying the data from the current table to the new table, then using the new table going forward instead of the current table.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources