SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Creating table from a Query results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 02/06/2014 :  03:37:39  Show Profile  Reply with Quote
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.

Edited by - stahorse on 02/06/2014 04:10:00

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/06/2014 :  10:29:44  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000