I am trying to get my CountryID field in tblCrater to match my other CountryID field in tblCountry based on the Country Name fields I have in both tables. It probably is a simple answer, but I cannot find the solution anywhere. I do not want a temporary join, and thanks for taking the time to assist, as I am an amateur at SQL at the moment.
Much Appreciated, and let me know if I need to clarify more.
USE master
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name='DeepImpact2')
DROP DATABASE DeepImpact
CREATE DATABASE DeepImpact
GO
USE DeepImpact
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'yourTABLEname' AND TABLE_SCHEMA = 'dbo')
DROP TABLE dbo.tblCountry, dbo.tblCrater;
GO
CREATE TABLE tblCountry
(
CountryID INT IDENTITY(1,1)NOT NULL,
CountryName VARCHAR(MAX) NULL,
CountryCode VARCHAR(5) NULL,
)
CREATE TABLE tblCrater
(
CraterID INT IDENTITY(1,1) NOT NULL,
CraterName VARCHAR(MAX) NULL,
CountryID INT NULL,
Diameter DECIMAL(8,3)NULL,
Age INT NULL,
CountryName VARCHAR(MAX) NULL,
Lattitude DECIMAL(9,6) NULL,
Longitude DECIMAL(9,6) NULL,
CraterRegion VARCHAR(MAX) NULL
)
GO
ALTER TABLE tblCountry ADD PRIMARY KEY (CountryID)
ALTER TABLE tblCrater ADD PRIMARY KEY (CraterID)
GO
ALTER TABLE tblCrater
ADD FOREIGN KEY (CountryID)
REFERENCES tblCountry (CountryID)
GO
INSERT INTO tblCrater (CraterName, CraterRegion, CountryName, Diameter, Lattitude, Longitude, Age)
VALUES
('Boxhole','Northern Territory','Australia',0.2,-22.6166666666667,135.2,-3400),
('Campo del Cielo','Chaco','Argentina',0.1,-27.6333333333333,-61.7,-2000),
('Henbury','Northern Territory','Australia',0.2,-24.5666666666667,133.133333333333,-2200),
('Kaali','Saaremaa','Estonia',0.1,58.4,22.6666666666667,-1500),
('Macha','Sakha Republic','Russia', 0.3,60.1,117.583333333333,-5300),
('Morasko','Stare Miasto','Poland',0.1, 52.4833333333333,16.9,-3000),
('Rio Cuarto','Cordoba Province','Argentina',4.5,-32.8783333333333,-64.2233333333333,-8000),
('Wabar','Rub al Khali desert','Saudi Arabia',0.1,21.5,50.4666666666667,-1800),
('Tenoumer','Sahara Desert','Mauritania',1.9,22.9166666666667,-10.4083333333333,-19000),
('Meteor Crater','Arizona','United States',1.2,35.0333333333333,-111.016666666667,-47000),
('Xiuyan','Xiuyan','China',1.8, 40.35, 123.45,-48000),
('Lonar','Maharashtra','India',1.8,19.9666666666667,76.5166666666667,-50000),
('Agoudal','Atlas Mountains','Morocco',3.0,31.9833333333333,-5.5,-103000),
('Tswaing','Pretoria Saltpan','South Africa',1.1,-25.4,28.0816666666667,-218000),
('Zhamanshin','Kazakhstan','Kazakhstan',14.0,48.4,60.9666666666667,-948000),
('Bosumtwi','Ashanti','Ghana',10.0,6.505,-1.40833333333333,-1100000),
('Elgygytgyn','Chukotka Autonomous','Russia',18.0,67.5,172,-3500000),
('Bigach','Kazakhstan','Kazakhstan',8.0,48.5666666666667,82.0166666666667,-5000000),
('Karla','Tatarstan','Russia',10.0,54.9166666666667,48.0333333333333,-5000000),
('Karakul','Pamir Mountains','Tajikistan',52.0,39.0166666666667,73.45,-5000000),
('Vredefort','Free State','South Africa',300.0,-27,27.5,-2032000000),
('Sudbury','Ontario','Canada',250.0,46.6,-81.1833333333333,-1849000000),
('Chicxulub','Yucatan','Mexico',180.0,21.3333333333333,-89.5,-66000000),
('Popigai','Siberia','Russia',100.0,71.65,111.183333333333,-35000000),
('Manicouagan','Quebec','Canada',90,51.3833333333333,-68.7,-215000000),
('Acraman South','Australia','Australia',90.0,-32.0166666666667,135.45,-580000000),
('Morokweng','Kalahari Desert','South Africa',70.0,-26.4666666666667,23.5333333333333,-145000000),
('Kara','Nenetsia','Russia',65,69.1,64.15,-70000000),
('Beaverhead','Idaho and Montana','United States',60.0,44.25,114.00,-600000000),
('Tookoonooka','Queensland','Australia',55.0,-27.1166666666667,142.833333333333,-120000000),
('Charlevoix','Quebec','Canada',54.0,47.5333333333333,-70.3,-314000000),
('Siljan Ring','Dalarna','Sweden',52.0,61.0333333333333,14.8666666666667,-377000000),
('Karakul','Pamir Mountains','Tajikistan',52.0,39.0166666666667,73.45,-15000000),
('Montagnais','Nova Scotia','Canada',45.0,42.8833333333333,-64.2166666666667,-50500000),
('Araguainha','Central Brazil','Brazil',40.0,-16.7833333333333,-52.9833333333333,-244400000),
('Chesapeake Bay','Virginia','United States',40.0,37.2833333333333,-76.0166666666667,-35000000),
('Mjølnir','Barents Sea','Norway',40.0, 3.8,29.6666666666667,-142000000),
('Puchezh-Katunki','Nizhny Novgorod Oblast','Russia',40.0,56.9666666666667,43.7166666666667,-167000000),
('Saint Martin','Manitoba','Canada',40.0,51.7833333333333,-98.5333333333333,-227000000),
('Woodleigh','Western Australia','Australia',40.0,-26.05,114.666666666667,-364000000),
('Carswell','Saskatchewan','Canada',39.0,58.45,-109.5,-115000000),
('Clearwater','West Quebec','Canada',36.0,56.2166666666667,-74.5,-290000000),
('Manson','Iowa','United States',35.0,42.5833333333333,-94.55,-74000000),
('Slate Islands','Ontario','Canada',30.0,48.6666666666667,-87.00,-450000000),
('Yarrabubba','Western','Australia',30.0,-27.1666666666667,118.833333333333,-1800000000),
('Keurusselk','Western Finland','Finland',30.0,62.1333333333333,24.6,-1450000000),
('Shoemaker','Western Australia','Australia',30.0,-25.8666666666667,120.883333333333,-1630000000),
('Mistastin','Newfoundland','Canada',28.0,55.8833333333333,-63.3,-36400000),
('Clearwater','East Quebec','Canada',26.0,56.0666666666667,-74.1,-290000000),
('Kamensk','Southern Federal Dist','Russia',25.0,48.35,40.50,-49000000),
('Steen River','Alberta','Canada',25.0,59.5,-117.633333333333,-91000000),
('Strangways','Northern Territory','Australia',25.0,-15.2,133.583333333333,-646000000),
('Tunnunik','Northwest Territories','Canada',25.0,72.4666666666667,-113.966666666667,-280000000),
('Boltysh','Kirovohrad Oblast','Ukraine',24.0,48.9,32.25,-65170000),
('Nordlinger','Ries Bavaria','Germany',24.0,48.8833333333333,10.5666666666667,-144000000),
('Presquole','Quebec','Canada',24.0,49.7166666666667,-74.8,-500000000),
('Haughton','Nunavut','Canada',23.0,75.3833333333333,-89.6666666666667,-39000000),
('Lappajorvi','Western Finland','Finland',23.0,63.2,23.7,-73300000),
('Rochechouart','France','France',23.0,45.825,0.783333333333333,-206920000),
('Gosses Bluff','Northern Territory','Australia',22.0,-23.8166666666667,132.308333333333,-142500000),
('Amelia Creek','Northern Territory','Australia',20.0,-20.9166666666667,134.833333333333,-1030000000),
('Logancha','Siberia','Russia',20.0,65.5166666666667,95.9333333333333,-40000000),
('Obolon','Poltava Oblast','Ukraine',20.0,49.5833333333333,32.9166666666667,-169000000)
INSERT INTO tblCountry (CountryName, CountryCode)
VALUES
('Afghanistan','AF'),
('Aland Islands','AX'),
('Albania','AL'),
('Algeria','DZ'),
('American Samoa','AS'),
('Andorra','AD'),
('Angola','AO'),
('Anguilla','AI'),
('Antarctica','AQ'),
('Antigua and Barbuda','AG'),
('Argentina','AR'),
('Armenia','AM'),
('Aruba','AW'),
('Australia','AU'),
('Austria','AT'),
('Azerbaijan','AZ'),
('Bahamas','BS'),
('Bahrain','BH'),
('Bangladesh','BD'),
('Barbados','BB'),
('Belarus','BY'),
('Belgium','BE'),
('Belize','BZ'),
('Benin','BJ'),
('Bermuda','BM'),
('Bhutan','BT'),
('Bolivia'', Plurinational State of','BO'),
('Bonaire'', Sint Eustatius and Saba','BQ'),
('Bosnia and Herzegovina','BA'),
('Botswana','BW'),
('Bouvet Island','BV'),
('Brazil','BR'),
('British Indian Ocean Territory','IO'),
('Brunei Darussalam','BN'),
('Bulgaria','BG'),
('Burkina Faso','BF'),
('Burundi','BI'),
('Cambodia','KH'),
('Cameroon','CM'),
('Canada','CA'),
('Cape Verde','CV'),
('Cayman Islands','KY'),
('Central African Republic','CF'),
('Chad','TD'),
('Chile','CL'),
('China','CN'),
('Christmas Island','CX'),
('Cocos (Keeling) Islands','CC'),
('Colombia','CO'),
('Comoros','KM'),
('Congo','CG'),
('Congo'', the Democratic Republic of the','CD'),
('Cook Islands','CK'),
('Costa Rica','CR'),
('Côte d''Ivoire','CI'),
('Croatia','HR'),
('Cuba','CU'),
('Curaçao','CW'),
('Cyprus','CY'),
('Czech Republic','CZ'),
('Denmark','DK'),
('Djibouti','DJ'),
('Dominica','DM'),
('Dominican Republic','DO'),
('Ecuador','EC'),
('Egypt','EG'),
('El Salvador','SV'),
('Equatorial Guinea','GQ'),
('Eritrea','ER'),
('Estonia','EE'),
('Ethiopia','ET'),
('Falkland Islands (Malvinas)','FK'),
('Faroe Islands','FO'),
('Fiji','FJ'),
('Finland','FI'),
('France','FR'),
('French Guiana','GF'),
('French Polynesia','PF'),
('French Southern Territories','TF'),
('Gabon','GA'),
('Gambia','GM'),
('Georgia','GE'),
('Germany','DE'),
('Ghana','GH'),
('Gibraltar','GI'),
('Greece','GR'),
('Greenland','GL'),
('Grenada','GD'),
('Guadeloupe','GP'),
('Guam','GU'),
('Guatemala','GT'),
('Guernsey','GG'),
('Guinea','GN'),
('Guinea-Bissau','GW'),
('Guyana','GY'),
('Haiti','HT'),
('Heard Island and McDonald Islands','HM'),
('Holy See (Vatican City State)','VA'),
('Honduras','HN'),
('Hong Kong','HK'),
('Hungary','HU'),
('Iceland','IS'),
('India','IN'),
('Indonesia','ID'),
('Iran'', Islamic Republic of','IR'),
('Iraq','IQ'),
('Ireland','IE'),
('Isle of Man','IM'),
('Israel','IL'),
('Italy','IT'),
('Jamaica','JM'),
('Japan','JP'),
('Jersey','JE'),
('Jordan','JO'),
('Kazakhstan','KZ'),
('Kenya','KE'),
('Kiribati','KI'),
('Korea'', Democratic People''s Republic of','KP'),
('Korea'', Republic of','KR'),
('Kuwait','KW'),
('Kyrgyzstan','KG'),
('Lao People''s Democratic Republic','LA'),
('Latvia','LV'),
('Lebanon','LB'),
('Lesotho','LS'),
('Liberia','LR'),
('Libya','LY'),
('Liechtenstein','LI'),
('Lithuania','LT'),
('Luxembourg','LU'),
('Macao','MO'),
('Macedonia'', the Former Yugoslav Republic of','MK'),
('Madagascar','MG'),
('Malawi','MW'),
('Malaysia','MY'),
('Maldives','MV'),
('Mali','ML'),
('Malta','MT'),
('Marshall Islands','MH'),
('Martinique','MQ'),
('Mauritania','MR'),
('Mauritius','MU'),
('Mayotte','YT'),
('Mexico','MX'),
('Micronesia'', Federated States of','FM'),
('Moldova'', Republic of','MD'),
('Monaco','MC'),
('Mongolia','MN'),
('Montenegro','ME'),
('Montserrat','MS'),
('Morocco','MA'),
('Mozambique','MZ'),
('Myanmar','MM'),
('Namibia','NA'),
('Nauru','NR'),
('Nepal','NP'),
('Netherlands','NL'),
('New Caledonia','NC'),
('New Zealand','NZ'),
('Nicaragua','NI'),
('Niger','NE'),
('Nigeria','NG'),
('Niue','NU'),
('Norfolk Island','NF'),
('Northern Mariana Islands','MP'),
('Norway','NO'),
('Oman','OM'),
('Pakistan','PK'),
('Palau','PW'),
('Palestine'', State of','PS'),
('Panama','PA'),
('Papua New Guinea','PG'),
('Paraguay','PY'),
('Peru','PE'),
('Philippines','PH'),
('Pitcairn','PN'),
('Poland','PL'),
('Portugal','PT'),
('Puerto Rico','PR'),
('Qatar','QA'),
('Réunion','RE'),
('Romania','RO'),
('Russian Federation','RU'),
('Rwanda','RW'),
('Saint Barthélemy','BL'),
('Saint Helena'', Ascension and Tristan da Cunha','SH'),
('Saint Kitts and Nevis','KN'),
('Saint Lucia','LC'),
('Saint Martin (French part)','MF'),
('Saint Pierre and Miquelon','PM'),
('Saint Vincent and the Grenadines','VC'),
('Samoa','WS'),
('San Marino','SM'),
('Sao Tome and Principe','ST'),
('Saudi Arabia','SA'),
('Senegal','SN'),
('Serbia','RS'),
('Seychelles','SC'),
('Sierra Leone','SL'),
('Singapore','SG'),
('Sint Maarten (Dutch part)','SX'),
('Slovakia','SK'),
('Slovenia','SI'),
('Solomon Islands','SB'),
('Somalia','SO'),
('South Africa','ZA'),
('South Georgia and the South Sandwich Islands','GS'),
('South Sudan','SS'),
('Spain','ES'),
('Sri Lanka','LK'),
('Sudan','SD'),
('Suriname','SR'),
('Svalbard and Jan Mayen','SJ'),
('Swaziland','SZ'),
('Sweden','SE'),
('Switzerland','CH'),
('Syrian Arab Republic','SY'),
('Taiwan'', Province of China','TW'),
('Tajikistan','TJ'),
('Tanzania'', United Republic of','TZ'),
('Thailand','TH'),
('Timor-Leste','TL'),
('Togo','TG'),
('Tokelau','TK'),
('Tonga','TO'),
('Trinidad and Tobago','TT'),
('Tunisia','TN'),
('Turkey','TR'),
('Turkmenistan','TM'),
('Turks and Caicos Islands','TC'),
('Tuvalu','TV'),
('Uganda','UG'),
('Ukraine','UA'),
('United Arab Emirates','AE'),
('United Kingdom','GB'),
('United States','US'),
('United States Minor Outlying Islands','UM'),
('Uruguay','UY'),
('Uzbekistan','UZ'),
('Vanuatu','VU'),
('Venezuela'', Bolivarian Republic of','VE'),
('Viet Nam','VN'),
('Virgin Islands'', British','VG'),
('Virgin Islands'', U.S.','VI'),
('Wallis and Futuna','WF'),
('Western Sahara','EH'),
('Yemen','YE'),
('Zambia','ZM'),
('Zimbabwe','ZW')
SELECT * FROM tblCrater
SELECT * FROM tblCountry
Because tblCrater accepts NULL values, you can keep your queries and add this one. It will change tblCrater's CountryID value to match tblCountry's value for the same CountryName
UPDATE tblCrater
SET tblCrater.CountryID = tblCrater.CountryID
FROM tblCrater
INNER JOIN tblCountry
ON tblCrater.CountryName = tblCountry.CountryName
EDIT : This is only good if you add data only once. A trigger could be an alternate solution if more data will be add to the tables over time
CREATE TRIGGER add_countryid
ON tblCrater
AFTER INSERT, UPDATE
AS BEGIN
UPDATE x
SET x.CountryID = tblCrater.CountryID
FROM tblCrater x
INNER JOIN INSERTED i
ON i.CraterID = x.CraterID
INNER JOIN tblCountry
ON x.CountryName = tblCountry.CountryName
END
GO