An oldie but a goodie (from a previous post back in the days of CPQA yore):
CREATE TABLE [cpqa].[tbl_SR_employeeInfo](
[employeeName][nvarchar](359),
[colorHair][nvarchar](MAX)
)
CREATE TABLE [cpqa].[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 [cpqa].[tbl_SR_employeeInfo]
FROM 'C:\USERS\Administrator\desktop\employeeInfo_td.txt'
Now transfer first table data to second table:
INSERT INTO [cpqa].[tbl_SR_employeeInfoIdx]
SELECT [employeeName], [colorHair] FROM [cpqa].[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]
Output:
Idx employeeName colorHair zeropaddedIdx concatenationFinal
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 originalgriff white 000001 N000001
2 ronbeyer brown 000002 N000002
3 andrekraak black 000003 N000003
4 codehunt red 000004 N000004
As I recall, I unceremoniously made no comment on being given points for posting a good answer. So to those who applauded then, Thank-you.
Submit an article or tip