I would like to select all data from the SaldoDeProdutos table, which has a CodigoDoProduto column whose field format is something like 00001, and insert into the other table of the BDMCOM1_V3_ALEA database already including 3 leading zeros to complete the 8 digits.
Enter X number of characters in SQL Server field
4
Hello, I have two banks BDMCOM1 and BDMCOM1_V3_ALEA, both of which have a table named ProductDrive and the field called CodigoProduct, in the database table BDMCOM1, theProductDocument has 5 digits, in the table of the other bank theProductDocument has 8 digits. ..
asked by anonymous 07.07.2016 / 21:40
1 answer
5
If you want to enter the zeros in the selection, you can do this:
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 does not matter if 3 or less zeros are missing. REPLICATE this way counts the missing zeros and places them for you.
See more about the REPLICATE function here .
The insertion would look 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 that the type of the CODIGO column supports leading zeros, such as varchar or nvarchar .
EDIT
Formatting the response for 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.
07.07.2016 / 21:45