Efficient bulk update and insert

README.md

Bulk insert or update CartoDB records

Use the SQL example here to efficiently update CartoDB tables. See both the INSERT and UPDATE variants

insert.sql

INSERT INTO counties_results (pres_dem_pct, pres_gop_pct, pres_oth_pct, pres_pctrpt, st_usps, fips)( VALUES
(48.51, 39.69, 11.8, 100, 'AK', '02000'),
(51.65, 41.67, 6.68, 100 , 'AL' , '01001' ),
(52.14, 42.4, 5.46, 100 , 'AL' , '01003' ),
(51.94, 42.24, 5.82, 100 , 'AL' , '01005' ),
(60.28, 34.95, 4.76, 100 , 'AL' , '01007' ),
(53.19, 42.04, 4.77, 100 , 'AL' , '01009' ),
(45.07, 50.85, 4.08, 100 , 'AL' , '01011' ),
(45.78, 49.06, 5.16, 100 , 'AL' , '01013' ),
(57.94, 36.59, 5.47, 100 , 'AL' , '01015' ),
(46.98, 48.19, 4.83, 100 , 'AL' , '01017' ),
(57.38, 37.44, 5.18, 100 , 'AL' , '01019' ),
(51.15, 43.66, 5.19, 100 , 'AL' , '01021' ),
(48.13, 47.03, 4.84, 100 , 'AL' , '01023' ),
(54.74, 40.23, 5.03, 100 , 'AL' , '01025' ),
(48.85, 45.55, 5.59, 100 , 'AL' , '01027' ),
(43.34, 52.17, 4.5, 100 , 'AL' , '01029' ),
(54.65, 40.71, 4.64, 100 , 'AL' , '01031' ),
(53.65, 41.07, 5.28, 100 , 'AL' , '01033' ),
(57.72, 36.74, 5.55, 100 , 'AL' , '01035' ),
(44.02, 52.32, 3.66, 100 , 'AL' , '01037' ),
(57.02, 38.25, 4.74, 100 , 'AL' , '01039' )
)

update.sql

UPDATE counties_results o SET pres_dem_pct = n.pres_dem_pct, pres_gop_pct = n.pres_gop_pct,
  pres_oth_pct = n.pres_oth_pct, pres_pctrpt = n.pres_pctrpt
FROM ( VALUES
(48.51, 39.69, 11.8, 100, 'AK', '02000'),
(51.65, 41.67, 6.68, 100 , 'AL' , '01001' ),
(52.14, 42.4, 5.46, 100 , 'AL' , '01003' ),
(51.94, 42.24, 5.82, 100 , 'AL' , '01005' ),
(60.28, 34.95, 4.76, 100 , 'AL' , '01007' ),
(53.19, 42.04, 4.77, 100 , 'AL' , '01009' ),
(45.07, 50.85, 4.08, 100 , 'AL' , '01011' ),
(45.78, 49.06, 5.16, 100 , 'AL' , '01013' ),
(57.94, 36.59, 5.47, 100 , 'AL' , '01015' ),
(46.98, 48.19, 4.83, 100 , 'AL' , '01017' ),
(57.38, 37.44, 5.18, 100 , 'AL' , '01019' ),
(51.15, 43.66, 5.19, 100 , 'AL' , '01021' ),
(48.13, 47.03, 4.84, 100 , 'AL' , '01023' ),
(54.74, 40.23, 5.03, 100 , 'AL' , '01025' ),
(48.85, 45.55, 5.59, 100 , 'AL' , '01027' ),
(43.34, 52.17, 4.5, 100 , 'AL' , '01029' ),
(54.65, 40.71, 4.64, 100 , 'AL' , '01031' ),
(53.65, 41.07, 5.28, 100 , 'AL' , '01033' ),
(57.72, 36.74, 5.55, 100 , 'AL' , '01035' ),
(44.02, 52.32, 3.66, 100 , 'AL' , '01037' ),
(57.02, 38.25, 4.74, 100 , 'AL' , '01039' )

) AS n(pres_dem_pct, pres_gop_pct, pres_oth_pct, pres_pctrpt, st_usps, fips)
 WHERE o.st_usps = n.st_usps AND o.fips = n.fips;