Alter Table Command - SQL Server Forums

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

SQL Server Development (2000)

Alter Table Command

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-28 : 18:28:20

I'm working with the following sp. I want the columns from the second select statement to be inserted into the #temp table. I think I need to use the alter tabel command but am unsure of the syntax. Combining both statements isn't an option (below is a simplified version).

Thanks, Lane

select item
into #temp
FROM mfgpro.dbo.[ANA No Dups]
where item like '12345%'

insert into #temp

SELECT MFGPRO.dbo.[ANA No Dups].item, LEFT(MFGPRO.dbo.[ANA No Dups].STATUS, 2) AS ana_s, MFGPRO.dbo.[ANA No Dups].PTPMCODE AS ana, LEFT(MFGPRO.dbo.[BRI No Dups].STATUS,
2) AS bri_s, MFGPRO.dbo.[BRI No Dups].PTPMCODE AS bri, LEFT(MFGPRO.dbo.[DEN No Dups].STATUS, 2) AS den_s,
MFGPRO.dbo.[DEN No Dups].PTPMCODE AS den, LEFT(MFGPRO.dbo.[FTM No Dups].STATUS, 2) AS ftm_s,
MFGPRO.dbo.[FTM No Dups].PTPMCODE AS ftm, LEFT(MFGPRO.dbo.[TEC No Dups].STATUS, 2) AS tec_s,
MFGPRO.dbo.[TEC No Dups].PTPMCODE AS tec, LEFT(MFGPRO.dbo.[WAY No Dups].STATUS, 2) AS way_s,
MFGPRO.dbo.[WAY No Dups].PTPMCODE AS way
FROM #temp FULL OUTER JOIN mFGPRO.dbo.[ana No Dups] on #temp.item = MFGPRO.dbo.[ANA No Dups].item FULL OUTER JOIN
MFGPRO.dbo.[FTM No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[FTM No Dups].ITEM FULL OUTER JOIN
MFGPRO.dbo.[WAY No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[WAY No Dups].ITEM FULL OUTER JOIN
MFGPRO.dbo.[BRI No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[BRI No Dups].ITEM FULL OUTER JOIN
MFGPRO.dbo.[TEC No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[TEC No Dups].ITEM FULL OUTER JOIN
MFGPRO.dbo.[DEN No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[DEN No Dups].ITEM
WHERE (MFGPRO.dbo.[ANA No Dups].ITEM = #temp.item)

select * from #temp
drop table #temp

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-28 : 19:13:59

Hi

Create the temp table with all the needed columns first using a Create Table statement. Then do both your inserts.

Damian

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-28 : 20:07:04

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources