CODEWITHSUNDEEP
pythonsqlsql-serverpandassqlalchemy
Craig Cunningham
Craig Cunningham

Reputation: 85

SQL INSERT data from 2 tables

I have 2 tables, postcodelatlng and branch.

postcodelatlng

postcode lat lng
AB10 1XG 57.1441650 -2.1148480
AB10 6RN 57.1378800 -2.1214870
AB10 7JB 57.1242740 -2.1271900
AB10 5QN 57.1427010 -2.0932950
AB10 6UL 57.1375470 -2.1122330

branch

branch postcode
1 ZE2 9TL
4 BB1 7DJ
9 YO8 9DW

I'm trying to create a new table that for every postcode in branch, it lists every postcode in postcodelatlng.

New table

from to from_lat from_lng to_lat to_lng
ZE2 9TL AB10 1XG 60.4481370 -1.1943700 57.1441650 2.1148480
ZE2 9TL AB10 6RN 60.4481370 -1.1943700 57.1378800 -2.1214870
ZE2 9TL AB10 7JB 60.4481370 -1.1943700 57.1242740 -2.1271900
ZE2 9TL AB10 5QN 60.4481370 -1.1943700 57.1427010 -2.0932950
ZE2 9TL AB10 6UL 60.4481370 -1.1943700 57.1375470 -2.1122330
BB1 7DJ AB10 1XG 53.7490640 -2.4843190 57.1441650 2.1148480
BB1 7DJ AB10 6RN 53.7490640 -2.4843190 57.1378800 -2.1214870
BB1 7DJ AB10 7JB 53.7490640 -2.4843190 57.1242740 -2.1271900
BB1 7DJ AB10 5QN 53.7490640 -2.4843190 57.1427010 -2.0932950
BB1 7DJ AB10 6UL 53.7490640 -2.4843190 57.1375470 -2.1122330
YO8 9DW AB10 1XG 53.7743390 -1.0714240 57.1441650 2.1148480
YO8 9DW AB10 6RN 53.7743390 -1.0714240 57.1378800 -2.1214870
YO8 9DW AB10 7JB 53.7743390 -1.0714240 57.1242740 -2.1271900
YO8 9DW AB10 5QN 53.7743390 -1.0714240 57.1427010 -2.0932950
YO8 9DW AB10 6UL 53.7743390 -1.0714240 57.1375470 -2.1122330

I've tried doing this in Python with Pandas and SQLAlchemy but I can't get my head around that so I thought it may be easier to just do it in SQL, but I'm also stuck on that!

The lat/lng data is only held in the postcodelatlng but this can be added manually to the branch table (only 42 unique postcodes there(some branches share a postcode))

I have 120 records in branch, 42 unique postcodes and 1778786 records on postcodelatlng.

Upvotes: 0

Views: 67

Answers (1)

persian-theme
persian-theme

Reputation: 6638

With an unconditional join, the desired table was reached, but I do not know the values of from_lat and from_lng

insert into newtable
select b.postcode,p.postcode,"from_lat = ?","from_lng = ?",p.lat,p.lng
from branch b join postcodelatlng p on 1 = 1

or cross join

insert into newtable
select b.postcode,p.postcode,"from_lat = ?","from_lng = ?",p.lat,p.lng
from branch b cross join postcodelatlng p

Upvotes: 2

Related Questions