Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Increment in sql server. Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, September 03, 2012 11:23 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, January 31, 2013 12:02 PM Points: 5, Visits: 16
 Hi all i need to do increment a number like aa001 to aa999 then again ab001 to ab999aa001a0 to aa999a0 then aa001b0 to aa999b0.Can any one help me out to find logic for this.
Post #1353709
 Posted Tuesday, September 04, 2012 12:10 AM
 Old Hand Group: General Forum Members Last Login: Today @ 4:27 AM Points: 398, Visits: 617
 venki.msg (9/3/2012)Hi all i need to do increment a number like aa001 to aa999 then again ab001 to ab999aa001a0 to aa999a0 then aa001b0 to aa999b0.Can any one help me out to find logic for this.Sorry I am confused with your examples aa001 to aa999 then again ab001 to ab999aa001a0 to aa999a0 then aa001b0 to aa999b0Could you please explain your exact requirement with clear expected output. --rhythmk------------------------------------------------------------------To post your question use below linkhttp://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1353721
 Posted Tuesday, September 04, 2012 3:34 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, January 31, 2013 12:02 PM Points: 5, Visits: 16
 I need logic for generating following pattern.AA001A0 TO AA999A0 THIS IS FIRST SCENARIO.NEXT IS AA001B0 TO AA999B0 IS SECOND SCENARIO.
Post #1353784
 Posted Tuesday, September 04, 2012 4:42 AM
 Old Hand Group: General Forum Members Last Login: Today @ 4:27 AM Points: 398, Visits: 617
 venki.msg (9/4/2012)I need logic for generating following pattern.AA001A0 TO AA999A0 THIS IS FIRST SCENARIO.NEXT IS AA001B0 TO AA999B0 IS SECOND SCENARIO.If you have only these two fix scenario.Then you can try Jeff's Tally table as mentioned below.`--===== Do this in a nice safe place that everyone has -- (You can build a permanent one in any database) USE TempDB; IF OBJECT_ID('TempDB..Tally','U') IS NOT NULL DROP TABLE Tally;GO--===================================================================-- Create a Tally table from 1 to 1000 (you can increase this number as per your requirement)--===================================================================--===== Create and populate the Tally table on the fly. SELECT TOP 1000 IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.sys.ALL_Columns ac1 CROSS JOIN Master.sys.ALL_Columns ac2;--===== Add a CLUSTERED Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100;--===== Allow the general public to use it GRANT SELECT ON dbo.Tally TO PUBLIC;GO------ Now move to your query SELECT 'AA' + RIGHT('00'+ CONVERT(VARCHAR,N),3) + 'A0' AS NUM FROM tempdb.dbo.TallyWHERE N < 1000`Likewise you can try out the second pattern on your own --rhythmk------------------------------------------------------------------To post your question use below linkhttp://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1353800
 Posted Tuesday, September 04, 2012 6:47 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, January 31, 2013 12:02 PM Points: 5, Visits: 16
 thank you
Post #1353857
 Posted Tuesday, September 04, 2012 6:52 AM
 SSChampion Group: General Forum Members Last Login: Today @ 12:01 PM Points: 12,744, Visits: 31,074
 if you can have an identity column in your table, you can create a calculated persisted column that auto generates that texty-like value.`--#################################################################################################--Pattern: AA000 to ZZ999 max value=676000--#################################################################################################IF OBJECT_ID('X') IS NOT NULL DROP TABLE XCREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 676000)), --limit=26 * 26 + 999 + 1XCALCULATED AS CHAR((XID/26000)%26+65) --1st Letter +CHAR((XID/1000)%26+65) --2nd Letter +REPLACE(STR(XID%1000,3),' ','0') PERSISTED, --The 3 digit numeric partSOMEOTHERCOL VARCHAR(30))INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')SET IDENTITY_INSERT X ON INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')SET IDENTITY_INSERT X OFFSET IDENTITY_INSERT X ON INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF') --FAILS! too big!SET IDENTITY_INSERT X OFFSELECT * FROM X--three char table: bigger range--#################################################################################################--Pattern: AAA000 to ZZZ999 max value=196040000--#################################################################################################IF OBJECT_ID('X') IS NOT NULL DROP TABLE XCREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 196040000)),XCALCULATED AS CHAR((XID/260000)%26+65) --1st Letter + CHAR((XID/26000)%26+65) --2nd Letter + CHAR((XID/1000)%26+65) --3rd Letter + REPLACE(STR(XID%10000,4),' ','0') PERSISTED, --The 4 digit numeric partSOMEOTHERCOL VARCHAR(30))INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')SET IDENTITY_INSERT X ON INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')SET IDENTITY_INSERT X OFFSET IDENTITY_INSERT X ON INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF')SET IDENTITY_INSERT X OFFSELECT * FROM XXID XCALCULATED SOMEOTHERCOL 1 AA001 WHATEVER 675999 ZZ999 MORESTUFF ` Lowell--There is no spoon, and there's no default ORDER BY in sql server either.Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1353861
 Posted Wednesday, September 05, 2012 12:05 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, January 31, 2013 12:02 PM Points: 5, Visits: 16
 Thank but only one value inserting in a table it's not incremented.example in table only aa001 only displaying it should be incremented till aa999 na.
Post #1354332
 Posted Thursday, September 06, 2012 11:50 PM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 6:03 PM Points: 3,590, Visits: 5,098
 Perhaps something like this:`DECLARE @t TABLE (ID INT IDENTITY, strcol VARCHAR(20))INSERT INTO @tSELECT 'aa001' UNION ALL SELECT 'ab001'UNION ALL SELECT 'aa001a0' UNION ALL SELECT 'aa001b0';WITH Tally (n) AS ( SELECT TOP 999 RIGHT('00' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR), 3) FROM sys.all_columns)SELECT STUFF(strcol, m, 3, n) FROM @tCROSS APPLY (SELECT CHARINDEX('001', strcol)) a(m)CROSS APPLY TallyORDER BY ID, n` My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!
Post #1355759

 Permissions