Insert X amount of characters in SQL Server field

Question

Hello, I have two banks BDMCOM1 and BDMCOM1_V3_ALEA, both of which have a table called Saldodeproducts and the field called Codigodoproduto, in the table of the bank BDMCOM1, Codigodoproduto has 5 digits, in the table of the other bank the codeDProduct has 8 digits...

I would like to select all table data SaldoDeProdutos, which has a column CodigoDoProduto whose field format is something like 00001, and insert into the other bank table BDMCOM1_V3_ALEA already including 3 zeroes left to complete the 8 digits.

Answer 1

If you want to enter the zeros in the selection, you can do so:

SELECT A.*, REPLICATE('0', 8 - LEN(B.CODIGO)) + B.CODIGO
FROM TABELA_A A
INNER JOIN TABELA_B B ON (...)

The idea is that it doesn’t matter if there are 3 or less zeros missing. REPLICATE this way counts the missing zeroes and puts them to you.

See more about the function REPLICATE here.

The insertion would be something like this:

INSERT TABELA_B (/* Campos aqui */)
SELECT REPLICATE('0', 8 - LEN(A.CODIGO)) + A.CODIGO as CODIGO, -- Mais campos aqui
FROM TABELA_A
WHERE -- Alguma condição aqui.

I’m guessing the column guy CODIGO supports zeros on the left, such as varchar or nvarchar.

---

EDIT

Formatting the answer to the rest of the information looks like this:

INSERT BDMCOM1_V3_ALEA (CODIGODOPRODUTO, /* Demais campos aqui */)
SELECT REPLICATE('0', 8 - LEN(A.CODIGODOPRODUTO)) + A.CODIGODOPRODUTO as CODIGODOPRODUTO, -- Mais campos aqui
FROM SaldoDeProdutos
WHERE -- Alguma condição aqui, não é obrigatório este where.