Get a comma-delimited list of columns

CrucisCo
21 Jul 2011
0

One of the things I've been doing recently has been writing lots of SQL inserts in which I've had to specify the columns. For example:


INSERT INTO tblMyTable
()
SELECT  FROM tblMySourceTable

The most tedious part was writing the column names in a comma-separated list. It would be good if I could automagically create a list which I could copy into the above statement in each case.

I set about finding a way to do it in an automatic way. There is a way to get the column details using a system stored procedure called sp_columns. This will return a table of column details for the table.


EXEC sp_columns tblMySourceTable

However in order to get the list of column names we need to be able to do what is essentially a 'select' on the output of the stored procedure; something that TSQL does not allow natively. In order to make this possible we have to perform a series of steps. However this will only work if the 'data access' server option is enabled. You can use the following to work out whether data access is enabled on the server.


--first, enable data access if necessary.  Use sp_helpserver to see current status
--returns 0 rows if not enabled
EXEC sp_helpserver 'MYDBSERVER', 'DATA ACCESS';

If data access is not enabled, you have to enable it using the following


--Enables data access
EXEC sp_serveroption 'MYDBSERVER', 'DATA ACCESS', TRUE;
--Disables data access (in case you need to set it back after your work)
--EXEC sp_serveroption 'MYDBSERVER', 'DATA ACCESS', TRUE;

Right, that covers the prep. Now the problem at hand:


USE MYDB
GO
--initially we just do a 'dummy' query -this return 0 rows, 
-- but it constructs the columns  we need in the temp table from the sp_columns system sproc
SELECT  * INTO #tmpTable FROM OPENQUERY([MYDBSERVER], 'EXEC sp_columns anytable')
--NOW we do the actual insert
INSERT INTO #tmpTable EXEC sp_columns tblMySourceTable
--now we have the data, we can construct the list
DECLARE @columnlist varchar(2000)
SET @columnlist = ''
SELECT @columnlist = coalesce (CASE 
			WHEN @columnlist = '' THEN COLUMN_NAME
			ELSE @columnlist + ', ' + COLUMN_NAME
		END, '')
FROM #tmpTable
DROP TABLE #tmpTable
PRINT @columnlist

Happy SQL-ing!