April 9, 2014 at 12:01 pm
Hi,
Below are my temp tables
--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL
SELECT '12A','Nokia' UNION ALL
SELECT '13A', 'Alcatel' UNION ALL
SELECT '14A', 'Motorolla' UNION ALL
SELECT '15A', 'ChinaSets' UNION ALL
SELECT '16A', 'Apple'
) data (Resourcekey, value)
;
create table #Resource (StringId Int identity(1,1) primary key,Resourcekey varchar(50));
create table #Resource_Trans (TransId Int identity(1,1) primary key,StringId int, value varchar(50));
I want to loop through the data from #Base_Resource and do the follwing logic.
1. get the Resourcekey from #Base_Resource and insert into #Resource table
2. Get the SCOPE_IDENTITY(),value and insert into to
#Resource_Trans table's column(StringId,value)
I am able to do this using while loop. Is there any way to avoid the while loop to make this work? Any sugestions or samples please
April 9, 2014 at 12:15 pm
Two solutions come to my mind. The first one assumes you don't have duplicate values for Resourcekey.
INSERT #Resource (Resourcekey)
SELECT Resourcekey
FROM #Base_Resource
INSERT #Resource_Trans (StringId, value)
SELECT r.StringId, b.value
FROM #Base_Resource b
JOIN #Resource r ON b.Resourcekey = r.Resourcekey
This second option might be better but has to pay a cost to order the data.
INSERT #Resource (Resourcekey)
SELECT Resourcekey
FROM #Base_Resource
ORDER BY Resourcekey
INSERT #Resource_Trans (StringId, value)
SELECT ROW_NUMBER() OVER(ORDER BY Resourcekey), b.value
FROM #Base_Resource b
April 9, 2014 at 12:19 pm
Hi Luis.
Yes, I don't have duplicates. So the first suggestion should work for me. Thank you for your suggestion and time on this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply