SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 03/18/2013 :  20:04:55  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 03/18/2013 :  20:26:39  Show Profile  Visit russell's Homepage  Reply with Quote
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
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 03/18/2013 :  21:09:02  Show Profile  Reply with Quote

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

'
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 03/18/2013 :  21:12:35  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 03/18/2013 :  21:13:52  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 03/18/2013 :  21:14:48  Show Profile  Reply with Quote
Thanks

I have only 400 records..
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/19/2013 :  02:34:06  Show Profile  Visit russell's Homepage  Reply with Quote
Well if you're still looking for help, show us what you have so far
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000