Transforming Data, Help Plz
-
Hi People,
I'm on migration process and started a build some scripts for migration data, but in a specific script i don't know where's error, i thank all people who can try help me.
In Source Database there data with space, ascii codes and i need import all.
My code:
SET NOCOUNT ON
DECLARE @MunicipioID INTEGER
DECLARE cCursor CURSOR FOR SELECT MunicipioID FROM AAPAS.dbo.MunicipioProjeto ORDER BY Nome, UF
OPEN cCursor
FETCH NEXT FROM cCursor INTO @MunicipioID
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
INSERT INTO SA1010 (A1_COD, A1_LOJA, A1_NOME, A1_PESSOA, A1_MODALID, A1_TIPO, A1_NREDUZ, A1_END, A1_MUN, A1_DTCAD,
A1_EST, A1_BAIRRO, A1_DDD, A1_CEP, A1_TEL, A1_DDI, A1_FAX, A1_CGC, A1_ZTIPO, A1_CODDEP,
A1_TEL2, A1_OUTTEL, A1_ZOBS, A1_EMAIL, A1_CODECT, A1_ZSITUA, A1_AGECT, A1_PASCOD, D_E_L_E_T_, R_E_C_N_O_)
SELECT
(SELECT RIGHT('000000'+CONVERT(VARCHAR(6),ISNULL(MAX(CONVERT(INTEGER,A1_COD)),0)+1),6) FROM SA1010) AS A1_COD,
'01' AS A1_LOJA,
AAPAS.dbo.TiraAcento(UPPER(RTRIM(LTRIM(NOME)))) AS A1_NOME,
'O' AS A1_PESSOA,
'3' AS A1_MODALID,
'F' AS A1_TIPO,
AAPAS.dbo.TiraAcento(UPPER(RTRIM(LTRIM(NOME)))) AS A1_NREDUZ,
AAPAS.dbo.TiraAcento(UPPER(RTRIM(LTRIM(ENDERECO)))) AS A1_END,
AAPAS.dbo.TiraAcento(UPPER(RTRIM(LTRIM(CIDADE)))) AS A1_MUN,
CONVERT(VARCHAR(8), GETDATE(), 112) AS A1_DTCAD,
UPPER(RTRIM(LTRIM(UF))) AS A1_EST,
AAPAS.dbo.TiraAcento(UPPER(RTRIM(LTRIM(BAIRRO)))) AS A1_BAIRRO,
CONVERT(VARCHAR(3), CONVERT(INTEGER, AAPAS.dbo.SomenteDigitos(RTRIM(LTRIM(DDD))))) AS A1_DDD,
UPPER(RTRIM(LTRIM(AAPAS.dbo.SomenteDigitos(CEP)))) AS A1_CEP,
UPPER(RTRIM(LTRIM(AAPAS.dbo.SomenteDigitos(TELEFONE1)))) AS A1_TEL,
'55' AS A1_DDI,
UPPER(RTRIM(LTRIM(AAPAS.dbo.SomenteDigitos(FAX)))) AS A1_FAX,
UPPER(RTRIM(LTRIM(AAPAS.dbo.SomenteDigitos(CGC)))) AS A1_CGC,
'L' AS A1_ZTIPO,
'0' AS A1_CODDEP,
UPPER(RTRIM(LTRIM(AAPAS.dbo.SomenteDigitos(TELEFONE2)))) AS A1_TEL2,
AAPAS.dbo.TiraAcento(UPPER(RTRIM(LTRIM(OutroTelefone)))) AS A1_OUTTEL,
AAPAS.dbo.TiraAcento(UPPER(RTRIM(LTRIM(Obs)))) AS A1_ZOBS,
LOWER(RTRIM(LTRIM(Email))) AS A1_EMAIL,
LOWER(RTRIM(LTRIM(CodAgenciaECT))) AS A1_CODECT,
CONVERT(VARCHAR(1), Situacao) AS A1_ZSITUA,
AAPAS.dbo.TiraAcento(UPPER(RTRIM(LTRIM(AgenciaECT)))) AS A1_AGECT,
MunicipioID AS A1_PASCOD,
' ' AS D_E_L_E_T_,
(SELECT ISNULL(MAX(R_E_C_N_O_),0)+1 FROM SA1010) AS R_E_C_N_O_
FROM AAPAS.dbo.MunicipioProjeto
WHERE MunicipioID = @MunicipioID
IF @@ERROR = 0
BEGIN
@MunicipioID)
COMMIT
END
ELSE
BEGIN
PRINT 'Erro: ' + CONVERT(VARCHAR(6), @MunicipioID)
ROLLBACK
END
FETCH NEXT FROM cCursor INTO @MunicipioID
END
CLOSE cCursor
DEALLOCATE cCursor
-
And i got this error:
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 3179
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 181
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 2846
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 2743
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 3504
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 1397
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 356
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 243
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 483
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 3487
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 3180
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 3181
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 51
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 3025
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 3426
Server: Msg 8152, Level 16, State 9, Line 14
String or binary data would be truncated.
The statement has been terminated.
Erro: 1461
More a lot error of same type, someone can help me?
-
Hey People,
I wanna ask if there someone who can give a hit where's the problem because i searched in this forum and in anothers forums but at the moment i don't get a response or hint.
Thanks for all people who can help me.
-
It looks like the error is telling you to re examine your datatypes. It looks like they don't match, and thus you will have to do some type of data type conversion to make this work.
Its not much, but should be able to get you started in the right direction 🙂
-
Hey Sean,
Thanks for help, i searched about the error on books online, and make a lot of selects for see data of source, the only thing i notice is values Null or ASCII 13 (enter in field), because this i asked if there a string or condition for i add in each line of import for try fix it.
If someone more cn help i thank a lot.
-
Assuming that you have already varified that the data types you are inserting match the data types you are selecting, here is the solution I used when I had this problem:
Use a CAST statement to make sure that the variables you select match exactly the fields you are putting the data in to. Example:
CAST(title AS char(50)) as title
I sounds silly, but this will resolve the truncation problems. You can find the specific fields you need to cast by dropping fields from the insert and select until the statement works. As you start adding them back put a CAST on any field that causes the error.
...JS
-
Maybe this can help a little. I did run into similar issue a few weeks back.
First the error message you are getting is for each record SQL Server is trying to write, So basically the error is in the table design, in my case.
The problem I had, and got the same kind of message, was realted not to the datatype, but to the length of the field.
I had some data coming like 0825 for date and I needed to get 08/25/2004.
The convert function worked fine but the field leght was set to 6 characters.
When SQL tried to write my convert statement it came back with the rror message entioned in your message. After a while, I figure out!!! changed the field lenght and the statement run perfectly. Maybe this is not your case, but worth the try. Good luck!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply