0

Let's say I have the following table:

ID Name Custom_Field
1  Alex   1_custom
2  Bob    2_somethingelse
.......

Can I do the insert in one sql statement? I couldn't find any function that will allow me to use my ID at the time of insert. ID is INT IDENTITY(1,1) I'm looking for something like this:

insert into table
values('Alex', ID_VALUE + '_custom')
user194076
  • 8,787
  • 23
  • 94
  • 154
  • This might help you https://stackoverflow.com/questions/20805067/how-to-get-the-next-identity-value-from-sql-server – uzi Jan 23 '18 at 05:20

2 Answers2

3

You could create a computed column, if you always require the field values like ID_custom:

CREATE TABLE SomeTable
(
    [id] int identity, 
    [name] varchar(20),
    CustomField AS CAST(id AS varchar(10)) + '_custom'
);

Though you can't insert values into it (and you don't have to). So your insert statements look like this:

INSERT INTO SomeTable
    ([name])
VALUES
    ('Alex'),
    ('Bob')
;

Here's a SQL Fiddle: http://sqlfiddle.com/#!18/10cac/1

Nisarg Shah
  • 14,151
  • 6
  • 34
  • 55
  • How the above resolved the issue ?? Btw. you should mark the computed col. as persisted. – Yogesh Sharma Jan 23 '18 at 05:20
  • @YogeshSharma The question does not present an issue, and I am merely pointing out a way to implement the requirement. Btw, yes the column can be marked as PERSISTED, but I don't think it is ALWAYS required. It depends on the use case. – Nisarg Shah Jan 23 '18 at 05:24
  • The best approach is computed column, I aggree to @Nisarg. Persisted or not. Since Identity column cannot be updated, the value in computed column will always be parallel to identity. – Eralper Jan 23 '18 at 05:26
0

You can get the last identity and increase by 1. Something like this

insert into table
 values('Alex', cast((select max(ID_VALUE )+1 from table) as nvarchar(10)) + '_custom')

There is SCOPE_IDENTITY() function in sql server, but you can use it only after insert statement.

Karen Avdalyan
  • 382
  • 1
  • 20
  • This could run into problems when your sql server [loses the 1000 number cache](https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database) of identity values. – tarheel Jan 24 '18 at 03:12