三个表用户表:bf_user
CREATE TABLE [dbo].[BF_User](
[cUser_Id] [nvarchar](20) NOT NULL,
[cUser_Name] [nvarchar](100) NULL,
[cPassword] [nvarchar](255) NULL,
。)
用户权限表:BF_HoldAuthCREATE TABLE [dbo].[BF_HoldAuth](
[hoid] [int] IDENTITY(1,1) NOT NULL,
[cacc_id] [varchar](50) NULL,
[iyear] [varchar](50) NULL,
[cauth_id] [varchar](50) NULL,
[cUser_Id] [nvarchar](20) NULL,
[iIsUser] [bit] NULL,
[csub_id] [varchar](50) NULL,
。。)用户组表:BF_HoldBcgrp
CREATE TABLE [dbo].[BF_HoldBcgrp](
[hbid] [int] IDENTITY(1,1) NOT NULL,
[cacc_id] [varchar](50) NULL,
[iyear] [varchar](50) NULL,
[BcGroup] [varchar](50) NULL,
[cUser_Id] [nvarchar](20) NULL,
。。)问题1. 向用户组表插入用户组权限
INSERT INTO BF_HoldBcgrp (bcgroup, cuser_id) VALUES ('A','35101'); 因为表【bf_user】中没有列“bcgroup”,所以如何根据查询表【bf_user】得出用户ID,批量插入。
列“bcgroup” 插入的是常量问题2. 插入用户权限
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF010401','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF010402','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020301','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020303','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020304','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020306','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020307','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020308','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020309','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF0202010201','35104','BF');
如何根据查询出查询表【bf_user】得出用户ID,批量插入用户权限。
[cauth_id],[csub_id]插入的值为常量。 请求解答!
CREATE TABLE [dbo].[BF_User](
[cUser_Id] [nvarchar](20) NOT NULL,
[cUser_Name] [nvarchar](100) NULL,
[cPassword] [nvarchar](255) NULL,
。)
用户权限表:BF_HoldAuthCREATE TABLE [dbo].[BF_HoldAuth](
[hoid] [int] IDENTITY(1,1) NOT NULL,
[cacc_id] [varchar](50) NULL,
[iyear] [varchar](50) NULL,
[cauth_id] [varchar](50) NULL,
[cUser_Id] [nvarchar](20) NULL,
[iIsUser] [bit] NULL,
[csub_id] [varchar](50) NULL,
。。)用户组表:BF_HoldBcgrp
CREATE TABLE [dbo].[BF_HoldBcgrp](
[hbid] [int] IDENTITY(1,1) NOT NULL,
[cacc_id] [varchar](50) NULL,
[iyear] [varchar](50) NULL,
[BcGroup] [varchar](50) NULL,
[cUser_Id] [nvarchar](20) NULL,
。。)问题1. 向用户组表插入用户组权限
INSERT INTO BF_HoldBcgrp (bcgroup, cuser_id) VALUES ('A','35101'); 因为表【bf_user】中没有列“bcgroup”,所以如何根据查询表【bf_user】得出用户ID,批量插入。
列“bcgroup” 插入的是常量问题2. 插入用户权限
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF010401','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF010402','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020301','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020303','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020304','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020306','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020307','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020308','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF02020309','35104','BF');
INSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id) VALUES ('BF0202010201','35104','BF');
如何根据查询出查询表【bf_user】得出用户ID,批量插入用户权限。
[cauth_id],[csub_id]插入的值为常量。 请求解答!
--问题1
insert into BF_HoldBcgrp
select '常量',[cUser_Id] from [dbo].[BF_User]--问题2
insert into BF_HoldBcgrp
select '常量',[cUser_Id],'常量' from [dbo].[BF_User]
insert into BF_HoldBcgrp
select '常量',[cUser_Id] from [dbo].[BF_User]--问题2
insert into BF_HoldBcgrp
select '常量',[cUser_Id],'常量' from [dbo].[BF_User]
问题一和问题二都是想实现批量插入:
如下所示
insert BF_HoldBcgrp (bcgroup, cuser_id) select '群组ID', cuser_id from BF_UserINSERT INTO BF_HoldAuth (cauth_id, cuser_id, csub_id)
select cauth_id,cuser_id , cuser_id from BF_User