Insert error: There are more Columns in the Insert statement than values specified in the values clause

Question

When I give an input searching data from another table, this error is returned, which may be wrong with the query ?

INSERT INTO dbo.CRMRAT (IDCRMRAT, CODCOLIGADA, STATUS, TIPO, IDCRMCLIENTE, IDCRMCOLABORADOR, IDCRMPROJETO, DATA, HORAINICIO, HORAFIM, INTERVALO, KMDESLOCAMENTO, VALORPEDAGIO, VALORALIMENTACAO, VALORHOTEL, VALORESTACIONAMENTO, OUTRASDESPESAS, OBSERVACAO, USUARIOCRIACAO, DATACRIACAO, USUARIOALTERACAO, DATAALTERACAO, IDCRMFATURA, DATAFATURAMENTO)
    VALUES ((SELECT MAX(IDCRMRAT) + 1 AS IDCRMRAT FROM CRMRAT ) , (SELECT CODCOLIGADA, STATUS, TIPO, IDCRMCLIENTE, IDCRMCOLABORADOR, IDCRMPROJETO, DATA, HORAINICIO, HORAFIM, INTERVALO, KMDESLOCAMENTO, VALORPEDAGIO, VALORALIMENTACAO, VALORHOTEL, VALORESTACIONAMENTO, OUTRASDESPESAS, OBSERVACAO, USUARIOCRIACAO, DATACRIACAO, USUARIOALTERACAO, DATAALTERACAO, IDCRMFATURA, DATAFATURAMENTO
            FROM dbo.CRMRAT2 WHERE IDCRMRAT = 11 ))

Error presented:

Answer 1

As the error already says (in free translation):

Error in the INSERT: There are more columns in the instruction INSERT than values specified in the clause VALUES

In the fields of his INSERT you inform 24 fields but us VALUES you have two SELECT. This causes the second error shown in the image:

Only one Expression can be specified in the select list when the subquery is not introduced with EXISTS

In free translation:

Only one expression can be specified in the selection list when the sub-allowance is not entered with EXISTS

To have the expected result of query above you should get, before entering, the largest code in the table. After that, use the ROW_NUMBER to get a new code:

DECLARE @inicial INT;

SET @inicial = ISNULL((SELECT MAX(IDCRMRAT) + 1 AS IDCRMRAT FROM CRMRAT), 0);

INSERT INTO dbo.CRMRAT(IDCRMRAT,
                       CODCOLIGADA,
                       STATUS,
                       TIPO,
                       IDCRMCLIENTE,
                       IDCRMCOLABORADOR,
                       IDCRMPROJETO,
                       DATA,
                       HORAINICIO,
                       HORAFIM,
                       INTERVALO,
                       KMDESLOCAMENTO,
                       VALORPEDAGIO,
                       VALORALIMENTACAO,
                       VALORHOTEL,
                       VALORESTACIONAMENTO,
                       OUTRASDESPESAS,
                       OBSERVACAO,
                       USUARIOCRIACAO,
                       DATACRIACAO,
                       USUARIOALTERACAO,
                       DATAALTERACAO,
                       IDCRMFATURA,
                       DATAFATURAMENTO)
SELECT @inicial + ROW_NUMBER() OVER(ORDER BY CODCOLIGADA),
       CODCOLIGADA,
       STATUS,
       TIPO,
       IDCRMCLIENTE,
       IDCRMCOLABORADOR,
       IDCRMPROJETO,
       DATA,
       HORAINICIO,
       HORAFIM,
       INTERVALO,
       KMDESLOCAMENTO,
       VALORPEDAGIO,
       VALORALIMENTACAO,
       VALORHOTEL,
       VALORESTACIONAMENTO,
       OUTRASDESPESAS,
       OBSERVACAO,
       USUARIOCRIACAO,
       DATACRIACAO,
       USUARIOALTERACAO,
       DATAALTERACAO,
       IDCRMFATURA,
       DATAFATURAMENTO,
  FROM dbo.CRMRAT2
 WHERE IDCRMRAT = 11;

---

ROW_NUMBER

Numbers the output of a set of results. More specifically, it returns the sequential number of a row in a partition of a result set, starting at 1 on the first row of each partition.

Answer 2

The problem is exactly what the error describes, you are trying to insert 6 values (IDCRMRAT, CODCOLIGADA, STATUS, TYPE, IDCRMCLIENTE and IDCRMCOLABORADOR) but in the part of the values only selected 3 values (max(IDCRMRAT) + 1), CODCOLIGADA and Status.

You need to say the other values in order to be able to enter the values.