Creating table from a Query results

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

Creating table from a Query results

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2014-02-06 : 03:37:39

Hi

I have this query;

select c.name
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
where t.name= 'Hosea_tblDATA_NOTES'and c.is_identity = 0
order by column_id

and I get these results;

NOTE_ID
NOTE_DESCRIPTION
NOTE_TEXT
NOTE_STATUS
NOTE_STARTDATE
NOTE_ENDDATE
NOTE_AUTHOR
NOTE_LASTUPDATE

I was thinking if it is possible to create a table from the results of this query, my table name(in this case 'Hosea_tblDATA_NOTES') will be a parameter, that whatever table name I pass, with the results I get I will be able to create another table, assuming all data types are varchars, but it should be generic.

I want to create a new table with the about of that query, that output has to be my columns. Help please, or advise.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-06 : 10:29:44

you can easily create a table from the results of a SELECT by using SELECT INTO. The datatypes will be implicitly determined by the underlying data.


SELECT <column List>
INTO <newTableName>
FROM <from clause>
WHERE <where clause>

And you should use the information.schema views rather than system tables:


select column_name
from   information_schema.columns
where  table_name = @tableName
and    columnProperty(object_id(table_name), column_name, 'isidentity') = 0
order by ordinal_position

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources