如何从另一个表中的表中插入记录 [英] how to insert the records in a table from onother table

问题描述

其实我在这里有三个表（注册，访问，user_access）我想要的是我在注册表中插入一条记录，同时我想在访问表中插入所有记录（记录是静态的） to user_access table with registration_id .........

例如：

//我在注册表中插入一条记录

- -------------------------------------------------- ---------------------------

registration_id |用户名| loginid |密码------------------------------------------------- -----------------------------

1 | nitish | [e-mail] | 12345





同时我在访问表中已经有3条记录（静态）如下图所示

------------------------------------------------- ------------------------------

accessid |描述|活跃的

----------------------------------------- --------------------------------------

1 |报告| 1

2 |引导| 1

3 |进口| 1



i希望在user_access表中插入访问记录，其中registration_id如下表所示

----------- -------------------------------------------------- -----------------

registration_id | access_id |活跃的

----------------------------------------- ----------------------------------------

1 |报告| 1

1 |引导| 1

1 |进口| 1





实际上我无法清楚地解释我的问题......



这是我的存储过程代码

Actually i am having three tables (registration,access,user_access) here what i want is i am inserting a record in registration table,at the same time i want to insert all the records(records are in static) in access table to user_access table with registration_id.........
example:
//i inserted one record in registration table
------------------------------------------------------------------------------
registration_id | username | loginid | password ------------------------------------------------------------------------------
1 | nitish | [e-mail] | 12345


at the same time i already having 3 records(static) in access table as shown bellow
-------------------------------------------------------------------------------
accessid | description | active
-------------------------------------------------------------------------------
1 | reports | 1
2 | leads | 1
3 | import | 1

i want to insert access records in user_access table with registration_id looks like below table
------------------------------------------------------------------------------
registration_id | access_id | active
---------------------------------------------------------------------------------
1 | reports | 1
1 | leads | 1
1 | import | 1


actually i am unable to explain my problem clearly......

this was my stored procedure code

if @option='InsertRegistrationDetails'
begin
insert into School_Details(School_Name,Address,Email_ID,Mobile_No,Fax_No,City,State,Country,Zip_Code,Created_On)values(@School_Name,@Address,@Email_ID,@Mobile_No,@Fax_No,@City,@State,@Country,@Zip_Code,getdate())
insert into Login_Details(School_ID,First_Name,Login_id,Password,Status,Designation,Created_on)values(@School_ID,@First_Name,@Login_id,@Password,@Status,@Designation,getdate())
update login_details set school_id=(select School_ID from school_details where school_name=@School_Name) where Login_id=@Login_id
--insert into User_Access(user_identity,Access_id,Active)select Access_id,active from access and user_identity=(select id from login_details where Login_id=@Login_id)
end

推荐答案

插入语句经常被忽视的一个方面是你不必使用VALUES语句。相反，你可以做的是在这个地方使用另一个select语句。你在这里可以做的是插入/选择查询。现在，在我向您展示语法之前，我想指出您的user_access表示例对我而言。我假设这个表中的access_id实际上应该是键，而不是描述。



对，查询：

An often overlooked aspect of an insert statement is that you don''t have to use the VALUES statement. What you can do, instead, is use another select statement in this place. What you could do here is do an insert/select query. Now, before I show you the syntax, I''d like to point out that your user_access table example looks wront to me. I assume that access_id in this table should actually hold the key, instead of the description.

Right, the query:

INSERT INTO user_access
SELECT registration.registration_id, access.access_id, 1
FROM registration JOIN access

你可能会注意到我实际上没有放入任何连接标准 - 这是因为你在这里有一个交叉连接，所以两个表中的所有行都匹配。

You may notice that I haven''t actually put any join criteria in - this is because what you have here is a cross join, so all the rows from both tables match.

我完全不明白你的问题。看看使用SELECT来INSERT记录 [ ^ ]，它可能对您有所帮助。如果您有任何问题，请告诉我。

I did not understand your question completely. Take a look at Using SELECT to INSERT records[^], it might help you. Let me know if you have any questions.

这篇关于如何从另一个表中的表中插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！