CODEWITHSUNDEEP
sqlsql-insertgreenplum
Антон Букреев
Антон Букреев

Reputation: 400

Greenplum: copy data between tables

I'm need copy data from table1 and table 2 in table3 I'ms try execute this request: 

WITH sel AS (
    SELECT 
        er.local_company_pk as "local_company_pk",
        dv.load_dtm as "load_dtm",
        dv.src as "rec_src",
        dv.companyogrn as "ogrn",
        dv."CompanyID" as "company_spark_id",
        dv.companyvat as "company_vat",
        dv."CompanyCountryCode" as "company_country_code",
        dv."CompanyShortName" as "company_short_name",
        dv."CompanyFullName" as "company_full_name",
        dv."CompanySize" as "company_size",
        dv."CompanyRevenue" as "company_revenue",
        dv."StaffQuantity" as "staff_quantity",
        dv."CompanyRegionName" as "company_region_name",
        dv."CompanyCityName" as "company_city_name",
        dv."RegistrationDT" as "registration_dt",
        dv."EndDT" as "end_dt",
        dv."CompanyStatus" as "company_status",
        dv."FailureIndex" as "failure_index",
        dv."IndexOfDueDiligence" as "index_of_due_diligence",
        dv."ConsolidateIndex" as "consolidate_index",
        ' ' as "hash_diff"
    FROM
        table1 as dv,
        table2 as er
    WHERE 
        er.inn = dv.companyvat
    LIMIT 10
)
INSERT INTO
    table3
SELECT * 
FROM sel

And I have error SQL Error [42601]: ERROR: syntax error at or near "INSERT"

How I can insert into table3 from select between table1 and table2?

Upvotes: 0

Views: 148

Answers (2)

Jim
Jim

Reputation: 26

How is registration_dt defined in table3? If it is a date already, there is no need to cast it in your SELECT. The error message indicates it is defined as a date.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Why use a CTE?

INSERT INTO table3
   SELECT 
        er.local_company_pk as "local_company_pk",
        dv.load_dtm as "load_dtm",
        dv.src as "rec_src",
        dv.companyogrn as "ogrn",
        dv."CompanyID" as "company_spark_id",
        dv.companyvat as "company_vat",
        dv."CompanyCountryCode" as "company_country_code",
        dv."CompanyShortName" as "company_short_name",
        dv."CompanyFullName" as "company_full_name",
        dv."CompanySize" as "company_size",
        dv."CompanyRevenue" as "company_revenue",
        dv."StaffQuantity" as "staff_quantity",
        dv."CompanyRegionName" as "company_region_name",
        dv."CompanyCityName" as "company_city_name",
        dv."RegistrationDT"::text as "registration_dt",
---------------------------^ you can cast the value
        dv."EndDT" as "end_dt",
        dv."CompanyStatus" as "company_status",
        dv."FailureIndex" as "failure_index",
        dv."IndexOfDueDiligence" as "index_of_due_diligence",
        dv."ConsolidateIndex" as "consolidate_index",
        ' ' as hash_diff
    FROM table1 as dv JOIN
         table2 as er
         ON er.inn = dv.companyvat
    LIMIT 10

I also fixed your broken JOIN syntax.

Upvotes: 1

Related Questions