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.
| Author |
Topic |
|
Taragor
Starting Member
46 Posts |
 Posted - 2005-01-27 : 15:46:24
|
| Greetings all,I'm slowly starting out in SQL and am trying to find a way to copy column names / datatypes from a main table to a temporary table.The following code is the data manipulation I am doing from the main table and would like to automatically copy the columns from main to table1 and table 2.***There's currently only 3 fields listed (for testing purposes) but the database itself has approximately 75 fields of different data types.--------------create table #table1 (exp1 char(12), exp2 datetime, exp3 smallint)insert into #table1 (exp1, exp2, exp3) (select distinct exp1, exp2, exp3 from maintable where exp1 = value and exp3 = 1)create table #table2 (exp1 char(12), exp2 datetime, exp3 smallint)insert into #table2 (exp1, exp2, exp3) (select distinct exp1, exp2, exp3 from maintable where exp1 = value and exp3 = 2)select distinct *from maintablewhere (exp3 = 2) and exp1 in (select exp1 FROM #table2 WHERE exp2 NOT IN (SELECT exp2 FROM #table1))-----Is there a T-SQL statement I can use to have all the columns from the main table replicated into the temp tables or do I have to manually input them? Taragor |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-01-27 : 16:32:30
|
| Select *INTO #TempTableFrom RealTable--Where 1=0 to create an empty table with the same structure |
 |
|
|
Taragor
Starting Member
46 Posts |
Posted - 2005-01-28 : 11:29:33
|
| TG: I now feel like a complete putz. :) Thanks, worked fine. Reason wasn't working when I tried it is I kept trying to create the table prior to doing the "into" command.Taragor |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-01-28 : 11:39:55
|
| I know the feeling well...Be one with the optimizerTG |
|
|
|
|
|
|
|
|
Copying Column Names / Format to a Temp Table