String Concatenation in T-SQL and Importing from Excel
I keep forgetting how simple this little trick is so it’s time to blog it.
I wanted to write some T-SQL that would confirm that the columns just created by an import from an Excel spreadsheet into a temporary table contained the expected columns irrespective of order or other columns that exist in the file. I also wanted to be able to inform the user of the problem instead of just fail with a horrible error. I chose a fairly simple method, I would concatenate the columns from the temporary table (#tempImport in this case) and compare that with an expected string. If they don’t match I can report back to the user that the expected columns do not exist (in this implementation I’m not interested reporting which columns are missing.) The implementation I chose was this :
select * into #tempImport from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=e:\temp\aSpreadsheet.xls', Sheet1$) declare @columns varchar(512) set @columns = '' select @columns = @columns + ',' + [name] from tempdb.sys.columns where [object_id] = object_id('tempdb..#tempImport') and [name] in ('Store','Store Name','PSA1','PSA2','Mtrg') order by [name] if (@columns <> ',Mtrg,PSA1,PSA2,Store,Store Name') print 'The expected columns in the spreadsheet do not exist'
This is a brute-force type of implementation but I like it because it’s very simple and keeps everything in one place. There will be better ways to implement this but for the moment this fits the bill.
http://support.microsoft.com/kb/326548 is a useful guide to using the ISAM provider.
http://www.projectdmx.com/tsql/rowconcatenate.aspx discusses concatenating rows in quite some detail.Post by: Philip Hendry