If you are ever in a position where you need to migrate data from one SQL Server Database (or Table) to Another which differ in Column sizes (length of fields), use this script to determine the Max Length of each column to compare for your Migration Scripts. Once you know the Max Length of the Data you are getting, you can properly handle placin…

Check-Max-Table-Column-Size.sql

--Source Discussion: https://community.spiceworks.com/topic/131997-sql-copy-field-in-to-smaller-field
--Author: Jason Crider - https://twitter.com/jasoncrider

--Get MaxLength on all columns in a table
DECLARE @SQL VARCHAR(MAX)

DECLARE @TableName sysname

SET @TableName = 'INSERT_TABLENAME_HERE'

SET @SQL = ''

SELECT @SQL = @SQL + 'SELECT ' + QUOTENAME(sc.name, '''') + ' AS ColumnName, ' + QUOTENAME(t.name, '''') + ' AS DataType, ' +

QUOTENAME(sc.max_length, '''') + ' AS SetLength, MAX(DATALENGTH(' + QUOTENAME(sc.name) + ')) AS MaxLength FROM '+@TableName+ char(10) +' UNION '

FROM sys.columns sc

join sys.types t on t.system_type_id = sc.system_type_id and t.name != 'sysname'

WHERE sc.OBJECT_ID = OBJECT_ID(@TableName)

SET @SQL = LEFT(@SQL, LEN(@SQL)-6)

PRINT @SQL

EXEC(@SQL)

Example-Resize-Column-Data.sql

--Truncate and Trim the Data to fit new Column
--NOTE: Before doing this you should run the above script to see if you need to increase Column Size
SELECT RTRIM(LTRIM(LEFT(FirstName, 50))) AS [FirstName]
FROM   OldDatabase.dbo.OldTable;