根据查询结果创建表

Question

我在创建正确的SQL语句时确实遇到了问题。

场景，我确实有一个表"Excel“，它被SQLBulkCopy填充了几次，在这个表中包含了我想要通过"Buildneed”求和的重复项之后，我已经想出了用下面的查询返回“整合”结果的方法：

SELECT GBC, Description, sum(Buildneed) as Buildneed, Replaced 
FROM Excel 
GROUP BY GBC, Description, Replaced 
ORDER BY GBC ASC

现在我想删除NewTable，如果存在，重新创建NewTable，并从上面提到的结果填充这个表。

所以我写了这样一句话：

DROP TABLE IF EXISTS dbo.NewTable

CREATE TABLE [dbo].[NewTable] 
(
    [GBC]         INT          NULL,
    [Description] VARCHAR (80) NULL,
    [Buildneed]   INT          NULL,
    [Replaced]    VARCHAR (80) NULL
);

SELECT Excel2.GBC, Excel2.Description, Excel2.Buildneed, Excel2.Replaced 
INTO NewTable 
FROM (SELECT GBC, Description, sum(Buildneed) as Buildneed, Replaced 
      FROM Excel 
      GROUP BY GBC, Description, Replaced 
      ORDER BY GBC ASC) AS Excel2

我没有收到任何错误信息，在运行上述查询后没有创建表。

每次有新数据进入"Excel“表时，我都希望"consolidate -> drop NewTable -> create NewTable with new data”。

示例数据：

CREATE TABLE [dbo].[Excel] 
(
        [GBC]         INT          NULL,
        [Description] VARCHAR (80) NULL,
        [Buildneed]   INT          NULL,
        [Replaced]    VARCHAR (80) NULL
    );

INSERT INTO @Excel (GBC, [Description], Buildneed, Replaced)
SELECT 71744, 'RES_TF,10k,0402,1%,0,1W,100PPM/C', 2000
UNION ALL
SELECT 71744, 'RES_TF,10k,0402,1%,0,1W,100PPM/C', 1000
UNION ALL
SELECT 76527, 'CAP_CER,10nF,0402,10%,50V,X7R', 288
UNION ALL
SELECT 86911, 'CAP_CER,10nF,0603,10%,100V,X7R', 1464

预期结果->使用名称"NewTable“创建的新表

GBC  / Description / Buildneed / Replaced
71744 / RES_TF,10k,0402,1%,0,1W,100PPM/C / **3000** / null
76527 / CAP_CER,10nF,0402,10%,50V,X7R / 288 / null
86911 / CAP_CER,10nF,0603,10%,100V,X7R / 1463 / null

Answer 1

首先，我建议使用另一个名称创建新表，在成功创建新表后删除旧表，然后将新表重命名为所需的名称-这样可以防止数据丢失，以防表在创建过程中无法正常工作。此外，SELECT ...INTO应该创建一个新的表，而不需要先创建它。另一种可能是插入到...选择...

下面是一个示例：

CREATE TABLE [dbo].[Excel] (
        [GBC]         INT          NULL,
        [Description] VARCHAR (80) NULL,
        [Buildneed]   INT          NULL,
        [Replaced]    VARCHAR (80) NULL
    );


insert into [Excel] (GBC, [Description], Buildneed, Replaced)
select 71744, 'RES_TF,10k,0402,1%,0,1W,100PPM/C' ,2000, NULL
union all
select 71744, 'RES_TF,10k,0402,1%,0,1W,100PPM/C' ,1000, NULL
union all
select 76527, 'CAP_CER,10nF,0402,10%,50V,X7R' ,288, NULL
union all
select 86911, 'CAP_CER,10nF,0603,10%,100V,X7R' ,1464, NULL;

SELECT Excel2.GBC, Excel2.Description, Excel2.Buildneed, Excel2.Replaced
  INTO NewTable
  FROM (SELECT GBC, Description, sum(Buildneed) as Buildneed, Replaced FROM Excel GROUP BY GBC, Description, Replaced) as Excel2;

SELECT *
  FROM NewTable

有关示例，请参阅SQL文件管理器: sqlfiddle.com/#!18/d568e/5/2