MSSQL - 当列名动态时显示数据

发布于 2024-07-16 10:09:06 字数 1167 浏览 6 评论 0原文

我正在向员工提交最终的权威评估分数。每行都是员工的数据，并且由于要评估的类别可能会随时间段的不同而变化，因此列名称不能在存储过程中进行硬编码。我已经设计了以下解决方案。

1 创建临时表
2 动态使用Alter Table命令添加所有适用的列（存储在@ColumnNames中）
3 在游标内使用动态 SQL 为每个获得正确分数的员工编写一条插入（IE N 员工意味着 N 次插入）

(SELECT @ECMScores = COALESCE(@ECMScores + ',', '') + CAST(EIS.ECMScore AS NVARCHAR(1000)) (FROM...))

SET @SQLString = ''

SET @SQLString = @SQLString + 'INSERT INTO      #ResultSet ('
SET @SQLString = @SQLString + 'EvaluationScoreID,'
SET @SQLString = @SQLString + 'EmployeeID,'
SET @SQLString = @SQLString + 'EmployeeName,'
SET @SQLString = @SQLString + @ColumnNames
SET @SQLString = @SQLString + ') '


SET @SQLString = @SQLString + 'VALUES ('
SET @SQLString = @SQLString + ''+CAST(@EvaluationScoreID AS NVARCHAR(MAX))+','
SET @SQLString = @SQLString + ''+CAST(@EmployeeID AS NVARCHAR(MAX))+','
SET @SQLString = @SQLString + '"'+@EmployeeName+'",'
SET @SQLString = @SQLString + @ECMScores
SET @SQLString = @SQLString + ')'

EXECUTE sp_executesql   @SQLString

问题是每 100 名员工大约需要 1 秒。这很快就变得不可接受……

有人对如何进行有更好的想法吗？删除光标（显然），并使用一个插入（也许选择进入）是我的第一个想法，也许是从动态创建的 XML 变量中读取......

谢谢，

原文

I am presenting to a final authority evaluation scores for employees. Each row is an employee’s data and since the categories to be evaluated can change from period to period the column names cannot be hardcoded in the Stored Procedures. I have already devised the following solution.

1 Create a temp table
2 Dynamically use the Alter Table command to add all applicable columns (Stored in @ColumnNames)
3 Use Dynamic SQL inside a cursor to write an insert for each employee that gets the correct scores (IE N employees means N inserts)

(SELECT @ECMScores = COALESCE(@ECMScores + ',', '') + CAST(EIS.ECMScore AS NVARCHAR(1000)) (FROM...))

SET @SQLString = ''

SET @SQLString = @SQLString + 'INSERT INTO      #ResultSet ('
SET @SQLString = @SQLString + 'EvaluationScoreID,'
SET @SQLString = @SQLString + 'EmployeeID,'
SET @SQLString = @SQLString + 'EmployeeName,'
SET @SQLString = @SQLString + @ColumnNames
SET @SQLString = @SQLString + ') '


SET @SQLString = @SQLString + 'VALUES ('
SET @SQLString = @SQLString + ''+CAST(@EvaluationScoreID AS NVARCHAR(MAX))+','
SET @SQLString = @SQLString + ''+CAST(@EmployeeID AS NVARCHAR(MAX))+','
SET @SQLString = @SQLString + '"'+@EmployeeName+'",'
SET @SQLString = @SQLString + @ECMScores
SET @SQLString = @SQLString + ')'

EXECUTE sp_executesql   @SQLString

The problem is it takes approx 1 second for every 100 employees. This quickly becomes unacceptable…

Does anyone have any better ideas on how to proceed? Removing the cursor (obviously), and using one insert (Perhaps Select into) is my first idea perhaps reading from a dynamically created XML variable…

Thanks,

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

來不及說愛妳 2024-07-23 10:09:06

不要在架构中存储数据，

创建这样的表：

Assignment
AssignmentID   int not null primary key identity(1,1)
AssignmentName varchar(50)  not null
AssignmentDate datetime not null
etc..

Score
ScoreID       int not null primary key identity(1,1)
EmployeeID    int not null
AssignmentID  int not null
ScoreValue    int not null

Employee
EmployeeID    int not null primary key identity(1,1)
EmployeeName  varhar(100) not null
etc..

您现在可以创建“作业”并插入“分数”，而无需构建动态查询，因为您的列总是会发生变化。您的列现在将保持不变，并且数据将发生变化。

don't store data in the schema

create tables like this:

Assignment
AssignmentID   int not null primary key identity(1,1)
AssignmentName varchar(50)  not null
AssignmentDate datetime not null
etc..

Score
ScoreID       int not null primary key identity(1,1)
EmployeeID    int not null
AssignmentID  int not null
ScoreValue    int not null

Employee
EmployeeID    int not null primary key identity(1,1)
EmployeeName  varhar(100) not null
etc..

you can now create "assignments" and insert "scores" without building dynamic queries because your columns always change. your columns will now stay the same and the data will change.

回复收藏 0 原文