I’m converting a client’s db to our internal structure. In doing so I need to bring their files down. Import them into sql then convert them to my internal structure. There are about 200 files ranging in all sizes. the first time I bring in all the files I usually set the cols to a data type that will give the lease amount of error. In some cases I make em all varchar(max) just to get the file loaded. I then go through and create the schema based on the data types the client sends. However in this instance it is not only outdated however but on a PDF and not from a sql server database so the types are all different.

Nonetheless, I get the data loaded into the dummy table w/ varchar(max) cols. then I go back and manually create the table based on the clients schema spec. Then I try and insert the dummie table into the newly create table. When I do so I find I get a lot of truncation errors. Some of these tables have 50+ columns so it's impossible to see where the truncation is occurring. Hence the below sql I generated shortly after I posted this. Rather than doing it for every table I simply made it to run for a single table. it has paid off and now I can get through all my truncation issues when they occur relatively quickly. When i have mor etime i''ll play w doing what i originally planned

DECLARE @outtersql VARCHAR(max)

SET @outtersql=''

DECLARE @innersql VARCHAR(max)

SET @innersql=''

DECLARE @tablename VARCHAR(1000)

SET @tablename = 'put_Table_name_here'

DECLARE @colname VARCHAR(1000)

SET @colname = ''

DECLARE @sql VARCHAR(max)

SET @sql=''

SELECT st.name tablename

,sc.name colname

,0 processed

INTO #temp

FROM sys.tables st

JOIN sys.columns sc

ON sc.object_id = st.object_id

WHERE st.name LIKE @tablename

ORDER BY st.name

,sc.column_id

WHILE EXISTS (SELECT TOP 1 *

FROM #temp

WHERE processed = 0)

BEGIN

SET @sql= ''

SET @innersql = ''

SET @outtersql = ''

SELECT TOP 1 @tablename = tablename

FROM #temp

WHERE processed = 0

SET @outtersql = ' select ''' + @tablename + ''' tableName, '

SET @innersql = ' from ( SELECT '

WHILE EXISTS (SELECT TOP 1 *

FROM #temp

WHERE processed = 0

AND tablename = @tablename)

BEGIN

SELECT TOP 1 @colname = colname

FROM #temp

WHERE processed = 0

AND tablename = @tablename

SET @outtersql = @outtersql + ' max([' + @colname + ']) '

+ '[MaxLength_' + @colname + '], '

SET @innersql = @innersql + ' len([' + @colname + ']) ' + '['

+ @colname + '], '

UPDATE #temp

SET processed = 1

WHERE processed = 0

AND tablename = @tablename

AND @colname = colname

END

SET @innersql = @innersql + 'from ' + @tablename

+ '(nolock) ) tt'

SET @innersql = Replace(@innersql, ', from', ' from ')

SET @outtersql = Reverse(Substring(Reverse(@outtersql), 2, 100000))

SET @sql= @outtersql + @innersql

SET @sql = Replace(@sql, ', from ', ' from ')

execute( @sql)

UPDATE #temp

SET processed = 1

WHERE processed = 0

AND @tablename = tablename

END

UPDATE #temp

SET processed = 0

DROP TABLE #temp