Turnip Turnip - 7 months ago 17
SQL Question

Select from multiple tables and combine into single row of temp table

I have three tables (

FuelRates_Petrol
,
FuelRates_LPG
and
FuelRates_Diesel
) that use the same structure:

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


I need to create a stored procedure that will select the record from each table with a matching
OrganisationID
. There will be one matching record in each table.

I then need to combine the results into a temporary table with the following structure and return it:

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


The
Band1
column from the
FuelRates_Petrol
table should be inserted into the
Petrol_Band1
column of the
FuelRates
temporary table. The rest of the columns should follow this convention to create a single row as shown above.

SQL isn't really my bag but this was my effort:

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


When I execute this I get the following error:


Msg 213, Level 16, State 1, Procedure FuelRates_RetrieveList, Line 33
Insert Error: Column name or number of supplied values does not match
table definition.


Line 33 is
Diesel_Band1 int,
in the temp table create block.

I'm using SQL Server 2005.

What is causing this error and am I even going about this in the correct way?

Answer

You can try this ...

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