Differences

This shows you the differences between two versions of the page.

--- microsoft:microsoft_sql_server:sql:insert:truncated_insert [2021/08/05 14:30] – created peter
+++ microsoft:microsoft_sql_server:sql:insert:truncated_insert [2021/08/05 16:05] (current) – peter
@@ Line 1: / Line 1: @@
 ====== Microsoft - Microsoft SQL Server - SQL - Insert - Truncated Insert ======
+===== Create a test database =====
+<code sql>
+Create Database TestDB
+Go
+Use TestDB
+Go
+CREATE TABLE TestTable
+(
+ [ID] INT identity(1,1),
+ [NAME] VARCHAR(10),
+)
+GO
+</code>
+----
+<code sql>
+INSERT INTO TestTable VALUES ('Some dummy data')
+GO
+INSERT INTO TestTable VALUES ('Some more dummy data')
+GO
+</code>
+returns errors:
+<code sql>
+SQL truncate error message ‘String or binary data would be truncated.’
+</code>
+----
+===== Check the length of the string that is being inserted =====
+<code sql>
+select len('Some dummy data') as [StringLength]
+Select len('Some more dummy data') as [StringLength]
+</code>
+<WRAP info>
+**NOTE:**  This should return 15 and 20.
+  * These are both longer than the length of the NAME field which caters for 10 characters.
+</WRAP>
+----
+===== Check the length of the Name column =====
+<code sql>
+select character_maximum_length,column_name
+from information_schema.columns
+where table_name = 'TestTable'
+and Column_name='NAME'
+</code>
+returns:
+<code sql>
+</code>
+----
+===== Solution =====
+<code sql>
+SET ANSI_WARNINGS off
+GO
+INSERT INTO TestTable VALUES ('Some dummy data')
+GO
+INSERT INTO TestTable VALUES ('Some more dummy data')
+GO
+SET ANSI_WARNINGS on
+GO
+</code>