从多个表中选择并组合成单行临时表

Question

我有三个使用相同结构的表(FuelRates_Petrol、FuelRates_LPG和FuelRates_Diesel)：

| OrganisationID | Band1 | Band2 | Band3 | ModifiedDate        |
|----------------|-------|-------|-------|---------------------|
| 1              | 10    | 12    | 19    | 01/05/2016 19:23:21 |
| 2              | 11    | 12    | 20    | 02/05/2016 19:23:21 |

我需要创建一个存储过程，使用匹配的OrganisationID从每个表中选择记录。每个表中将有一个匹配的记录。

然后，我需要将结果组合成一个具有以下结构的临时表并返回它：

| OrganisationID | Petrol_Band1 | Petrol_Band2 | Petrol_Band3 | LPG_Band1 | LPG_Band2 | LPG_Band3 | Diesel_Band1 | Diesel_Band2 | Diesel_Band3 | ModifiedDate        |
|----------------|--------------|--------------|--------------|-----------|-----------|-----------|--------------|--------------|--------------|---------------------|
| 1              | 10           | 12           | 19           | 10        | 12        | 19        | 10           | 12           | 19           | 01/05/2016 19:23:21 |

应该将来自Band1表的FuelRates_Petrol列插入到FuelRates临时表的Petrol_Band1列中。其余的列应该遵循这个约定来创建一个行，如上面所示。

SQL实际上不是我的包，但这是我的努力：

ALTER PROCEDURE [dbo].[FuelRates_RetrieveList] 
    @OrganisationID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    CREATE TABLE #FuelRates(
    OrganisationID int,
    Petrol_Band1 int, 
    Petrol_Band2 int,
    Petrol_Band3 int,
    LPG_Band1 int,
    LPG_Band2 int,
    LPG_Band3 int,
    Diesel_Band1 int,
    Diesel_Band2 int,
    Diesel_Band3 int,
    ModifiedDate DateTime)

    SELECT Band1 As Petrol_Band1, Band2 As Petrol_Band2, Band3 As Petrol_Band3, ModifiedDate, OrganisationID FROM [FuelRates_Petrol]
    WHERE OrganisationID = @OrganisationID
    INSERT INTO #FuelRates

    SELECT Band1 As LPG_Band1, Band2 As LPG_Band2, Band3 As LPG_Band3 FROM [FuelRates_LPG]
    WHERE OrganisationID = @OrganisationID
    INSERT INTO #FuelRates

    SELECT Band1 As Diesel_Band1, Band2 As Diesel_Band2, Band3 As Diesel_Band3 FROM [FuelRates_Diesel]
    WHERE OrganisationID = @OrganisationID
    INSERT INTO #FuelRates

    SELECT * FROM #FuelRates

    DROP TABLE #FuelRates
END

当我执行此操作时，会得到以下错误：

Msg 213，级别16，状态1，过程FuelRates_RetrieveList，第33行插入错误:列名或提供的值数与表定义不匹配。

第33行是临时表create块中的Diesel_Band1 int,。

我正在使用Server 2005。

是什么导致了这个错误，我是否以正确的方式去做这件事？

Answer 1

你可以试试这个。

ALTER PROCEDURE [dbo].[FuelRates_RetrieveList] 
    @OrganisationID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    CREATE TABLE #FuelRates(
    OrganisationID int,
    Petrol_Band1 int, 
    Petrol_Band2 int,
    Petrol_Band3 int,
    LPG_Band1 int,
    LPG_Band2 int,
    LPG_Band3 int,
    Diesel_Band1 int,
    Diesel_Band2 int,
    Diesel_Band3 int,
    ModifiedDate DateTime)

    INSERT INTO #FuelRates
    (OrganisationID, Petrol_Band1, Petrol_Band2, Petrol_Band3,LPG_Band1,LPG_Band2,LPG_Band3,
      Diesel_Band1, Diesel_Band2, Diesel_Band3, ModifiedDate)
    SELECT OrganisationID,
        p.Band1 As Petrol_Band1, p.Band2 As Petrol_Band2, p,Band3 As Petrol_Band3, 
        lp.Band1 As LPG_Band1, lp.Band2 As LPG_Band2, lp.Band3 As LPG_Band3,
        d.Band1 As Diesel_Band1, d.Band2 As Diesel_Band2, d.Band3 As Diesel_Band3,
        GetDate() as ModifiedDate
    FROM [FuelRates_Petrol] p
    LEFT JOIN FuelRates_LPG lp on lp.OrganisationID = p.OrganisationID
    LEFT JOIN FuelRates_Diesel d on d.OrganizationID = p.OrganisationID
    WHERE p.OrganisationID = @OrganisationID

    SELECT * FROM #FuelRates

    DROP TABLE #FuelRates
END

Answer 2

我知道它说错误出现在第33行，您已经将其归因于Diesel_Band1，但在此之后的消息是：

插入错误:列名或提供的值数与表定义不匹配。

Server用于定义表的INSERT方法是首先放置insert语句。

当您没有以与定义的表完全相同的顺序插入列的确切数量时，您需要定义列和顺序。

INSERT INTO myTable (column1, column2, column3,...columnN)

将插入内容更改为以下内容：

INSERT INTO #FuelRates
    (Petrol_Band1, Petrol_Band2, Petrol_Band3, ModifiedDate, OrganisationID)
    SELECT Band1 As Petrol_Band1, Band2 As Petrol_Band2, Band3 As Petrol_Band3, ModifiedDate, OrganisationID 
    FROM [FuelRates_Petrol]
    WHERE OrganisationID = @OrganisationID