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.