Auto generate - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Auto generate

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-03-18 : 10:02:17

i want to auto generate ID'S in sqlserver with select scripts i want to add numbers with no duplication like 1000000000,1000000002,1000000003............
I want to generate upto 100,000 records from 1000000000.

Please suggest..

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-18 : 10:04:02

look for identity function and property.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-18 : 10:04:14

check this link
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-18 : 10:07:30

Do you want to insert or just need to select ?

Karthik

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-03-18 : 10:09:12

I want a select not insert..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-18 : 10:18:51

[code]
just sample example
DECLARE @refNo VARCHAR(50),
@referencenumber VARCHAR(32)

SELECT @ReferenceNumber = ''

SELECT @ReferenceNumber = MAX(empid)
FROM emptable

PRINT @ReferenceNumber

IF ISNULL(@ReferenceNumber,'') = ''
SELECT @refno = 'TR-'+ '01'
ELSE
SELECT @refno = 'TR-'+ RIGHT('00000' + CAST(CAST(RIGHT(@ReferenceNumber, 4) AS INT) + 1 AS VARCHAR(6)), 6)

SELECT @referencenumber = @refno
SELECT @referencenumber
[/code]

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-03-18 : 10:25:23

Thanks for the query.
The ID is Int...
I need a simple query select which generate a

SELECT ID= IDENTITY(int, 100000,1) from tablename

start range 100000
Increment with 1
end upto 200000

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-18 : 10:37:17

Are u expecting this,?

declare @a table (id int,ssn varchar(100))

insert into @a values(1,'123-23-1234')
insert into @a values(2,'12323-1234')
insert into @a values(3,'123-2-1234')
insert into @a values(4,'123-23-14')
insert into @a values(5,'3-231234')

select row_number() over(order by id)+100000 from @a

Karthik

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-03-18 : 10:48:56

Thanks for the query.That's what i m looking..

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-18 : 10:52:12

welcome.

Karthik

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-03-18 : 11:47:53

[code]

I have 250 records in the table if i use below query i can generate the 500 records is there a way where i can generate 50,000 records with the scripts..

SELECT
ID,
Tname,
ROW_NUMBER () OVER ( ORDER BY ID)+1000000000 AS ID
from Tablename

UNION ALL

SELECT
ID,
Tname,
ROW_NUMBER () OVER ( ORDER BY ID)+1000000251 AS ID
from Tablename

Please suggest.

Thanks for your help in advance..
[/code]

obiron
Starting Member

23 Posts

Posted - 2009-03-18 : 12:05:00

quote:
Originally posted by karthik_padbanaban

Are u expecting this,?

declare @a table (id int,ssn varchar(100))

insert into @a values(1,'123-23-1234')
insert into @a values(2,'12323-1234')
insert into @a values(3,'123-2-1234')
insert into @a values(4,'123-23-14')
insert into @a values(5,'3-231234')

select row_number() over(order by id)+100000 from @a

Karthik

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-03-18 : 12:16:24

i got the expected from karthik query.
but i have different results needed..



insert into @a values(1,'123-23-1234')
insert into @a values(2,'12323-1234')
insert into @a values(3,'123-2-1234')
insert into @a values(4,'123-23-14')
insert into @a values(5,'3-231234')

select row_number() over(order by id)+100000 from @a

Output with above query:

100001
100002
100003
100004
100005

But i need a below output upto 100500 but i have a only 5 records in a table:

100001
100002
100003
100004
100005
100006
100007
100008
100009
100010
.
.
.
.
.
100500

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-03-18 : 12:42:37

My goal is to generate the test data....

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-18 : 23:27:01

try this once
declare @a table (id int,ssn varchar(100))

insert into @a values(1,'123-23-1234')
insert into @a values(2,'12323-1234')
insert into @a values(3,'123-2-1234')
insert into @a values(4,'123-23-14')
insert into @a values(5,'3-231234')

select number +100001
from
@a t
inner join
master..spt_values as m ON type ='p'
group by number
having count(id)*100 >number

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources