Creating table from structure of another table with no data

Question

How can this be achieved in SQL (MS SQL)

Thanks

OK: let me bit clear what am trying to acheive is generating dynamic select statement with EXCEPT clause.

eg: select col1,col2,col3 from @table except select col1,col2,col2 from @table

so my resultset will be always different based on @table

futher down i want to use this @table in CTE

with filteredData as ( select col1, col2 from temptable --(created above) )

below is the code so far:

    DECLARE @TABLENAME VARCHAR(200) = 'SERVICE_DELIVERY_LOCATION';
DECLARE @COLCOUNT INT ;
DECLARE @TEMPCOLNAME VARCHAR(500) ;
DECLARE @SELECTCOLUMNS VARCHAR(5000)='';
DECLARE @EXCEPTSTATEMENT VARCHAR(5000)='' ;

---------------------------------------------------------------------------------------------------
--CASE: GET COMMON_COLUMNS COLUMNS OF SOURCE AND DESTINATION TABLE
---------------------------------------------------------------------------------------------------
DECLARE @COMMON_COLUMNS TABLE( COLUMN_NAME VARCHAR(500))
INSERT  INTO @COMMON_COLUMNS 
    SELECT  SOURCE.COLUMN_NAME FROM   SCD_SOURCE.INFORMATION_SCHEMA.COLUMNS SOURCE
    INNER JOIN SCD_DESTI.INFORMATION_SCHEMA.COLUMNS DESTI ON SOURCE.COLUMN_NAME = DESTI.COLUMN_NAME


---------------------------------------------------------------------------------------------------
--CASE: GET PK COLUMNS OF SOURCE TO MAP TO DESTINATION IN CASE WHERE NEED TO DO UPDATES
---------------------------------------------------------------------------------------------------
DECLARE @PK_COLUMNS TABLE ( PK_COLUMN VARCHAR(500))
INSERT INTO @PK_COLUMNS
    SELECT KCU.COLUMN_NAME
    FROM    SCD_SOURCE.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC  
    JOIN    SCD_SOURCE.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA  
        AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME  
        AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA  
        AND KCU.TABLE_NAME = TC.TABLE_NAME 
        AND KCU.COLUMN_NAME !=  'CREATE_DATA_CONTAINER_ID'
    WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY') 


SELECT  @COLCOUNT = COUNT(*) FROM @COMMON_COLUMNS

WHILE ( @COLCOUNT != 0 )
    BEGIN
        SET @TEMPCOLNAME = (SELECT TOP 1 COLUMN_NAME FROM @COMMON_COLUMNS)
        SET @SELECTCOLUMNS = @SELECTCOLUMNS + @TEMPCOLNAME + ', '
        DELETE FROM @COMMON_COLUMNS WHERE COLUMN_NAME = @TEMPCOLNAME
        SELECT  @COLCOUNT = COUNT(*) FROM @COMMON_COLUMNS
    END

SET @SELECTCOLUMNS = SUBSTRING(@SELECTCOLUMNS, 1, LEN(@SELECTCOLUMNS) - 1)

SET @EXCEPTSTATEMENT = 'SELECT ' + @SELECTCOLUMNS + ' FROM SCD_SOURCE.DBO.' + @TABLENAME + ' EXCEPT SELECT ' + @SELECTCOLUMNS + ' FROM SCD_DESTI.DBO.' + @TABLENAME
EXEC(@EXCEPTSTATEMENT)

want the resultset of last line into temptable

thanks

Does a program have to create new tables with the same schema? If it is done by a human anthares is correct. — awright18, Feb 19 '10 at 13:03

AdaTheDev · Answer 1 · 2010-02-19T12:48:22.957

SELECT TOP 0 *
INTO NewTable
FROM OriginalTable

This will copy the structure, but won't copy constraints etc. If you want everything, best thing to do is just generate the script from SSMS and change the table/constraint/index names.

Edit: Re: "want the resultset of last line into temptable"

You could change the last 2 lines to:

SET @EXCEPTSTATEMENT = 'SELECT * INTO MyNewTable FROM (SELECT ' + @SELECTCOLUMNS + ' FROM SCD_SOURCE.DBO.' + @TABLENAME + ' EXCEPT SELECT ' + @SELECTCOLUMNS + ' FROM SCD_DESTI.DBO.' + @TABLENAME + ') x'
EXECUTE(@EXCEPTSTATEMENT)

This would put the resultset into a "real" table; alterneratively you'd have to use a global temporary table (just change "MyTable" to "##MyTable"). It wouldn't work with a local temporary table ("#MyTable") as the scope of that would be within the EXECUTE statement i.e. after the EXECUTE, you couldn't have code that then queried the local temporary table as it won't exist in that scope. Hence, it would need to be a real table, or a global temporary table (which would be accessible outside of the current scope)/

Thanks for the correction, the exact statement is like: "select * into MyNewTableName from TheOldTable where 1=2;" - tried and tested it on SQL Server 2005. Copies schema without constraints. — KMån, Feb 19 '10 at 13:11

score 4 · Answer 2 · answered Feb 19 '10 at 11:13

4

Open your managment studio, right click to your table and create to -> New Query window. This will generate the query for creating exact copy of your table with indexes constraints etc...

answered Feb 19 '10 at 11:13

anthares

11,070
4
41
61

score 1 · Answer 3 · answered Feb 19 '10 at 11:18

1

SELECT *
INTO NewTable
FROM OriginalTable
WHERE 1 = 0

answered Feb 19 '10 at 11:18

John Sansom

41,005
9
72
84

score 1 · Accepted Answer · edited May 23 '17 at 10:24

1

I've used this answer / script before.

edited May 23 '17 at 10:24

Community

1
1

answered Feb 19 '10 at 11:27

Richard Harrison

19,247
4
40
67

Creating table from structure of another table with no data

4 Answers4