June 1, 2011 at 8:09 am
Hi,
I have these two following tables:
[Registry]
pinDate
12543-Feb-2011
12544-Feb-2011
12545-Feb-2011
11114-Feb-2011
11115-Feb-2011
[Rule]
Date
3-Feb-2011
4-Feb-2011
5-Feb-2011
6-Feb-2011
I need to insert into the Registry table, the date that is missing for each distinct pin, based on the Rule Table.
I need to get the result as:
[Registry]
pinDate
12543-Feb-2011
12544-Feb-2011
12545-Feb-2011
12546-Feb-2011 // Iserted
11113-Feb-2011 // Inserted
11114-Feb-2011
11115-Feb-2011
11115-Feb-2011 //Inserted
Does Anyone know how to to this?
June 1, 2011 at 9:01 am
highlord1 (6/1/2011)
Does Anyone know how to to this?
Yes. Use an inline view to check for non-existing rows a.k.a. the ones you want to insert.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 1, 2011 at 9:10 am
It looks like a Select with an Outer Join should do what you need.
select pin, Rule.Date
from [Pins]
cross join [Rule]
left outer join [Registry]
on [Pins].Pin = [Registry].Pin
and [Rule].Date = [Registry].Date
where [Registry].Date is null;
I'm assuming you have a table of the distinct Pin values. If not, you'll need to add a sub-query to get those.
Then, when you have the Select doing what you want, add an Insert statement on top of it, and you'll have what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 1, 2011 at 9:11 am
Make sure you understand this code before deploying it.
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.registry')
AND type IN (N'U') )
DROP TABLE dbo.registry
GO
CREATE TABLE dbo.registry (pin INT, dt DATE)
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.my_rule')
AND type IN (N'U') )
DROP TABLE dbo.my_rule
GO
CREATE TABLE dbo.my_rule (dt DATE)
GO
INSERT INTO dbo.registry
(pin, dt)
VALUES (1254, '3-Feb-2011'),
(1254, '4-Feb-2011'),
(1254, '5-Feb-2011'),
(1111, '4-Feb-2011'),
(1111, '5-Feb-2011')
INSERT INTO dbo.my_rule
(dt)
VALUES ('3-Feb-2011'),
('4-Feb-2011'),
('5-Feb-2011'),
('6-Feb-2011') ;
WITH all_pin_dt_combinations_cte(pin, dt)
AS (
SELECT pin.pin,
my_rule.dt
FROM (
SELECT DISTINCT
pin
FROM dbo.registry
) pin
CROSS JOIN (
SELECT DISTINCT
dt
FROM dbo.my_rule
) my_rule
)
--SELECT * FROM all_pin_dt_combinations_cte;
SELECT cte.pin,
cte.dt
FROM all_pin_dt_combinations_cte cte
LEFT JOIN dbo.registry r ON cte.pin = r.pin
AND cte.dt = r.dt
WHERE r.pin IS NULL ;
PS Your naming convention leaves a lot to be desired. "Rule" is a reserved word in SQL Server...we try not to use those as identifiers. Same with "Date"!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply