如何从多个表中选择几列并插入到一个表中。 [英] How to select few columns from multiple table and insert into one table.

问题描述

我有3张桌子

itemcreation table

Item_Name varchar（50），

Item_Code varchar（50），

Group_id int，（外键）

Unit_id int（外键）





itemgroup table

Group_Id int（主键）

Group_Name varchar（50），

Chbox_Val varchar（50），

UnderGroup varchar（50）





单位表

Unit_Id（主键）

Unit_Name varchar（50），

Unit_Abbreviation varchar（50）



i想要将表项组中的Group_name，Undergroup插入表项创建

还将unit_name从unit table into itemcreation table。



我尝试过的事情：



i这样做：

INSERT INTO tbl_ItemCreation SELECT @ Item_Name，@ Item_Code，t1.Group_Name，t1.underGroup，t2.unit_Name

FROM tbl_ItemGroup t1，tbl_Unit t2

内部联接tbl_ItemCreation t3 on t3.ItemCreation_Id = t1.ItemGroup_id

内部加入t3l_ItemCreation t3 on t3.ItemCreation_Id = t2.Unit_Id



但是收到错误

i have 3 tables
itemcreation table
Item_Name varchar(50),
Item_Code varchar(50),
Group_id int,(foreign key)
Unit_id int (foreign key)


itemgroup table
Group_Id int (primary key)
Group_Name varchar(50),
Chbox_Val varchar(50),
UnderGroup varchar(50)


Unit table
Unit_Id (primary key)
Unit_Name varchar(50),
Unit_Abbreviation varchar(50)

i want to insert Group_name,Undergroup from table itemgroup into table itemcreation
also unit_name from unit table into itemcreation table.

What I have tried:

i did this:
INSERT INTO tbl_ItemCreation SELECT @Item_Name,@Item_Code,t1.Group_Name,t1.underGroup ,t2.unit_Name
FROM tbl_ItemGroup t1,tbl_Unit t2
Inner JOIN tbl_ItemCreation t3 on t3.ItemCreation_Id=t1.ItemGroup_id
Inner Join tbl_ItemCreation t3 on t3.ItemCreation_Id=t2.Unit_Id

but getting error

推荐答案

尝试运行时遇到的错误，以及我如何修复每个错误：



1.无效对象name'tbl_ItemGroup'（等）

这完全取决于您描述问题的方式。我愚蠢地假设你在给出架构时使用了真正的表名。我已经创建了表，所以下面发布的任何sql都会缺少表名上的tbl_。



2.多部分标识符t1.ItemGroup_id 无法绑定。

再次，直到您提供的架构。在下面的任何sql中，该列将被称为Group_id



3. 在FROM子句中多次指定相关名称't3'。

这是一个真正的问题。您不能使用别名 t3 两次。所以你要么意味着

The errors I got when trying to run this, and how I fixed each one:

1. Invalid object name 'tbl_ItemGroup' (et al)
This is entirely down to the way you have described your problem. I foolishly assumed you had used the real table names when you gave the schema. I had already created the tables so any sql posted below will be missing the "tbl_" on the table names.

2. The multi-part identifier "t1.ItemGroup_id" could not be bound.
Again, down to the schema you provided. In any sql below that column will be called Group_id

3. The correlation name 't3' is specified multiple times in a FROM clause.
This is a real problem. You can't use the alias t3 twice. So you either meant

Inner JOIN ItemCreation t3 on t3.ItemCreation_Id=t1.Group_Id
Inner Join ItemCreation t4 on t4.ItemCreation_Id=t2.Unit_Id

或者更可能意味着

or probably more likely meant

Inner JOIN ItemCreation t3 on t3.ItemCreation_Id=t1.Group_Id
                AND t3.ItemCreation_Id=t2.Unit_Id

4.多部分标识符t1.Group_Id无法绑定。

起初我认为这只是模式和你发布的查询之间的差异，但后来我发现你还没有加入 Unit 正确

您需要使用JOIN而不是您使用的旧语法。否则，您将需要一个WHERE子句来定义Unit应如何链接到其他表之一。所以我再看一下我在第3点提到的那些线，并得出结论，你的实际意思是

4. The multi-part identifier "t1.Group_Id" could not be bound.
At first I thought this was just down to the differences between the schema and the query you posted but then I spotted that you hadn't joined to Unit correctly
You need to use JOIN rather than the old syntax you have used. Otherwise you will need a WHERE clause to define how Unit should link to one of the other tables. So I looked again at the lines I mentioned in point 3 and concluded that what you actually meant was

INSERT INTO ItemCreation
SELECT @Item_Name,@Item_Code,t1.Group_Name,t1.underGroup ,t2.unit_Name
FROM ItemGroup t1
Inner JOIN ItemCreation t3 on t3.ItemCreation_Id=t1.Group_Id
inner join Unit t2 on t3.ItemCreation_Id=t2.Unit_Id

5.这里没有语法错误但是你在这个查询中没有WHERE子句 - 这意味着它将为表中已经存在的每一行插入一个新行 - 我不确定这也是你真正想做的事情。



修复你的错误并尝试多次运行你的查询。检查行为是否真的是你想要的。如果没有，那么一定会带回一些样本数据和预期结果。

5. Not a syntax error here but you have no WHERE clause on this query - which means it is going to insert a new row for every row that is already on the table - I'm not sure that's what you really want to do either.

Get your errors fixed and try running your query several times. Check the behaviour is really what you want. If not then by all means come back with some sample data and an expected result.

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