[Resolved] Change column value at time of insert

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

General SQL Server Forums

New to SQL Server Programming

[Resolved] Change column value at time of insert

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-01-29 : 14:19:37

Prior to insert (into #JobListTable) I need to change the value of the 't2.plant_id' column (from #EquentialJobListTable). This column will have a value of 110, 300 or 320. If 110 then value should be SBGB2, if 300 then values should be RGWP and if 320 value should be RGWP. Is this possible to do?

INSERT		#JobListTable
		(
			job_date,
			job_number,
			job_phase,
			qty_received,
			plant_id
					)
SELECT	t2.job_date,
		t2.job_number,
		t2.job_phase,
		t2.qty_received,
		t2.plant_id
FROM		#EquentialJobListTable AS t2
LEFT JOIN	#JobListTable AS t1 ON t1.job_date = t2.job_date
			AND t1.job_number = t2.job_number
			AND t1.job_phase = t2.job_phase
WHERE		t1.job_date IS NULL

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-29 : 14:50:57

Check out CASE in Books Online:


SELECT	t2.job_date,
		t2.job_number,
		t2.job_phase,
		t2.qty_received,
		case 
			when t2.plant_id = 110 then 'SBGB2'
			when t2.plant_id = 300 then 'RGWP'
			when t2.plant_id = 320 then 'RGWP'
			end
FROM		#EquentialJobListTable AS t2
LEFT JOIN	#JobListTable AS t1 ON t1.job_date = t2.job_date
			AND t1.job_number = t2.job_number
			AND t1.job_phase = t2.job_phase
WHERE		t1.job_date IS NULL

Be One with the Optimizer
TG

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-01-29 : 15:02:51

Thank you, thank you, works like a dream......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources