First of all, this is not really a good practice to do in your production environment but this is a script i created which allows you to create a composite table from a temporary table
This has quite a few limitations but generally gives you the basic structure of the table you want without keys, indexes, null contraints etc
This has been created to work with SQL Server 2012, if you need to make it work for older versions you will need to update the CONCAT function to use the old string concatenation
Replace your table names with the ones in the script
Heres the script
This has quite a few limitations but generally gives you the basic structure of the table you want without keys, indexes, null contraints etc
This has been created to work with SQL Server 2012, if you need to make it work for older versions you will need to update the CONCAT function to use the old string concatenation
Replace your table names with the ones in the script
Heres the script
/*
Intialise variables and set table names you want to create a table from
*/
DECLARE @columns VARCHAR(max)
DECLARE @createsql nVARCHAR(max)
DECLARE @tempTableName sysname
DECLARE @newTableName sysname
DECLARE @columnIterator int
DECLARE @columnName sysname
DECLARE @columnType VARCHAR(13)
SET @tempTableName = '#temp'
SET @newTableName = 'MYNEWTABLE'
/*
Check the temp table exists
*/
IF OBJECT_ID(CONCAT('tempdb.dbo.',@tempTableName)) IS NULL
BEGIN
PRINT 'Temp table does not exist'
RETURN
END
/*
Table variable to hold the columns
*/
DECLARE @cols TABLE ([TABLE_QUALIFIER] sysname, [TABLE_OWNER] sysname, [TABLE_NAME] sysname, [COLUMN_NAME] sysname, [DATA_TYPE] smallint,
[TYPE_NAME] varchar(13), [PRECISION] int, [LENGTH] int, [SCALE] smallint, [RADIX] smallint, [NULLABLE] smallint,
[REMARKS] varchar(254), [COLUMN_DEF] nvarchar(4000), [SQL_DATA_TYPE] smallint, [SQL_DATETIME_SUB] smallint,
[CHAR_OCTET_LENGTH] int, [ORDINAL_POSITION] int, [IS_NULLABLE] varchar(254), [SS_DATA_TYPE] TINYINT
)
INSERT INTO @cols
EXEC tempdb..sp_columns @table_name = @tempTableName
/*
build the create table statement using the columns we know about
*/
SELECT TOP 1 @columnIterator = ORDINAL_POSITION,@columnName = COLUMN_NAME, @columnType = TYPE_NAME
from @cols
ORDER BY ORDINAL_POSITION
WHILE 1=1
BEGIN
SET @columns = CONCAT(@columns,' ',@columnName,' ', @columnType, ', ')
SELECT TOP 1 @columnIterator = ORDINAL_POSITION,@columnName = COLUMN_NAME, @columnType = TYPE_NAME
from @cols
WHERE @columnIterator < ORDINAL_POSITION
ORDER BY ORDINAL_POSITION
IF @@ROWCOUNT = 0
BREAK
END
SET @columns = left(@columns,LEN(@columns)-1)
SET @createsql = CONCAT('CREATE TABLE ', @newTableName, '( ',@columns,') ')
/*
Create the table
*/
EXEC sp_executeSQL @statement = @createsql
go
