CODEWITHSUNDEEP
sqlsql-server
Sreedhar
Sreedhar

Reputation: 30045

Creating table from structure of another table with no data

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

Upvotes: 5

Views: 7544

Answers (4)

AdaTheDev
AdaTheDev

Reputation: 147374

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)/

Upvotes: 7

Richard Harrison
Richard Harrison

Reputation: 19403

I've used this answer / script before.

Upvotes: 1

John Sansom
John Sansom

Reputation: 41899

SELECT *
INTO NewTable
FROM OriginalTable
WHERE 1 = 0

Upvotes: 1

anthares
anthares

Reputation: 11223

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...

Upvotes: 4

Related Questions