SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Get Temp Table definition

By Wes Henriksen,

Generate a temp table using Select * INTO #[table].  Once the temp table has been created, execute this statement within the same session (tab in SSMS).  The output will have a row per line definition of the table, in column order, that can be used to explicitly declare the temp table.

I've used this in the past when I wanted to generate a temp table manually but do not want to type up the statements, or don't know what the final data types will be.  With this, I can simply identify the data I need, insert it using INTO #temp, and then execute this to generate the create statement I need.

I wrote this a few years ago so I'm sure someone can improve on it, but figured it was worth sharing anyway.  It may not work for all column data types as I've only included the ones I've needed so far.

Total article views: 487 | Views in the last 30 days: 13
Related Articles

Generate Insert Statements

This script Generates the insert statements for a particular table.


Generating Insert Statements

This article from new author Oleg Netchaev describes the cursor-less script used to generate insert ...


MERGE Statement Generator

Generates the merge statement for a given table.


Generating Insert Statements

G’day, I been asked a few times lately the best way to generate INSERT statements. I’d always repl...


Generate RESTORE LOG statements

Today, we're going to take a specific, simple example of generating RESTORE LOG statements from a di...

temporary table