透视查询的示例数据 [英] Sample Data for Pivot Query
问题描述
如何使用sql profiler测试sql server性能
how to test the sql server performance using sql profiler
推荐答案
这里有几个问题...除了你的帖子由于数量不可读的事实您附带的样本数据 - 相应表格中的137和668条记录 - 您可以重新创建表格中没有数据的错误！
- 使用
There are several problems here... apart from the fact your post is unreadable because of the amount of sample data you included with it - 137 and 668 records on the respective tables - you can recreate the errors with no data in the tables!
- With
ALTER TABLE [dbo].[ResTracker_EmployeeSummary] WITH CHECK ADD CONSTRAINT [FK_ResTracker_EmployeeSummary_Designation] FOREIGN KEY([DesignationID])
REFERENCES [dbo].[ResTracker_DesignationDetails] ([DesignationID])
ON UPDATE CASCADE
ON DELETE CASCADE您将收到错误：
外键'FK_ResTracker_EmployeeSummary_Designation'引用无效表'dbo.ResTracker_DesignationDetails'。
Foreign key 'FK_ResTracker_EmployeeSummary_Designation' references invalid table 'dbo.ResTracker_DesignationDetails'.
您要创建外键的表 - 'dbo.ResTracker_DesignationDetails' - 应该在创建外键之前创建key。
同样适用于以后的尝试
The table to which you are trying to build a foreign key to - 'dbo.ResTracker_DesignationDetails' - should be created before you create the foreign key.
Same applies to a later attempt
外键'FK_Status_Employee '引用无效表'dbo.ResTracker_Staus'。
Foreign key 'FK_Status_Employee' references invalid table 'dbo.ResTracker_Staus'.
你需要创建表'dbo.ResTracker_Staus'。 NB 我怀疑拼写错误可能会导致您以后遇到问题...应该是'状态'吗？
- 你的下一组问题当你试图插入数据时会出现 - 请注意数据实际上与外键约束匹配。
- 同样在你的样本数据中你提供了值对于IDENTITY列 - 没有必要这样做，没有必要继续重复您添加的列列表。例如，以下两种添加行的方法都是有效的
You need to create table 'dbo.ResTracker_Staus'. NB I suspect that spelling mistake may cause you problems later ... should that be 'Status'?
- Your next set of problems will come when you try to insert your data - take care that the data actually matches up with the foreign key constraints.
- Similarly in your sample data you have provided values for an IDENTITY column - there is no need to do this, no is there any need to keep repeating the list of columns that you are adding. For example both of the following methods of adding a row are valid
SET IDENTITY_INSERT [dbo].[ResTracker_TeamDetails] ON
INSERT INTO [dbo].[ResTracker_TeamDetails] ([TeamID], [TeamName], [ParentTeamID], [ProcessOwnerID], [Level], [AboveCOF], [Status], [TeamShortName], [SLAID], [GskgroupID]) VALUES
(0, N'KC', NULL, CAST(145172 AS Numeric(18, 0)), N'1', NULL, 1, N'KC', 2, 24)
,(1, N'BI/BT/CHRD-CSI/MARS/SS/Admin/Support IT', 0, CAST(100543 AS Numeric(18, 0)), N'1', NULL, 1, N'BI/BT/CHRD-CSI/MARS/', 23, 24)或
INSERT INTO [dbo].[ResTracker_TeamDetails] VALUES
(N'KC', NULL, CAST(145172 AS Numeric(18, 0)), N'1', NULL, 1, N'KC', 2, 24)
,(N'BI/BT/CHRD-CSI/MARS/SS/Admin/Support IT', 0, CAST(100543 AS Numeric(18, 0)), N'1', NULL, 1, N'BI/BT/CHRD-CSI/MARS/', 23, 24)
如果你再次遇到这类问题，这里有一些技巧可以帮助你自己。
1.构建你的脚本/查询/分阶段编码。
2.在修复上一阶段的所有错误之前，不要进入下一阶段
3.添加少量数据
在这种情况下，逐个创建表格。添加外键，然后添加数据。
如果遇到
If you come across this type of problem again here are some techniques to help you help yourself.
1. Build your script / query / code up in stages.
2. Do not move onto the next stage until you have fixed all of the errors in the previous stage
3. Add small volumes of data
In this case, create the tables one by one. Add the foreign keys, then add the data.
If you come across
INSERT语句与FOREIGN KEY约束FK_ResTracker_EmployeeSummary_Team
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ResTracker_EmployeeSummary_Team"
然后确保以正确的顺序填充表，并确保您尝试使用的数据引用确实存在。
最后，对你的问题看起来如何思考 - 你想要最大限度地增加准备阅读它的人数，而不是立即让人们离开。带有
then make sure you have populated the tables in the correct order and that the data references you are trying to use actually do exist.
Finally, apply some thought to how your question looked - you want to maximise the number of people who are prepared to read it, not put people off straight away. Surround code snippets with
<pre> ... code here ... </pre>
如果有人在您的帖子上发布问题或评论，那么请礼貌并回复他们...之前你发布了另一个问题。
If someone posts a question or a comment on your post then be polite and respond to them ... before you post another question.
这篇关于透视查询的示例数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
