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: 354 | Views in the last 30 days: 55
 
Related Articles
SCRIPT

Generate Insert Statements

This script Generates the insert statements for a particular table.

ARTICLE

Generating Insert Statements

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

SCRIPT

MERGE Statement Generator

Generates the merge statement for a given table.

SCRIPT

Generate Repair Statements for Not-Trusted Foreign Keys

Generates "check this foreign key" statements for all not-trusted foreign keys on a server.

SCRIPT

Generate insert statements with column names for any or all tables

Generate a script of insert statements with column names for any table including those with identity...

Tags
schema    
temporary table    
 
Contribute