criar usuário TESTING identificado por “Teste”;
grant connect, resource to TESTING;
grant dba to TESTING;
connect TESTING/Test;
set define off;
create table airport_final
(
iata_code varchar2(3),
city_code varchar2(3),
city_name nvarchar2(24),
country_code varchar2(2),
country_name nvarchar2(33),
no_of_airports number(1),
airport_code varchar2(3),
city_with_country nvarchar2(61),
airport_name nvarchar2(80)
);
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name)
VALUES ('YVE','YVE', 'Vernon', 'CA', 'Canada');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name)
VALUES ('ZZW','ZZW', 'Day Trip Mystery', 'PG', 'Australia');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name)
VALUES ('VRA','VRA', 'Varadero', 'CU', 'Cuba');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name)
VALUES ('VRO','VRA', 'Varadero', 'CU', 'Cuba');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name)
VALUES ('BOL','BOL', 'Ballykelly', 'PG', 'United Kingdom');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name)
VALUES ('BMV','BMV', 'Buon Ma Thuot', 'PG', 'Vietnam');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name)
VALUES ('WBI','WBU', 'Boulder', 'US', 'United States');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name)
VALUES ('WBU','WBU', 'Boulder', 'US', 'United States');
INSERT INTO airport_final(iata_code, city_code, city_name, country_code, country_name)
VALUES ('WHH','WBU', 'Boulder', 'US', 'United States');
commit;
select iata_code, city_code, city_name, country_code, country_name from airport_final where city_code='VRA';
create table city(
city_code varchar2(3),
country_code varchar2(3),
city varchar2(24)
);
Eu inseri dados do arquivo CSV na tabela Oracle airport_final
e desejo inserir colunas específicas na tabela city usando esta instrução
SQL> insert
into city(city_code, country_code,city)
with t as (
select distinct c.country_code,
a.city_name,
a.city_code
from airport_final a,
city c
where c.city_code = a.city_code
)
select
city_code,
country_code,
city_name
from t
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
0 rows created.
Esta declaração não está inserindo dados Por favor, informe como posso inserir dados de colunas específicas de outra tabela
Atenciosamente
O que há de errado com [o antiquado]:
Sua consulta de inserção usa junção entre essas duas tabelas, o que sugere que já podem existir algumas linhas na
city
tabela de destino. Se for assim, que tal outra abordagemmerge
?Se já existirem algumas linhas, execute a atualização; caso contrário, execute insert: