Moving Values from Temp table to another tamp Table

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  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

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  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.