I can't add data into my database.
I tried using quotation marks and apostrophes but still, I can't find the error.
insert into cliente(name, email, phone, adress, postalCode)
values ('Filipe', '[email protected]', 912345678, 'Red Street', '1234-567')
ERROR :
"String or binary data would be truncated"
I hope I can fix this quickly.
You should check the columns and their datatypes. One of the columns is smaller than the data you are trying to insert.
So for instance if email is a VARCHAR (10) this would break, because the input exceeds the max characters of 10.
Given below is the stored procedure that can find the exact column name and its data which is exceeding the limit of column width.
--DROP PROCEDURE usp_String_or_binary_data_truncated
--GO
CREATE PROCEDURE usp_String_or_binary_data_truncated
@String VARCHAR(MAX)
AS
DECLARE @VARCHAR AS VARCHAR(MAX)
DECLARE @Xml AS XML
DECLARE @TCount AS INT
SET @String= REPLACE(REPLACE(REPLACE(REPLACE(@String,'''','')
,'[',''),']',''),CHAR(13) + CHAR(10),'')
SET @Xml = CAST(('<a>'+REPLACE(@String,'(','</a><a>')
+'</a>') AS XML)
SELECT @TCount=COUNT(*)
FROM @Xml.nodes('A') AS FN(A)
;WITH CTE AS
(SELECT
(CASE
WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))>0)
THEN 1
WHEN CHARINDEX('VALUES',A.value('.', 'varchar(max)'))>0
THEN 2
WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))=0
AND CHARINDEX('VALUES',A.value('.', 'varchar(max)'))=0)
AND @TCount=2 THEN 2
WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))=0
AND CHARINDEX('VALUES',A.value('.', 'varchar(max)'))=0)
AND @TCount=3 THEN 3
END) AS[Batch Number],
REPLACE(REPLACE(A.value('.', 'varchar(max)')
,'INSERT INTO',''),'VALUES ','') AS [Column]
FROM @Xml.nodes('A') AS FN(A))
, [CTE2] AS
(
SELECT
[Batch Number],
CAST('' + REPLACE([Column], ',' , '')
+ '' AS XML)
AS [Column name And Data]
FROM [CTE]
)
,[CTE3] AS
(
SELECT [Batch Number],
ROW_NUMBER() OVER(PARTITION BY [Batch Number]
ORDER BY [Batch Number] DESC) AS [Row Number],
Split.a.value('.', 'VARCHAR(MAX)') AS [Column name And Data]
FROM [CTE2]
CROSS APPLY [Column name And Data].nodes('/M')Split(A))
SELECT
ISNULL(B.[Column name And Data],C.name) AS [Column Name]
,A.[Column name And Data] AS [Column Data]
,C.max_length As [Column Length]
,DATALENGTH(A.[Column name And Data])
AS [Column Data Length]
FROM [CTE3] A
LEFT JOIN [CTE3] B
ON A.[Batch Number]=2 AND B.[Batch Number]=3
AND A.[Row Number] =B.[Row Number]
LEFT JOIN sys.columns C
ON C.object_id =(
SELECT object_ID(LTRIM(RTRIM([Column name And Data])))
FROM [CTE3] WHERE [Batch Number]=1
)
AND (C.name = B.[Column name And Data]
OR (C.column_id =A.[Row Number]
And A.[Batch Number]<>1))
WHERE a.[Batch Number] <>1
AND DATALENGTH(A.[Column name And Data]) >C.max_length
AND C.system_type_id IN (167,175,231,239)
AND C.max_length>0
GO
varcharvalue you are trying toINSERTis too large for one of the columns. Which column, we don't know (we don't have the DDL for your table). You'll need to check the lengths of yourvarcharcolumns and ensure the literal strings you are passing are small enough to fit, or increase the size of your column's length. If you post the DDL of your table, we'll be able to be more explicit.INSERTstatement implies you're storingphoneas anintorbigint. This is actually a bad idea. Although Phone numbers are (normally) made up of numerical characters they aren't actually "numbers" that can be stored in a numerical data type, likeint. Phone Number can have leading zeros, which would be lost with a numerical data type, can have country codes (which can/do start with a+), extensions and often formatting attached to them. For example020 7123 4567and01234 567890for UK numbers. You would be far better off storingphoneas avarchar.