query help - 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 2008 Forums

Transact-SQL (2008)

query help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-03-18 : 20:04:55

I need a query which should auto increment the values from the below value 'X0000005'

Ex:
'X0000005'
'X0000006'
'X0000007'
'X0000008'
..etc
Thanks for your help in advance

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-18 : 20:26:39

lots of ways to do this. here's one


-- set up test data
create table #t (n char(8));
GO
insert #t values ('X0000005')
insert #t values ('X0000006')
insert #t values ('X0000007')
insert #t values ('X0000008')
GO

-- insert incremented value
BEGIN TRAN
INSERT	#t
	SELECT	'X' +
		RIGHT('0000000' +
			Convert(VARCHAR(7), MAX(Convert(INT, RIGHT(n, 7))) + 1), 7
		)
	FROM	#t
COMMIT
GO
-- show it
SELECT * FROM #t
GO

-- clean up
DROP TABLE #t
GO

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-03-18 : 21:09:02

[code]
Thanks a lot for the immediate response..

I need a query to autoincrement the values based on the corresponding rowcount..

Ex:

Col1
----
Test1
Best2
Crest3
Rust4

Expected output

Col1 col2
---- -------
Test1 'X0000005'
Best2 'X0000006'
Crest3 'X0000007'
Rust4 'X0000008

[/code]'

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-03-18 : 21:12:35

another way would be to create a table that is prepopulated with millions of rows, in your case 1 million cap. then do a
insert into yourdesttable
select incrementvalue from prepop where incrementvalue not in (select incrementvalue from yourdesttable) or some other query left join or not exists etc

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-03-18 : 21:13:52

is this a one time thing or continous process

quote:

Originally posted by sqlfresher2k7


Thanks a lot for the immediate response..

I need a query to autoincrement the values based on the corresponding rowcount..

Ex:

Col1   
----   
Test1   
Best2   
Crest3  
Rust4   

Expected output

Col1     col2
----   -------
Test1   'X0000005'
Best2   'X0000006'
Crest3  'X0000007'
Rust4   'X0000008

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-03-18 : 21:14:48

Thanks

I have only 400 records..

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-19 : 02:34:06

Well if you're still looking for help, show us what you have so far

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources