Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
I need a query which should auto increment the values from the below value 'X0000005'Ex:'X0000005''X0000006''X0000007''X0000008'..etcThanks 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 datacreate table #t (n char(8));GOinsert #t values ('X0000005')insert #t values ('X0000006')insert #t values ('X0000007')insert #t values ('X0000008')GO-- insert incremented valueBEGIN TRANINSERT #t SELECT 'X' + RIGHT('0000000' + Convert(VARCHAR(7), MAX(Convert(INT, RIGHT(n, 7))) + 1), 7 ) FROM #tCOMMITGO-- show itSELECT * FROM #tGO-- clean upDROP TABLE #tGO
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 outputCol1 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 yourdesttableselect 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 outputCol1 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
ThanksI 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