如果不存在该ID，如何插入具有不同id的重复记录 [英] How to insert duplicate records with different id if its not present with that id

问题描述

name   pdcode    amount
 1     20       5
 1     30       10 
 2     20       199
 3     30       40

如果你看到上面的pdcode = 30不能用于name = 2，并且同样的pdcode = 20不适用于name = 3。我需要输出如下

If you see above pdcode=30 is not available for name =2 and similarily pdcode=20 not available for name=3 . I need output as below

name   pdcode    amount
 1     20       5
 1     30       10 
 2     20       199
 2     30        0
 3     30       40
 3     20        0

推荐答案

如果你是说 [name] 的每个值都必须包含 [pdcode] 的所有可能值的条目，然后......



1.确定 [name] 和的所有可能组合[pdcode ] 是必需的。如果您假设该表至少包含每个[名称]的一条记录，并且每个[pdcode]至少包含一条记录，则可以通过选择 DISTINCT [ ^ ]每个值并使用交叉申请 [ ^ ]



2.您可以将这些结果放入公用表格表达式（CTE） - 请参阅 SQL SERVER 2008中的公用表表达式（CTE） [< a href =http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVERtarget =_ blanktitle =New Window> ^ ]



3.如果您使用CTE的结果作为左表（即我们想要的）和 LEFT OUTER JOIN [ ^ ]我们已经你可以在桌面上过滤原始表格中pdcode为NULL的结果 - 即缺失行列表



4.然后你可以< a href =http://www.w3schools.com/sql/sql_insert_into_select.asp> INSERT INTO [ ^ ]这些值进入原始表中，默认值为金额



这样简单得多听起来......

- 创建测试数据：

If you are saying that every value of [name] must have an entry for all possible values of [pdcode] then...

1. Determine what all the possible combinations of [name] and [pdcode] are required. If you assume that the table contains at least one record for each [name] AND at least one record for each [pdcode] you can do this by selecting the DISTINCT[^] values of each and using CROSS APPLY[^]

2. You can put those results into a Common Table Expression (CTE) - see Common Table Expressions(CTE) in SQL SERVER 2008[^]

3. If you use the results from the CTE as the left table (i.e. what we want) and LEFT OUTER JOIN[^] to what we already have on the table you can filter the results for where pdcode from the original table is NULL - i.e. the list of "missing" rows

4. You can then just INSERT INTO[^] those values into the original table with a default value for the amount

It's a lot simpler than it sounds...
- Create Test data:

create table joemens
(
    [name] int,
    pdcode int,
    amount int
)

insert into joemens values
(1,20,5),
(1,30,10),
(2, 20, 199),
(3,30,40)

- 确定我们需要的所有值

- Identify all the values we need

SELECT * FROM
(select distinct pdcode from joemens) A
CROSS APPLY (select distinct [name] from joemens) B

给出

pdcode name
20     1
20     2
20     3
30     1
30     2
30     3

- 将其粘贴到CTE并确定差距

- Stick it into a CTE and identify the gaps

;WITH CTE AS(
    SELECT * FROM
    (select distinct pdcode from joemens) A
    CROSS APPLY (select distinct [name] from joemens) B
)
select * -- CTE.[Name], CTE.pdcode, 0
from CTE
left outer join joemens J on J.[name]=CTE.[name] AND J.pdcode=CTE.pdcode

导致

20	1	1	20	5
20	2	2	20	199
20	3	NULL	NULL	NULL
30	1	1	30	10
30	2	NULL	NULL	NULL
30	3	3	30	40

- 将它们全部拉到一起 - 我们要插入出现NULL的行

- pull it all together - we want to insert the rows where the NULLs appear

;WITH CTE AS(
    SELECT * FROM
    (select distinct pdcode from joemens) A
    CROSS APPLY (select distinct [name] from joemens) B
)
INSERT INTO joemens
select CTE.[Name], CTE.pdcode, 0
from CTE
left outer join joemens J on J.[name]=CTE.[name] AND J.pdcode=CTE.pdcode
WHERE J.pdcode IS NULL

以下组合：

1. SQL SERVER 2008中的公用表表达式（CTE） [ ^ ]

2. SQL连接的可视化表示 [ ^ ]

导致：

A combination of:
1. Common Table Expressions(CTE) in SQL SERVER 2008[^]
2. Visual Representation of SQL Joins[^]
leading to:

With CTE1(pdcode)
AS
(
SELECT pdcode FROM tablename
),
CTE2(name, pdcode)
AS
(
SELECT DISTINCT t1.name, CTE1.pdcode FROM CTE1, tablename t1
)
SELECT CTE2.name, CTE2.pdcode, ISNULL(t2.amount, 0)  FROM CTE2
LEFT JOIN tablename t2 on CTE2.name=t2.name and CTE2.pdcode=t2.pdcode

这篇关于如果不存在该ID，如何插入具有不同id的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！