为一个唯一键SQL服务器插入multipl行 [英] Insert multipl rows for one unique key SQL server
问题描述
你好每一个
我需要为同一个主键插入多个行的表格
我的表名为invoice
表发票的列是id发票，客户发票，员工（为客户工作的员工）
所以我想插入一张发票，例如1号发票有一个客户，但是客户有超过一个员工实际上可能有超过20名员工，所以我尝试了下面的代码
但是thas不起作用。
i希望表是喜欢那个
请有人帮帮我。
idinvoice costumername员工
4 thales_informatique Samir
4 thales_informatique John
4 thales_informatique nally
4 thales_informatique Aymane
我希望你能理解我。
我尝试过：
Hello every one
I need to insert into a table more than one row for the same primary key
my table named invoice
The columns of table invoice are id invoice,costumer invoice,employee(employee which work for the costumer)
so i want to insert one invoice for example invoice number 1 have one costumer but that's costumer have more than one employee actually it may have more than 20 employee so I tried the code bellow
but thas does not work.
i want the table be like that
can anyone help me with that please.
idinvoice costumername employee
4 thales_informatique Samir
4 thales_informatique John
4 thales_informatique nally
4 thales_informatique Aymane
I hope you can understand me.
What I have tried:
declare @costumename varchar(50)
set @costumename='thales_informatique'
while @costumename='thales_informatique'
begin
insert into client values (4,'thales_informatique','Samir')
insert into client values (4,'thales_informatique','John')
insert into client values (4,'thales_informatique','nally')
insert into client values (4,'thales_informatique','Aymane')
break;
end
推荐答案
您不能在主键之间复制值。您需要设置另一个表，用于存储额外员工或将其作为列添加到现有表中。
You cannot duplicate values among primary keys. You need to setup another table where you store the extra employees or add them on as columns to your existing table.
我需要为同一个主键插入多个行的表
I need to insert into a table more than one row for the same primary key
你没有！这就是原则，主键是唯一的。
您可以拥有多个具有相同键的行，但该键不是主键。
You don't! that is the principle, a primary key is unique.
You can have multiple rows with same key, but that key will not be a primary one.
其他解决方案告诉你你不能做什么，这里有一个解决方案可以让你看到你想看到的东西。
正如我在评论中提到的，你的数据库结构是不对的。规范化您的数据并创建三个类似于这个简化示例的表...
The other solutions have told you what you cannot do, here is a solution that gets you to what you want to see.
As I mentioned in my comments, your database structure is not quite right. Normalize your data and create three tables similar to this much simplified example...
create table #customer
( custid int identity(1,1), custname nvarchar(50))
create table #employee
(empid int identity(1,1), custid int, empname nvarchar(50))
create table #invoice
(invid int identity(1,1), custid int, invdate date)现在我要把一些示例数据到我的客户和员工表中：
Now I'll put some sample data into my customer and employee tables:
insert into #customer (custname) values ('thales_informatique'), ('another client')
insert into #employee (custid, empname) values
(1, 'Samir'), (1, 'John'), (1, 'nally'), (1, 'Aymane'),
(2, 'Sammy'), (2, 'Jayne'), (2, 'Sally'), (2, 'Mary'), (2, 'Joe'), (2, 'Ian')现在我的发票表中有一些非常简单的数据
And now some very simple data into my invoice table
insert into #invoice (custid, invdate) values
(1, getdate()), (1, getdate()), (2, getdate())所以现在我有三张发票，1,2和3.但如果我现在加入我的其他表，我可以得到你看起来的结果之后：
So now I have three invoices, 1, 2 and 3. BUT if I now join to my other tables I can get the results you seem to be after:
select i.invid, i.invdate, c.custid, c.custname, e.empname
from #invoice i
inner join #customer c on i.custid = c.custid
inner join #employee e on i.custid = e.custid
order by invid, empname结果：
Results:
Invid InvDate CustId CustName EmpName
1 2018-03-02 1 thales_informatique Aymane
1 2018-03-02 1 thales_informatique John
1 2018-03-02 1 thales_informatique nally
1 2018-03-02 1 thales_informatique Samir
2 2018-03-02 1 thales_informatique Aymane
2 2018-03-02 1 thales_informatique John
2 2018-03-02 1 thales_informatique nally
2 2018-03-02 1 thales_informatique Samir
3 2018-03-02 2 another client Ian
3 2018-03-02 2 another client Jayne
3 2018-03-02 2 another client Joe
3 2018-03-02 2 another client Mary
3 2018-03-02 2 another client Sally
3 2018-03-02 2 another client Sammy现在我得到每个发票的所有客户员工的清单。如果你真的想要（但你不应该），你可以将这些结果插入到另一个表中，但你必须有另一个主键。
但是这不是一个真正有用的查询。您不希望为每张发票列出每位员工。通常你只想知道谁参与了这次销售。因此，您将需要另一个表来创建该关系 - 哪个员工参与了哪个发票，以及原因。例如
Now I get a list of all the customer's employees for each invoice. You could insert those results into another table if you really wanted to (but you shouldn't), but you will have to have another "primary key".
But that isn't really a useful query. You don't want to be listing every single employee for every invoice. Usually you just want to know who was involved in that sale. So you are going to need another table to create that relationship - which employee was involved in which invoice, and why. For example
create table #empToinv
(invid int, empid int, emprole varchar(50))
insert into #empToinv (invid, empid, emprole) values
(1, 1, 'Salesman'), (1,4,'Supervisor'), (1,3, 'Picker'),
(2,2, 'Salesman'),
(3,10, 'Salesman'), (3,8,'Supervisor')并更改查询以使用新表
select i.invid, i.invdate, c.custid, c.custname, e.empname
from #invoice i
inner join #empToinv T on i.invid = T.invid
inner join #customer c on i.custid = c.custid
inner join #employee e on T.empid = e.empid
order by invid, empname结果：
Invid InvDate CustId CustName EmpName
1 2018-03-02 1 thales_informatique Aymane
1 2018-03-02 1 thales_informatique nally
1 2018-03-02 1 thales_informatique Samir
2 2018-03-02 1 thales_informatique John
3 2018-03-02 2 another client Ian
3 2018-03-02 2 another client Mary
这篇关于为一个唯一键SQL服务器插入multipl行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
