从没有数据的另一个表的结构创建表

How can this be achieved in SQL (MS SQL)

如何在SQL（MS SQL）中实现这一点

Thanks

谢谢

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

好的：让我清楚一下，尝试实现的是使用EXCEPT子句生成动态select语句。

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

例如：从@table中选择col1，col2，col3，除了从@table中选择col1，col2，col2

so my resultset will be always different based on @table

所以我的结果集总是基于@table而不同

futher down i want to use this @table in CTE

进一步下来我想在CTE中使用这个@table

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

with filteredData as（从temptable中选择col1，col2 - （上面创建））

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

希望最后一行的结果集成为temptable

thanks

谢谢

4 个解决方案

#1

I've used this answer / script before.

我以前用过这个答案/剧本。

#2

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.

这将复制结构，但不会复制约束等。如果你想要一切，最好的办法就是从SSMS生成脚本并更改表/约束/索引名称。

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

编辑：Re：“想要将最后一行的结果集转换为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)/

这会将结果集放入“真实”表中;换句话说，你必须使用全局临时表（只需将“MyTable”更改为“## MyTable”）。它不适用于本地临时表（“＃MyTable”），因为它的范围将在EXECUTE语句中，即在EXECUTE之后，您不能拥有随后查询本地临时表的代码，因为它不会存在于该范围内。因此，它需要是一个真实的表，或一个全局临时表（可以在当前范围之外访问）/

#3

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

打开管理工作室，右键单击表格并创建到 - >新建查询窗口。这将生成查询，以创建具有索引约束的表的精确副本等...

#4

SELECT *
INTO NewTable
FROM OriginalTable
WHERE 1 = 0

#1