SQLTeam.com | Weblogs | Forums

Sql Insert query auto sequence with prefixed

I try to design a insert query, when user inserted the employee data it will generate auto sequence of employee id with the prefix of username and the sequence.
[My table has 3 columns Emp_Id,Name,Age]

For Example if a user (Red - his username) adds an employee data, then Emp_Id for that employee can be Red001. If he adds more then it takes that sequence such as Red002,Red003 likewise..

The same for another user Green001,Green002 like wise...

My Code is

 if(isset($_POST['Submit']))
    {
         $eid = $username;
         $name = $_POST['inputname'];
         $age = $_POST['inputage'];


         $sql = "INSERT INTO demo (Emp_Id, FirstName, Age) VALUES ('$eid', '$name', '$age')";
  if(mysqli_query($con, $sql))
   {
        echo "Records added successfully.";
    } 

else{
        echo "ERROR: Could not able to execute $sql. " . mysqli_error($con);
    }
 }

one way to do this !!!

when inserting

the table should have an identity column which is generated automatically
insert table select username , emp_id , username+emp_id+ lag(identity_column) + 1
here lag is previous identity column value

please let me know if you understand this !!!!
or
i can explain with Diagrams and data
:slight_smile: :slight_smile:
hope this helps

I am not convinced about the design.
The following may help:

USE tempdb;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TABLE dbo.temp
(
	emp_id varchar(20) NOT NULL PRIMARY KEY
	,username varchar(20) NOT NULL
	,dob date NOT NULL
);
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE VIEW dbo.vtemp
AS
SELECT emp_id, username, dob
FROM dbo.temp;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER dbo.TR_vtempI
ON dbo.vtemp
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON;

	WITH OffSet
	AS
	(
		SELECT *
			,ROW_NUMBER() OVER (PARTITION BY username ORDER BY (SELECT NULL)) AS rn
		FROM inserted
	)
	,MaxIds
	AS
	(
		SELECT T1.username, CAST(RIGHT(MAX(T1.emp_id), 3) AS int) AS MaxId
		FROM dbo.temp T1 WITH (UPDLOCK, SERIALIZABLE)
		WHERE EXISTS
		(
			SELECT 1
			FROM inserted I1
			WHERE I1.username = T1.username
		)
		GROUP BY T1.username
	)
	INSERT INTO dbo.temp (emp_id, username, dob)
	SELECT I.username + RIGHT('00' + CAST(COALESCE(M.MaxId, 0) + I.rn AS varchar(3)), 3)
		,I.username, I.dob
	FROM OffSet I
		LEFT JOIN MaxIds M
			ON I.username = M.username;
END;
GO

INSERT INTO dbo.vtemp (username, dob)
VALUES('Red', '19800101');

select * from dbo.temp
GO
INSERT INTO dbo.vtemp (username, dob)
VALUES('Red', '19800101')
	,('Red', '19800101')
	,('Green', '19800102')
	,('Green', '19800102')
	,('Green', '19800102');

select * from dbo.temp
GO
DROP TRIGGER dbo.TR_vtempI;
DROP VIEW dbo.vtemp;
DROP TABLE dbo.temp;
GO

why do you want to do this? what do you gain by doing this? What are you trying to solve by doing this?

But , how will the identity column knows the sequence? Sorry.. I think , I didn't get you

Hi

What I am saying is

Another idea ...

Table ABC

Has

Columns

IdentityColumn....name ..

When you insert names James and John

IdentityColumn. name

  1.                           James
    
  2.                           John
    

Then you can derive the column you want

Select

IdentityColumn,

name ,

Name + IdentityColumn

as derivedColumn

From table abc

Now you get

IdentityColumn. ..name ...derivedColumn

  1.                        James.    James1
    
  2.                        John.       John2

I would recommend changing the table so you can include a simple column for the sequence number - and then use SQL Server's built-in SEQUENCE functionality to generate the appropriate sequence number.

https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15

1 Like