CODEWITHSUNDEEP
sql
user1949277
user1949277

Reputation: 11

Creating a table from a query in SQL

I'm pretty new to SQL and I'm trying to create a table from a query. However, I'm running into some errors when I try to execute the query.

I'm trying to create a table called "CUM_PRD" with the following columns:

Gov_fld_nm,
Gov_pol_nm,
Oil_pd,
Gas_pd,
Water_pd.

The values that should be populated with this query is from the query below. 

SELECT GOV_FLD_NM,
SUM (OIL_PD),
SUM (GAS_PD),
SUM (WAT_PD)
FROM PRE_PRD
GROUP BY GOV_FLD_NM

This query works fine when it is all by itself, but when I try to include it in the create table query, it does not work. I believe I'm not using the correct syntax.

Could someone help?

This is what I tried to use.

CREATE TABLE CUM_PRD
(
    GOV_FLD_NM NVARCHAR (50),
    OIL_PD INT,
    GAS_PD INT,
    WAT_PD INT
)
INSERT INTO CUM_PRD
SELECT (
    GOV_FLD_NM,
    SUM (OIL_PD),
    SUM (GAS_PD),
    SUM (WAT_PD)
)
FROM PRE_PRD
GROUP BY GOV_FLD_NM

Upvotes: 1

Views: 132

Answers (3)

DNax
DNax

Reputation: 1433

You should use the CREATE TABLE table_name AS syntax

CREATE TABLE CUM_PRD
AS
(
SELECT GOV_FLD_NM, SUM (OIL_PD) OIL_PD_SUM, SUM (GAS_PD) GAS_PD_SUM, SUM (WAT_PD) WAT_PD_SUM
FROM PRE_PRD
GROUP BY GOV_FLD_NM)

Upvotes: 0

amphibient
amphibient

Reputation: 31238

I would just do

SELECT GOV_FLD_NM, SUM (OIL_PD), SUM (GAS_PD), SUM (WAT_PD) 
INTO CUM_PRD 
FROM PRE_PRD 
GROUP BY GOV_FLD_NM;

SELECT INTO creates a table for you

Upvotes: 0

Taryn
Taryn

Reputation: 247690

If you want to create a temp table you can use SELECT ... INTO...:

SELECT GOV_FLD_NM, SUM (OIL_PD), SUM (GAS_PD), SUM (WAT_PD) 
INTO #CUM_PRD
FROM PRE_PRD 
GROUP BY GOV_FLD_NM

Since your script is not creating a temp table why not just create the table first, then insert into it:

CREATE TABLE CUM_PRD 
( 
  GOV_FLD_NM NVARCHAR (50), 
  OIL_PD INT,
  GAS_PD INT, 
  WAT_PD INT 
);

Then insert the data:

INSERT INTO CUM_PRD (GOV_FLD_NM, OIL_PD, GAS_PD, WAT_PD)
SELECT GOV_FLD_NM, SUM (OIL_PD), SUM (GAS_PD), SUM (WAT_PD) 
FROM PRE_PRD 
GROUP BY GOV_FLD_NM

Upvotes: 2

Related Questions