SQL Server Inserting identity column value as param to other column [duplicate]

nom7f22z 于 9个月前发布在其他

关注(0)|答案(3)|浏览(64)

This question already has answers here:

Inserting concatenate Identity column with other column (2 answers)
Closed 8 days ago.

I have a table named tab_stores, it's structure is shown below

CREATE TABLE tab_stores
(
    id_num INT IDENTITY(1, 1),
    sName VARCHAR(20),
    mySKey VARCHAR(20)
);

Now, when I insert the values in this table using the query below, it inserts values and increments the id_num by +1 which is working fine.

INSERT tab_stores (sName, mySKey)
VALUES ('Store', 'Store');

The data inserted is

1,Store,Store

I want to modify it a bit so that when I insert the row, the auto incremented column value should be post fixed and added in mySKey column, so the result should be

1,Store,Store1

I have tried using the query below, but it does not work - and I get

Msg 207, Level 16, State 1, Server 49706b09f367, Line 7
Invalid column name 'id_num'

INSERT tab_stores (sName, mySKey)
VALUES ('Store', 'Store' + id_num);

Can anyone please help me? Thank you

sql-server

来源：https://stackoverflow.com/questions/77725336/inserting-identity-column-value-as-param-to-other-column

3条答案

按热度按时间

roejwanj1#

If this rule always applies - then you have two options:

Create a view on top of your table that handles this:

CREATE TABLE dbo.tab_stores
(
    id_num INT IDENTITY(1, 1),
    sName VARCHAR(20)
);

CREATE VIEW dbo.view_Stores
AS
    SELECT
        id_num, sName,
        sName + CAST(id_num AS VARCHAR(10)) AS mySKey
    FROM
        dbo.tab_stores;

SELECT *
FROM dbo.view_Stores;

You could also create a computed column in your base table (instead of creating a separate column and filling it):

CREATE TABLE dbo.tab_stores
(
    id_num INT IDENTITY(1, 1),
    sName VARCHAR(20),
    mySKey AS sName + CAST(id_num AS VARCHAR(10)) PERSISTED
);

INSERT INTO dbo.tab_stores (sName) 
VALUES ('Store'), ('StoreABC');

SELECT *
FROM dbo.view_Stores;

1,'Store','Store1'
2,'StoreABC','StoreABC2'

赞(0）回复(0）举报 9个月前

acruukt92#

Get max id_num value from tab_scores and concatenate with "Store" text.
But when table has now rows, max function prints NULL so use isnull function to change 1.

INSERT tab_stores (sName, mySKey)
VALUES ('Store', 'Store' + convert(varchar,(select isnull(max(id_num) + 1, 1) from tab_stores)));

赞(0）回复(0）举报 9个月前

zrfyljdw3#

you can check the below links for getting some idea.

Insert same auto increment value to another column of same table https://www.sqlservercentral.com/forums/topic/use-generated-identity-for-another-column-within-the-same-insert

Thanks.

赞(0）回复(0）举报 9个月前

我来回答

SQL Server Inserting identity column value as param to other column [duplicate]

3条答案

相关问题

热门标签

最新问答