SQL Insert using MAX

[Developer2007]

To all the SQL junkies out there,

I am trying to insert a new record with a primary key like this "A0024". Is there a way I can increment on insert using a sql statement?

Also another problem i'm having is trying to add a new record using the max command?

I have tried:
"INSERT INTO TableName VALUES(MAX(FieldName)+1)"

And also tried:
"INSERT INTO TableName VALUES((SELECT MAX(FieldName)+1 FROM TableName))"


Thanks in advance.

[anhn]

This may help:

INSERT INTO TableName ( A_ID ) 
SELECT Format$(Max(CLng(Mid$(A_ID,2)))+1,"\A0000") FROM TableName

[Developer2007]

This may help:

INSERT INTO TableName ( A_ID ) 
SELECT Format$(Max(CLng(Mid$(A_ID,2)))+1,"\A0000") FROM TableName

I tried this but I got errors. Mid$ wasn't a valid function. I changed the Mid$ command to substring() and then it said CLng isn't a function and then Format$ is n't a valid function.

The database is SQL Server2005, if that helps.

[anhn]

Oh! That is the code in Access.
You can replace all functions Format(), CLng() and Mid() with equivalent functions in SQL Server2005.

[Developer2007]

I'm still having a little trouble getting this to work. Details down below, any help appreciated.

This is the line i'm using:

INSERT INTO tblTest (testid) SELECT convert(varchar(5), Max(substring(testid,2,len(testid)))+1,'\A0000') FROM tblTest


This is the error message:

System.Data.SqlClient.SqlException: Argument data type varchar is invalid for argument 3 of convert function. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at TestApplication.DataService.ExecuteNonQuery(String Sql) in C:\Documents and Settings\HJohs\My Documents\Visual Studio 2008\Projects\TestApplication\TestApplication\ClassLibrary\DataService.vb:line 51

[anhn]

I don't familiar with those syntax, but the original SELECT can be re-written as:

SELECT "A" & Format(Max(CLng(Mid(A_ID,2)))+1,"0000") From TableName

[si_the_geek]

Here's the SQL Server equivalent (untested):

INSERT INTO tblTest (testid) 

SELECT 'A' + Right('0000' + convert(varchar(5), Max(substring(testid,2,len(testid)))+1), 4) 
FROM tblTest

..but there is a very important pair of questions to ask - why have you decided to use an almost numeric field that includes a leading character, rather than a plain numeric field? (you can easily format it in your front end, or by using a View/UDF in the database).

Why are you creating your own next-free-number method, rather than using the built-in version (Identity) which has been designed to solve multiple issues with this kind of situation?

[Developer2007]

The only reason I am using a non numeric indentifier is because the person wants to keep the same numbering system. I have tried to convince them to change over but no luck with that so far.

by the way. it goes from A0001 to Z9999.

[anhn]

by the way. it goes from A0001 to Z9999.

This is more problem. The suggestion above won't work for this as the TEXT part is not fixed.
The next ID of A9999 is B0000.
The best way is to separate the ID field into 2 parts: The Prefix (from "A" to "Z") and the Numeric (from 0000 to 9999). For output you always can combine them.

Below is my new suggestion, but need someone to convert it to SQL Server syntax:

INSERT INTO TableName (ID)
SELECT Chr(Int(A9/10000)) & Right(CStr(A9),4) FROM
(SELECT Asc(Left(Max(ID),1))*10000 + CLng(Mid(Max(ID),2,4))) + 1 AS A9 From TableName)

[kevchadders]

Right, i've seen a few of these over my time here so it though it would give you a quick way to do it using a Computed Column shown in red.

You should be able to copy and run this Query. (Created with SQL Server 2005)

What i did was the following:

• Drop the test table if it exists, then created a test table called ComputeKey
• Declare some temp variables
• Set the @maxSeedVal (for you this would be 1000)
• Set the current count to 0
• Declare a simple count loop to replicate multiple inserts
• Get the lastest letter and lastest seedCount from the table
• If there is no current count then go to the bottom of the code to insert the first record
• If we have reached the maxSeedVal then reset the count back to 0 and increment the letter by 1 using the ASCII code
• Insert the test data into the database
• If its the first time we are entering data enter the default count of 1, with the letter 'A'
• Finally, increment the counter

-- Drop Table
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ComputeKey]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[ComputeKey]

-- Create table
CREATE TABLE [dbo].[ComputeKey]
(
   [pk] [int] IDENTITY(1,1) NOT NULL,
   [seedCount] INT,
   [letter] CHAR,
   [UniqueID] AS ([letter]+ RIGHT('0000' + CONVERT([varchar](40),[seedCount],0),4)),
   [TestData] varchar(50) 
) ON [PRIMARY]

-- Test to see if we need to chance the letter
DECLARE @seed AS INT 
DECLARE @letter AS CHAR
DECLARE @currentcount AS INT
DECLARE @maxSeedVal AS INT
SET @maxSeedVal = 4
SET @currentcount = 0

-- Set the counter to check
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 20
BEGIN

   -- Get the current seed value
   SET @letter = (SELECT MAX(letter) FROM [dbo].[ComputeKey]
      WHERE pk = (SELECT MAX(pk) FROM [dbo].[ComputeKey]))
   SET @currentcount = (SELECT seedCount FROM [dbo].[ComputeKey]
      WHERE pk = (SELECT MAX(pk) FROM [dbo].[ComputeKey]))

   -- If there is already data
   IF @currentcount IS NOT NULL 
   BEGIN

      -- Check to see if the seedvalue has reached the max
      IF @currentcount = @maxSeedVal
      BEGIN
         SET @currentcount = 0
         SET @letter = CHAR(ASCII(@letter) + 1)
      END
	
      -- Insert Test Data
      INSERT INTO [ComputeKey](seedCount, letter, TestData) values (@currentcount+1, @letter, 'Testy')
  
   END
   ELSE -- First time data is entered
   BEGIN
	
      -- Insert Test Data
      INSERT INTO [ComputeKey](seedCount, letter, TestData) values (1, 'A', 'TestyFirst')

   END

   -- Increment the counter
   SET @counter = @counter + 1

END

-- Select the data
select * from [ComputeKey]

[Developer2007]

Thanks to Anhn, KevChadders and si the geek,

I used a combination of both your solutions and it is now working. Always good learning something new.


Cheers
Developer