Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Moving Values from Temp table to another temp Tabl

Author  Topic 

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-04-09 : 14:14:41
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


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 suggestions or samples please

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-10 : 01:31:36
no loop needed

INSERT INTO #Resource(Resourcekey)
OUTPUT INSERTED.StringID INTO #Resource_Trans(StringId)
SELECT Resourcekey
FROM #Base_Resource


UPDATE RT
SET
RT.Value= BS.Value
FROM
#Resource_Trans as RT
INNER JOIN #Resource as RS ON RS.StringID=RT.StringID
INNER JOIN #Base_Resource as BS
ON BS.Resourcekey=RS.Resourcekey



output

TransId StringId value
1 1 Samsung
2 2 Nokia
3 3 Alcatel
4 4 Motorolla
5 5 ChinaSets
6 6 Apple



StringId Resourcekey
1 11A
2 12A
3 13A
4 14A
5 15A
6 16A



sabinWeb MCP
Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-04-11 : 10:02:48
thank you stepson. Appreciate your time on this.
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-11 : 14:52:04
Your welcome!


sabinWeb MCP
Go to Top of Page
   

- Advertisement -