kdarty
10/8/2015 - 6:02 PM

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

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 placing it into the new Table/Columns.

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

Example-Resize-Column-Data.sql
content_copyfile_download
--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;
Check-Max-Table-Column-Size.sql
content_copyfile_download
--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)