Multiple SELECT statements from different tables

I have two independent tables: ‘Clients’ and ‘Country’.

Country Table:

IdCountry   Country
1   SPAIN
2   PORTUGAL

Clients Table

IdClient    Entity   IdCountry
1   Adam Alves       2
2   Peter Smith      2
3   David Ramos      1
4   Rafael Castro    1

I would like to add a new client into ‘Clients’ table but using the information from ‘Country’ table like this:

INSERT INTO Clients(IdClient, Entity, Country)
SELECT max(IdClient) + 1, '--New--' FROM Clients,
SELECT IdCountry FROM Country WHERE Country = 'SPAIN'

I would like to have this INPUT:

IdClient   Entity    IdCountry
    5      --New--   1

But if I run this query, it doesn’t work. Could anybody help me, please?

COMMENTS: I prefer don’t use autoincrement option.

Thank you very much.

Wardiam

Answer

You can do it like this:

INSERT INTO Clients(IdClient, Entity, Country)
SELECT 
  (SELECT MAX(IdClient) + 1 FROM Clients), 
  '--New--',
  (SELECT IdCountry FROM Country WHERE Country = 'SPAIN')

See the demo.
Results:

| IdClient | Entity        | Country |
| -------- | ------------- | ------- |
| 1        | Adam Alves    | 2       |
| 2        | Peter Smith   | 2       |
| 3        | David Ramos   | 1       |
| 4        | Rafael Castro | 1       |
| 5        | --New--       | 1       |