英文:
Create unique identifier column in SQL Server
问题
You can achieve this by using the CONCAT function in SQL. Here's the SQL code to update your Unique_ID to have the prefix 04cust_:
UPDATE [dbo].[my_table]
SET [Unique_ID] = CONCAT('04cust_', [Unique_ID])
WHERE [Unique_ID] = 45;
This code will update the Unique_ID from 45 to '04cust_45' for the table 'april_customers'.
英文:
CREATE TABLE [dbo].[my_table]
(
[V01_V] [real] NULL,
[V02_V] [real] NULL,
[V03_V] [real] NULL,
[V04_V] [real] NULL,
[V05_V] [real] NULL
[Date_Inserted] [datetime] NOT NULL,
[Unique_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_row_id]
PRIMARY KEY CLUSTERED ([Unique_ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I currently have Unique_ID which just captures the row number in the table. I am trying to add table information to these records, so it's easier to identify the table they are coming from.
- Table name:
april_customers - Current Unique_ID: 45
- Goal for Unique_ID: 04cust_45
How do I add 04cust_ to my current Unique_ID?
答案1
得分: 1
你需要添加一个计算列来生成这样的ID
CREATE TABLE [dbo].[my_table](
[V01_V] [real] NULL,
[V02_V] [real] NULL,
[V03_V] [real] NULL,
[V04_V] [real] NULL,
[V05_V] [real] NULL,
[Date_Inserted] [datetime] NOT NULL,
[Unique_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_row_id] PRIMARY KEY CLUSTERED
(
[Unique_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[my_table] values (1,1,1,1,1, CURRENT_TIMESTAMP ),(2,2,2,2,2, CURRENT_TIMESTAMP )
ALTER TABLE dbo.my_table ADD new_id AS ('04cust_' + CONVERT(varchar(16),Unique_ID));
SELECT * FROM [dbo].[my_table]
| V01_V | V02_V | V03_V | V04_V | V05_V | Date_Inserted | Unique_ID | new_id |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 1 | 2023-03-20 22:37:27.493 | 1 | 04cust_1 |
| 2 | 2 | 2 | 2 | 2 | 2023-03-20 22:37:27.493 | 2 | 04cust_2 |
当然，如果将来遇到类似的问题，你也可以添加一个UUID。
英文:
You would add a computed column to generate such an id
CREATE TABLE [dbo].[my_table](
[V01_V] [real] NULL,
[V02_V] [real] NULL,
[V03_V] [real] NULL,
[V04_V] [real] NULL,
[V05_V] [real] NULL,
[Date_Inserted] [datetime] NOT NULL,
[Unique_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_row_id] PRIMARY KEY CLUSTERED
(
[Unique_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[my_table] values (1,1,1,1,1, CURRENT_TIMESTAMP ),(2,2,2,2,2, CURRENT_TIMESTAMP )
ALTER TABLE dbo.my_table ADD new_id AS ('04cust_' + CONVERT(varchar(16),Unique_ID));
SELECT * FROM [dbo].[my_table]
| V01_V | V02_V | V03_V | V04_V | V05_V | Date_Inserted | Unique_ID | new_id |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 1 | 2023-03-20 22:37:27.493 | 1 | 04cust_1 |
| 2 | 2 | 2 | 2 | 2 | 2023-03-20 22:37:27.493 | 2 | 04cust_2 |
Of course you could add a uuid also, if you such problems in future
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论