如何在不应用循环的情况下将数据插入到表中。？ [英] How do I insert data into my table without applying the loop.?

问题描述

我想在我的表中插入数据，但问题是返回多个值的用户ID coloumn。所以在这种情况下我能做些什么。



我尝试过的事情：

I want to insert data into my table but the problem is the user id coloumn which is returning multiple values. so what can i do in that case.

What I have tried:

Insert into UserAccess(ClientId,UserId,ParentId,Name,ToolTip,NavigateUrl,UserRole,IsParent,Menu_Order,Status,MenuId) SELECT @ClientId ,(select USR_ID 
from UserDetail WHERE Client_Id=@ClientId AND (',' + USR_Role + ',') LIKE '%,2,%' AND Status=1 AND Emp_Id!=0), ParentId,Name,ToolTip,NavigateUrl,UserRole,IsParent,Menu_Order,Status,Id from UserMenu WHERE UserRole=2 AND Status=1

问题是usr_Id coloumn因为起初我们只选择userid = 2的前1位用户，但现在我们的要求发生了变化，因此我们必须为每个用户ID = 2的用户插入数据。我怎样才能实现这一目标？

Problem is the usr_Id coloumn because at first we only select the top 1 user where userid =2 but now our requirments changes so we have to insert the data for every user with userid=2. How can i acheive this?

推荐答案

您没有提供3天前请求的其他信息，但我会尝试演示解决方案。



我用这个脚本创建了一些演示表

You haven't provided the additional information that was requested 3 days ago but I will attempt to demonstrate the solution.

I created some demo tables with this script

create table #user
(
	id int identity(1,1),
	dataitem varchar(10),
	[status] int
)
insert into #user (dataitem, [status]) values
('User1',1), ('User2',1), ('User3',1)

create table #usermenu
(
	id int,
	menudetail varchar(10),
	[user_id] int
)
insert into #usermenu (id, menudetail, [user_id]) values
(1, 'Menu 1', 1), (1, 'Menu 1', 2), (1, 'Menu 1', 3)

所以现在我有3个用户，都处于状态1，并且都可以访问菜单1.如果我尝试这样的查询样式

So now I have 3 users, all at status 1 and all have access to Menu 1. If I attempt your style of query like this

SELECT (select id from #user where status = 1), id, menudetail from #usermenu

我收到错误

Q uote：

Msg 512，Level 16，State 1，Line 25

子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。

Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

您需要能够以某种方式加入表。在我的例子中，我将使用

You need to be able to join the tables in some way. In my example I would use

select a.id, b.id, menudetail
from #usermenu B
INNER JOIN #user A on A.ID=B.[user_id]

您的代码有几个方面与我有关。

1.代码段 LIKE'％，2，％'表示您将用户角色存储在以逗号分隔的字符串中。这是非常糟糕的设计。相反，您应该将这些角色存储在另一个表中 - 这样您就不必混淆分离字符串或搜索它以查看哪些角色可用 - 它在其他地方进行了详细讨论。例如。

There are a couple of aspects of your code that concern me.
1. The snippet LIKE '%,2,%' implies that you are storing user roles in a comma-separated string. This is very bad design. Instead you should store those roles in another table - that way you don't have to mess around with separating the string or searching it to see which roles are available - it's discussed at length elsewhere. E.g.

create table #UserRoles
(
	[User_Id] int,
	roleNo int
)

我可能不会打扰单独的角色表，因为它会使标准化太过分了。

2.你有一个名为UserMenu的表，但它似乎包含所有的菜单信息。我会有一个表菜单，其中包含所有数据和一个单独的表，只是将User_Ids链接到他们有权访问的菜单。

I probably wouldn't bother having a separate roles table as that would be taking normalisation a tad too far.
2. You have a table called UserMenu but it appears to have all of the Menu information in it. I would have a table Menu that contains all that data and a separate table that just links User_Ids to the Menu's to which they have access.

这篇关于如何在不应用循环的情况下将数据插入到表中。？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！