This error message is relatively straight forward. The way it normally happens is when you are trying to insert data from a table that contains values that have larger data lengths than the table you are trying to insert into. An example of this would be trying to insert data from a permanent table, into a temporary table that has data types defined at a fixed length. In order to illustrate the example, we’ll create two temporary tables below and try to insert a value that will not fit into the second temporary table. [cc lang=”sql”] — First create the table that will hold the initial value CREATE TABLE #Employee ( ID int IDENTITY(1,1) ,FirstName varchar(50) ,LastName varchar(50) ) — Insert the example value INSERT INTO #Employee ( FirstName ,LastName ) SELECT FirstName = ‘Billie’ — 6 characters ,LastName = ‘Jean’ — Create the smaller table that will not fit the value CREATE TABLE #Employee_Truncate ( ID int ,FirstName varchar(5) — 5 characters ,LastName varchar(49) ) — Attempt to fit the smaller value into the table INSERT INTO #Employee_Truncate ( FirstName ,LastName ) SELECT FirstName ,LastName FROM #Employee [/cc] From this we see the following error: In order to fix the error, either the second temp table needs to have its data type expanded to be able to fit the data, or you could use the SELECT..INTO method of inserting the data. That way, no matter what size of data the source table changed to, the destination table would automatically […]
Continue reading ...
