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
gives results of
pdcode name
20 1
20 2
20 3
30 1
30 2
30 3
- 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 *
from CTE
left outer join joemens J on J.[name]=CTE.[name] AND J.pdcode=CTE.pdcode
which results in
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
- 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