I'm really sorry if you don't understand my question, my english is not good . kindly correct it if can

I create a table called 'Class'

create table class(
    candidate_id int identity,
    candidate_name varchar(50),
    candidate_course varchar(15) default 'SQL Server');

I just want to insert only candidate_name column

  Insert into class(Candidate_name)
   values('User');

If I execute this command i'll get

Candidate_id	Candidate_name	Candidate_course
1	user	SQL Server

As, identity column will generate no.s so is it possible combine both with candidate_name while inserting .

Just like other languages Print("Hello world " S) where s='Stack' output would be

Hello world Stack

Expecting output like this

Candidate_id	Candidate_name	Candidate_course
1	user1	SQL Server
2	user2	SQL Server
3	user3	SQL Server
nth	user nth	SQL Server

kindly help . Hope i'm clear

CodePudding user response：

You can concatenate those 2 values simply using ' '. Also, we need to convert the candidate_id to varchar, since it is integer.

Query

select candidate_id,
Candiate_name   cast(candidate_id as varchar(10)) as candidate_name,
Candidate_course
From class;

CodePudding user response：

It is not possible to concatenate an identity with a user defined value provided in a raw insert statement. That being said there are a couple of things that you can do.

Define the table with a generated column.

You can actually define a column in your table that is generated from other columns.

create table class(
    candidate_id int identity,
    candidate_name varchar(50),
    candidate_course varchar(15) default 'SQL Server',
    candidate_name_id AS CONCAT(candidate_name, '_', candidate_id)
    );



Insert into class(Candidate_name)
   VALUES
   ('User'),
   ('User'),
   ('User');


SELECT *
FROM dbo.class AS c

This produces the following table.

candidate_id	candidate_name	candidate_course	candidate_name_id
1	User	SQL Server	User_1
2	User	SQL Server	User_2
3	User	SQL Server	User_3

For further reference on 'Computed Columns' https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver16

Define The Table With A Sequence

Instead of using an identity you can use a sequence which gives you a lot more flexibility. You can generate the next value for the sequence and use it in your insert statements. I actually recommend this since you can generate the sequence before hand and use it in other parts of your code.

CREATE SEQUENCE SQ_class INCREMENT BY 1 START WITH 1 AS INT;

create table class(
    candidate_id int DEFAULT NEXT VALUE FOR SQ_class,
    candidate_name varchar(50),
    candidate_course varchar(15) default 'SQL Server'
    );



Insert into class(candidate_id, Candidate_name)
   VALUES
   (NEXT VALUE FOR SQ_class, CONCAT('User', '_', NEXT VALUE FOR SQ_class)),
   (NEXT VALUE FOR SQ_class, CONCAT('User', '_', NEXT VALUE FOR SQ_class)),
   (NEXT VALUE FOR SQ_class, CONCAT('User', '_', NEXT VALUE FOR SQ_class));


SELECT *
FROM dbo.class AS c

Which gives you the following result

candidate_id	candidate_name	candidate_course
1	User_1	SQL Server
2	User_2	SQL Server
3	User_3	SQL Server

CodePudding user response：

in the same column you can achieve that, and as the other answer shows you can generate it on every sele as it is redundat, to have another column.

But you can make a generated column

MS SQL Server 2017 Schema Setup:

create table class(
    candidate_id int identity,
    candidate_name varchar(50),
    fnew_candidate AS (candidate_name   cast(candidate_id as varchar(10))) PERSISTED,
    candidate_course varchar(15) default 'SQL Server');


  Insert into class(Candidate_name)
   values('User');

SQL Fiddle

Query 1:

SELECT * FROM class

Results:

| candidate_id | candidate_name | fnew_candidate | candidate_course |
|--------------|----------------|----------------|------------------|
|            1 |           User |          User1 |       SQL Server |

CodePudding user response：

You may use a Trigger to update the inserted candidate_name automatically as the following:

CREATE TRIGGER ClassInsert ON Class
    FOR INSERT AS 
    Begin
        Update Class Set candidate_name = (candidate_name   Cast(candidate_id as nvarchar(5)))
        Where candidate_id = (Select Max(candidate_id) From Class);
    End

See a demo from db<>fiddle.