CODEWITHSUNDEEP
sql-serversql-server-2012ssmsssms-2012
craig
craig

Reputation: 26262

Generate CREATE TABLE statement from arbitrary SQL query

Is there script/procedure/function that can generate a CREATE TABLE statement from an arbitrary SQL query?

When building procedures, I'd like to have a quick way to generate a temporary table, rather than having to review the table definitions of all of the tables referenced in the query.

Simple example:

SELECT p.pat_id, pat_name, 
       enc_id, admsn_time, disch_time
FROM   patient p
INNER JOIN encounter e ON p.pat_id=e.pat_id
WHERE  admsn_time >= '01/01/2014'

Would generate (columns' data definition is retrieved from the system table):

-- randomly-generated table name
CREATE TABLE #random_name (
  PAT_ID      VARCHAR(18) NOT NULL,
  PAT_NAME    VARCHAR(200),
  ENC_ID      NUMERIC(18,0) NOT NULL,
  ADMSN_TIME  DATE,
  DISCH_TIME  DATE
)

SSMS workflow:

  1. select the text
  2. right click, select Generate CREATE TABLE statement (function/script/proc called; result placed on clipboard)
  3. place cursor in desired location
  4. paste

Upvotes: 1

Views: 249

Answers (3)

WonderWorker
WonderWorker

Reputation: 9062

First of all, I would make a stored procedure of the query. (It keeps me from forgetting it later)

Secondly, I would write a query to generate the table for me:

DECLARE @CREATE_TABLE_QUERY NVARCHAR(MAX) = N'';


SELECT 
    @CREATE_TABLE_QUERY += ', ' + name + ' ' + UPPER(system_type_name) + CHAR(13) + CHAR(10) + CHAR(9)

FROM 
    sys.dm_exec_describe_first_result_set('YOUR_PROCEDURE_NAME_HERE', NULL, 1);


SELECT 
    @CREATE_TABLE_QUERY = N'CREATE TABLE TABLE_NAME_HERE(' + CHAR(13) + CHAR(10) + CHAR(9) + STUFF(@CREATE_TABLE_QUERY, 1, 1, N'') + ');';

PRINT @CREATE_TABLE_QUERY;

Note: Replace 'YOUR_PROCEDURE_NAME_HERE' with the name of your own stored procedure.

Note: Replace TABLE_NAME_HERE with the table name of your choice.

The above will generate something like this:

CREATE TABLE TABLE_NAME_HERE(
     WeekName VARCHAR(40)
    , Line Name VARCHAR(50)
    , TheDate DATETIME
    , ReceivedAll INT
    , Answered INT
    , Abandoned INT
    , Call Length INT
    , WaitTimeAnswer INT
    , WaitTimeAbandon INT
    , PeriodName VARCHAR(10)
    , Week SMALLINT
    , Period SMALLINT
    , Year SMALLINT
    , WeekInPeriod SMALLINT
    , NumWeeksInPeriod SMALLINT
    , WeekendDate DATETIME
    , CRCOperative VARCHAR(100)
    , CallType VARCHAR(20)
    , Charge Time INT
    , SourceNumber VARCHAR(80)
    , DestinationNumber VARCHAR(80)
    , CallStart DATETIME
    , Out of Hours VARCHAR(12)
    , IsWorkingDay BIT
    );

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26343

This is a bit of a hack, but you could try selecting into a table (see line #3):

SELECT p.pat_id, pat_name, 
   enc_id, admsn_time, disch_time
INTO delete_me
FROM   patient p
INNER JOIN encounter e ON p.pat_id=e.pat_id
WHERE  admsn_time >= '01/01/2014'

Then you can highlight the delete_me table in SSMS, right-click, and generate the CREATE TABLE script.

Finally, you'd want to DROP TABLE delete_me to clean up.

Upvotes: 3

A ツ
A ツ

Reputation: 1267

use

SELECT p.pat_id, pat_name, 
       enc_id, admsn_time, disch_time
into #randomtable
FROM   patient p
INNER JOIN encounter e ON p.pat_id=e.pat_id
WHERE  admsn_time >= '01/01/2014'

and your table will be created when you execute the statement.

Upvotes: 2

Related Questions