Insert does not map to correct fields

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

Insert does not map to correct fields

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-02-04 : 11:39:09

I have created 2 tables and populated them with data. When doing an inert into #JoblistTable from #EquentialJobListTable, fields are not mapped correctly. Example: qty_delivered in #JobListTable is filled with data from qty_received from #EquetialJobListTable?

CREATE TABLE	#JobListTable
		(
			job_date datetime,
			job_number char(15),
			cost_code char(15),
			qty_delivered decimal(8,2),
			qty_received decimal(8,2),
			qty_used decimal(8,2),
			qty_wasted decimal(8,2),
			plant_id char(10)
                                        		)

Fill table.........

CREATE TABLE	#EquentialJobListTable
		(
			job_date datetime,
			job_number char(15),
			cost_code char(15),
			qty_received decimal(8,2),
			qty_used decimal(8,2),
			qty_wasted decimal(8,2),
			plant_id char(10)
			                                    		)

Fill table .......

INSERT		#JobListTable
		(
			job_date,
			job_number,
			cost_code,
			qty_delivered,
			qty_received,
			qty_used,
			qty_wasted,
			plant_id
					)
SELECT	t2.job_date,
		t2.job_number,
		t2.cost_code,
		t2.qty_received,
		t2.qty_used,
		t2.qty_wasted,
		t2.plant_id,
		case 
			when t2.plant_id = 320 then 'RGFP'
			when t2.plant_id = 300 then 'RGWP'
			when t2.plant_id = 600 then 'RGDF'
			when t2.plant_id = 330 then 'RGGT'
			when t2.plant_id = 350 then 'RGWG'
			when t2.plant_id = 340 then 'RGDB'
			when t2.plant_id = 610 then 'RGMI'
			when t2.plant_id = 110 then 'SBGB2'
		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.cost_code = t2.cost_code
WHERE		t1.job_date IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 12:03:20

quote:

Originally posted by snufse

I have created 2 tables and populated them with data. When doing an inert into #JoblistTable from #EquentialJobListTable, fields are not mapped correctly. Example: qty_delivered in #JobListTable is filled with data from qty_received from #EquetialJobListTable?

CREATE TABLE	#JobListTable
		(
			job_date datetime,
			job_number char(15),
			cost_code char(15),
			qty_delivered decimal(8,2),
			qty_received decimal(8,2),
			qty_used decimal(8,2),
			qty_wasted decimal(8,2),
			plant_id char(10)
                                        		)

Fill table.........

CREATE TABLE	#EquentialJobListTable
		(
			job_date datetime,
			job_number char(15),
			cost_code char(15),
			qty_received decimal(8,2),
			qty_used decimal(8,2),
			qty_wasted decimal(8,2),
			plant_id char(10)
			                                    		)

Fill table .......

INSERT		#JobListTable
		(
			job_date,
			job_number,
			cost_code,
			qty_delivered,
			qty_received,
			qty_used,
			qty_wasted,
			plant_id
					)
SELECT	t2.job_date,
		t2.job_number,
		t2.cost_code,
		t2.qty_received,
		t2.qty_used,
		t2.qty_wasted,
		t2.plant_id,
		case 
			when t2.plant_id = 320 then 'RGFP'
			when t2.plant_id = 300 then 'RGWP'
			when t2.plant_id = 600 then 'RGDF'
			when t2.plant_id = 330 then 'RGGT'
			when t2.plant_id = 350 then 'RGWG'
			when t2.plant_id = 340 then 'RGDB'
			when t2.plant_id = 610 then 'RGMI'
			when t2.plant_id = 110 then 'SBGB2'
		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.cost_code = t2.cost_code
WHERE		t1.job_date IS NULL

As you see above you are mapping t2.qty_received value to be inserted to qty_delivered field. change the order to insert correct values to correct fields (i really cant find a suitable field for qty_delivered values in source though)

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-02-04 : 13:47:48

The 'qty_delivered' is not needed for the insert. Even if I leave it out from the #JobListTable, I get syntax error:

Error 121: The select list for the Insert statement contains more items than the insert list. The number of select values must match the number of Insert columns.

I do not really understand this?

INSERT #JobListTable
(
job_date,
job_number,
cost_code,
qty_received,
qty_used,
qty_wasted,
plant_id
)

I noticed, that if I comment out the 'case...' there is no syntax error and things works fine. So how can I get the 'case' back working ???????

INSERT		#JobListTable
		(
			job_date,
			job_number,
			cost_code,
			qty_received,
			qty_used,
			qty_wasted,
			plant_id
					)
SELECT	t2.job_date,
		t2.job_number,
		t2.cost_code,
		t2.qty_received,
		t2.qty_used,
		t2.qty_wasted,
		t2.plant_id
		--case 
			--when t2.plant_id = 320 then 'RGFP'
			--when t2.plant_id = 300 then 'RGWP'
			--when t2.plant_id = 600 then 'RGDF'
			--when t2.plant_id = 330 then 'RGGT'
			--when t2.plant_id = 350 then 'RGWG'
			--when t2.plant_id = 340 then 'RGDB'
			--when t2.plant_id = 610 then 'RGMI'
			-- t2.plant_id = 110 then 'SBGB2'
		--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.cost_code = t2.cost_code
WHERE		t1.job_date IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources