为SQl表列设置前缀（最好在主键列上） [英] Setting Prefix for SQl table columns (preferably on primary key column)

问题描述

我想设计一个员工表，其中employee_id（主键）以序列E0001，E0002开头， E0003 ...



请帮忙...

解决方案

在这里你去：

 CREATE TABLE [tbl_SR_employeeInfo]（
 [employeeName] [nvarchar]（359），
 [colorHair] [nvarchar]（MAX）
）

 CREATE TABLE [tbl_SR_employeeInfoIdx]（
 [Idx] [int] IDENTITY（1,1），
 [employeeName] [ nvarchar]（359），
 [colorHair] [nvarchar]（MAX）
）

employeeInfo.txt的内容包含制表符分隔的数据文件示例数据：

 originalgriff white 
 ronbeyer brown 
 andrekraak black 
 codehunt red 

 BULK INSERT [tbl_SR_employeeInfo] 
 FROM'C：\USERS\SR\employeeInfo_td.txt'

现在转账第一个表数据到第二个表：

 INSERT INTO [tbl_SR_employeeInfoIdx] 
 SELECT [employeeName]，[colorHair] FROM [tbl_SR_employeeInfo] 

现在，当你去查询后面的表时，使用过滤器为索引字段添加前缀：

 SELECT [Idx]，[employeeName]，[colorHair]，LEFT（REPLICATE（' 0'，6），6-LEN（Idx））+ CAST（[Idx] AS [nvarchar]（12））AS [zeropaddedIdx]，'N'+ LEFT（REPLICATE（'0'，6），6-LEN （Idx））+ CAST（[Idx] AS [nvarchar]（12））AS [concatenationFinal] FROM [cpqa]。[tbl_SR_employeeInfoIdx] 

订单更接近要求

Hi,


I want to design an employee table where the employee_id(primary key) starts with sequence E0001, E0002,E0003...

Please help...

解决方案

Here you go:

CREATE TABLE [tbl_SR_employeeInfo](
     [employeeName][nvarchar](359),
         [colorHair][nvarchar](MAX)
          )

CREATE TABLE [tbl_SR_employeeInfoIdx](
   [Idx][int]IDENTITY(1,1),
     [employeeName][nvarchar](359),
         [colorHair][nvarchar](MAX)
          )

Content of employeeInfo.txt a tab-delimited datafile containing example data:

originalgriff    white
ronbeyer    brown
andrekraak    black
codehunt    red

BULK INSERT [tbl_SR_employeeInfo]
    FROM 'C:\USERS\SR\employeeInfo_td.txt'

Now transfer first table data to second table:

INSERT INTO [tbl_SR_employeeInfoIdx]
   SELECT [employeeName], [colorHair] FROM [tbl_SR_employeeInfo]

Now, when you go to query the later table, prefix the indexed field using a filter:

SELECT [Idx], [employeeName], [colorHair],  LEFT(REPLICATE('0', 6), 6-LEN(Idx)) + CAST([Idx] AS [nvarchar](12)) AS [zeropaddedIdx], 'N'+  LEFT(REPLICATE('0', 6), 6-LEN(Idx)) + CAST([Idx] AS [nvarchar](12)) AS [concatenationFinal] FROM [cpqa].[tbl_SR_employeeInfoIdx]

Order as closer to requirement.

这篇关于为SQl表列设置前缀（最好在主键列上）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！