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