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 |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2002-12-12 : 08:56:52
|
San writes "Is there a way to declare a table variable to have the same shape (meaning same columns and column types) of an existing user table without explicitly specifying all the column names and column types?Eg., Say I have a table called Person with 15 columns. I want a local table variable to have the same structure as Person. I know I can dodeclare @myPersonTable (col1, col1Type.....col15 col15Type).ThanksSan" |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-12-12 : 10:29:19
|
No, but you can with temp tables....select * into #sysdb from sysdatabase Jay White{0} |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-12-12 : 10:40:50
|
I like using an automated script to get all that. This script will look up all the info you need from the syscolumns and systypes tableselect case when st.name = 'varchar' then sc.name + ' ' + st.name + '(' + convert(varchar(5),sc.length)+'),' else sc.name + ' ' + st.name + ','endfrom syscolumns sc inner join systypes st on st.xtype = sc.xtype where id = object_id('resource_data_seq_administration') order by colidthe results will be col1 varchar(40),col2 int,and so on. Copy paste the result (easiest if you tell query analyzer to spit out everything into a text file) and do a couple modifcations. You'll need to remove the comma from the last line (haven't quite figured out a way to automate that)Hope it helps... typing out column names and data types can take waaaay to long sometimes-----------------------SQL isn't just a hobby, It's an addiction |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-12-12 : 10:42:27
|
SELECT * INTO #MyTempTableFROM MyRealTableWHERE 1=0This will create #MyTempTable with same structure and zero records. |
|
|
|
|
|