Post reply

Moving Values from Temp table to another tamp Table

  • April 9, 2014 at 12:01 pm

    #396572

    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

    #1704643

    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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 9, 2014 at 12:19 pm

    #1704646

    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