June 6, 2011 at 1:31 pm
I am having difficulty in trying to wrap my head around, maybe it is just that I am back from vacation, don't know. The requirement is to create a duplicate record for each distinct record but with different values in the last two columns. In this case NULL values Two records for one and all records to live in same table. Example:
ProductLineProductCodePartStrategyBoneStrategy
------------------------------------------------------------------------
00002370-0000-0000TG280BG2380
00002370-0000-0000NULLNULL
00002371-0000-0000TG2801BG23801
00002371-0000-0000NULLNULL
Source code:
CREATE TABLE dbo.Products
(ProductLinechar(4)NOT NULL,
ProductCodechar(14)NOT NULL,
PartStrategyvarchar(8)NOT NULL,
BoneStrategyvarchar(8)NOT NULL)
INSERT INTO dbo.Products
VALUES ('0000', '2370-0000-0000', 'TG280','BG2380')
INSERT INTO dbo.Products
VALUES ('0001', '2371-0000-0000', 'TG2801','BG23801')
INSERT INTO dbo.Products
VALUES ('0002', '2372-0000-0000', 'TG2802','BG23802')
INSERT INTO dbo.Products
VALUES ('0002', '2373-0000-0000', 'TG2803','BG23803')
INSERT INTO dbo.Products
VALUES ('0004', '2374-0000-0000', 'TG2804','BG23804')
June 6, 2011 at 1:44 pm
Are you looking for something like this:
insert into products
select ProductLine, ProductCode, NULL, NULL
from products
where not exists (select 1 from products p2
where product.ProductLine = p2.ProductLine
and product.ProductCode = p2.ProductCode
and p2.PartStrategy IS NULL
and p2.BoneStrategy IS NULL)
June 6, 2011 at 1:49 pm
Yes, thank you very much for your time. However, I should of looked at my code a little better as I had the two strategy columns listed as not accepting nulls. Thank you again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply