Tag Archives: comma separated

Convert rows to comma separated list and insert in a table

While performing one of my query diagnostics I had to gather some ids from one database server and use those ids in another. The best way was to gather all ids in a comma separated or in a proper list which can be used to insert into a temp table on another sql server.

I used the below query to gather the list of ids separated by ‘UNION ALL SELECT’

SELECT DISTINCT  CONVERT(varchar,ci.Id) + ' UNION ALL SELECT '
				  FROM dbo.Car c (NOLOCK)
				  INNER JOIN dbo.CarItem ci (NOLOCK) on c.CarId = ci.CarId
                   FOR XML PATH('')

The result produced an XML formatted file which I used in another server to insert in a temp table.

In the process I came to know of one restriction of the SQL Server select/insert that the maximum number of rows allowed are only 4096.