November 1, 2012 at 10:39 pm
Hi,
I have a question & I think I know the answer but am struggling to get the exact result set I'm after.
I currently have one table (source) where i have an up to date list of items.
[dbo].[itemlist]
ID INT (Identity 1,1),
Description VARCHAR(100)
Example of the Source Dataset is as below
1, Furniture
2, BathroomCare
I then have another table (target) where i need to insert the description into it and generate a certain number which is an increment of 1000 each time.
[dbo].[Item]
ID INT (IDENTITY 1,1),
ItemCategoryID INT,
Description VARCHAR(100)
Example of the Target Dataset is as below
1, 1000, Electronic
2, 1100, Homeware
3, 1200, Gardening
Outcome/end goal:
I need to create a script that will produce the following results as you can see it grabbed the MAX(ItemCategoryID) 1200 first, added 1000 then inserted Source.ID = 1 with itemCategoryID 1300, then done the same thing for the next records.
1, 1000, Electronic
2, 1100, Homeware
3, 1200, Gardening
4, 1300, Furniture
5, 1400, BathroomCare
Currently im trying to use a WHILE loop but i've only ever done this where i use SCOPE_IDENTITY()because i'm passing through a unique value this time im not. I've also created temporary tables to store just description in @a etc
Any idea's/help would be appreciated.
Thanks
Tava
EDIT: spelling errors
November 1, 2012 at 11:28 pm
do you want this type of query
declare @itemlist table
(
ID INT Identity (1,1),
Descs VARCHAR(100)
)
declare @item table
(
ID INT Identity (1,1),
ItemCategoryID VARCHAR(100),
Descs VARCHAR(100)
)
insert into @itemlist
select * from
(
values
('Furniture'),
('BathroomCare')
)
a (name)
insert into @item
select * from
(
values
(1000,'a'),
(1100,'b'),
(1200,'c')
)
a (id,name)
DECLARE @max-2 INT =(SELECT MAX(ItemCategoryID) FROM @item)
INSERT INTO @item
select (ROW_NUMBER() over (order by a.Id))*100 +@MAX,a.Descs
from @itemlist a
left join @item b on a.Descs=b.Descs
SELECT * FROM @item
November 2, 2012 at 1:39 am
Its a good solution by BriPan,
although you can get away without doing the lookup of the max ItemCategoryId, as long as you can be sure that the Identity column on the target table doesnt have any holes by using
Declare @Base int =1000
select @Base+((Row_NUMBER() OVER(Order by a.ID)-1)+SCOPE_IDENTITY())*100,a.Descs
from @itemlist a
left join @item b on a.Descs=b.Descs
Where b.Desc is NULL
I'm not sure why BriPan has the left join unless the there is a missing "Where b.Desc is NULL" missing from the insert statement to prevent duplicate descriptions occuring.
Edit : Typo & Missing Where clause in SQL
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 4, 2012 at 2:30 pm
Thanks guys,
I had the same solution as BriPan up to the Row_Number statement - i had a while loop that would do it while a < b it worked but i thought to much logic in there for no apparent reason.
Jason, there shouldnt be any holes but as anything never a guarantee... why the left join used im not sure cause there will never be that case as its a mapping table im bringing in only distinct values.
Looks like i need to research the Row_Number values a little more as seems quiet valuable
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply