String or binary data would be truncated.

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 inherit.

An example of this would be to use:

[cc lang=”sql”]
— Define the source table
CREATE TABLE #Employee
(
ID int IDENTITY(1,1)
,FirstName varchar(50)
,LastName varchar(50)
)

— Insert sample values into the source table
INSERT INTO #Employee
(
FirstName
,LastName
)
SELECT
FirstName = ‘Billie’ — 6 characters
,LastName = ‘Jean’

— Now insert all the values from the source table
SELECT
* — insert all values without having to define the columns
INTO #Employee_Wont_Truncate
FROM #Employee
[/cc]

This way does not require a declaration of a table prior to insertion, and automatically creates a table at run time. We can also insert all the columns at run time, so if additional columns get added, they will also be automatically added to our temporary table.