[Resolved] Force value into temp table field

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 2005 Forums

Transact-SQL (2005)

[Resolved] Force value into temp table field

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-04-16 : 13:27:00

I have a sql script where I need to read from multiple data bases. I've created a temp table and each time I loop through data bases I need to manually set a value into the "plant_id" field in the temp table The "plant_id" does not come from any tables). Not sure how to do this. Thank you.

create table	#PlantTable

	         		(	
                                   plant_id char(10),
			           shift_date datetime,
			           outside_qty decimal(15,2),
			           intercompany_qty decimal(15,2)  


*** Loop 1 - need to set plant_id = 'RGWP'
                		)
Insert into                       #PlantTable(shift_date, intercompany_qty, outside_qty)
select * from openquery(ADS_RGWP_SERVER, 'select
                                             max(shift_started) as shift,
                                             sum(case when customer_type = ''Inter-Company'' then qty_shipped_today else 0 end) as intercompany_qty,
                                             sum(case when customer_type = ''Charge'' then qty_shipped_today else 0 end) as outside_qty
                                          from salestkt')

*** Loop 2 - need to set plant_id = 'RGFP'
                		)
Insert into                       #PlantTable(shift_date, intercompany_qty, outside_qty)
select * from openquery(ADS_RGFP_SERVER, 'select
                                             max(shift_started) as shift,
                                             sum(case when customer_type = ''Inter-Company'' then qty_shipped_today else 0 end) as intercompany_qty,
                                             sum(case when customer_type = ''Charge'' then qty_shipped_today else 0 end) as outside_qty
                                          from salestkt')

*** Loop 3 - need to set plant_id = 'RGDF'
                		)
Insert into                       #PlantTable(shift_date, intercompany_qty, outside_qty)
select * from openquery(ADS_RGDF_SERVER, 'select
                                             max(shift_started) as shift,
                                             sum(case when customer_type = ''Inter-Company'' then qty_shipped_today else 0 end) as intercompany_qty,
                                             sum(case when customer_type = ''Charge'' then qty_shipped_today else 0 end) as outside_qty
                                          from salestkt')


select * from #PlantTable

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-04-16 : 17:57:57

You can specify the value as part of your SELECT:

Insert into #PlantTable(plant_id, shift_date, intercompany_qty, outside_qty)
select 'RGWP', * from openquery(ADS_RGWP_SERVER, 'select
max(shift_started) as shift,
sum(case when customer_type = ''Inter-Company'' then qty_shipped_today else 0 end) as intercompany_qty,
sum(case when customer_type = ''Charge'' then qty_shipped_today else 0 end) as outside_qty
from salestkt')

-- Repeat as necessary

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-04-19 : 13:58:06

Thank you Bustaz...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources