String Or Binary Data Would Be Truncated

I have seen a couple of Google searches hitting this blog with the search 'String or binary data would be truncated'
Basically what the error message 'String or binary data would be truncated' means is that the data that you are trying to fit into a column inside a table won't fit because the column isn't wide enough
A lot of times this occurs when you are inserting data into 1 table from another table

For example you have a table on some database server and you will need to import that data into a new table
You will have to store Unicode in the future so you make one of the columns nvarchar, you don't bother to check for the maximum length of the data in the original table and just make you column nvarchar(4000) (the max in SQL server 2000)
Now let's test a couple of these cases

--Create the 'original' table
CREATE TABLE TestData (ID int,
SomeValue VARCHAR(5000),
SomeOtherValue VARCHAR(6))


--add some data
INSERT INTO TestData VALUES (1,REPLICATE('A',4002),'abcdef')
INSERT INTO TestData VALUES (2,'123','abcde')
INSERT INTO TestData VALUES (3,'123','abcdef')


--Create the 'new' table
CREATE TABLE TestDataTruncate2 (ID INT,
SomeValue NVARCHAR(4000),
SomeOtherValue VARCHAR(5)) --Oops 1 less than in the original table

--Fails
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =1
/*
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Fails because nvarchar can only hold 4000 characters
*/



--No Problem
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =2

--Fails
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =3
/*
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Fails because SomeOtherValue has 6 charaters of data
but the column is only 5 characters in the new table
*/



--Instead of opening up 2 windows and comparing the tables column by column
--you can use the query below to return all that info in a resultset

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TestDataTruncate'
UNION ALL
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TestData'
ORDER BY ORDINAL_POSITION,TABLE_NAME

--you can also create a self join and return only the columns
--where the name is the same but the column size is different
SELECT t1.COLUMN_NAME,t1.DATA_TYPE,
t1.CHARACTER_MAXIMUM_LENGTH,t2.CHARACTER_MAXIMUM_LENGTH,t1.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS t1
JOIN INFORMATION_SCHEMA.COLUMNS t2 ON t1.COLUMN_NAME =t2.COLUMN_NAME AND t1.TABLE_NAME <> t2.TABLE_NAME
WHERE t1.TABLE_NAME ='TestDataTruncate'
AND t2.TABLE_NAME ='TestData'
AND t1.CHARACTER_MAXIMUM_LENGTH <> t2.CHARACTER_MAXIMUM_LENGTH


--Strangely enough when you try to assign values to a variable
--it will truncate it without an error message
DECLARE @chvCity VARCHAR(8)
SELECT @chvCity ='Princeton'
SELECT @chvCity


--Clean up this mess ;-)
DROP TABLE TestDataTruncate,TestData