Monthly Archives: February 2014

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.

Regular expression reference

Regular expression reference table list

Regular ExpressionExplanation
a-zmatches small letters starting from 'a' - 'z'
A-Zmatches capital letters starting from 'A' - 'Z'
.dot(.) represents single wildcard character (joker in a card game)
\escape character. To escape the actual representation and represent actual character
^is used to negate a batch of character like [^a-z] -- matches the characters other than 'a' - 'z'
\ddigits
{m, n}m, n are integers here. represents any preceding character appears atleast 'm' times and atmost 'n' times. ex: a[1, 3] 'a' can appear 1-3 times
*represents the preceding expression can occur from 0 to any number of times. ex: \d* any digit can appear from 0 to any number of times
+represents the preceding expression will occur atleast once. ex: \d+ - digit will appear atleast once.
?represents the preceding character is optional(it may occur). ex: a? -- the match may have 'a' & ab?c -- 'b' is optional.
\wrepresents alphanumeric [a-zA-Z0-9_]
-space
\ttab
\nnewline
\rcarriage return
\sspace (represents space, tab, newline or carriage return)
^....$^ starting an expressiong and $ represents ending of the expression
()capturing part
|OR (cats|dogs|birds) - cats or dogs or birds
\bboundary between a word and a non-word character.
Any expression written in CAPITAL LETTER represents opposite of actual definition
ex: \D - non digits
\W - non alphanumeric
\S - non whitespace
Referencing any captured groups using parenthesis('()')
--- there can be many groups clubbed together and to refer each group use
ex: (\d*\.([,\d*]?))*$
\0 - full matched text
\1 - group 1
\2 - group 2

Running application from registry key path

This post for my reference:
I had a different version of MSAccess application and I had to run the application using Powershell script. The path of the application differs from computer to computer, since mine x64 bit the application resides in Program Files(x86) folder and in some other x32 bit machines the application is in Program Files. Moreover with different versions of Access the folder structure changes.

With the registry key it will target the latest version installed on your machine

@ECHO OFF
for /f "tokens=3*" %%x in ('reg query "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.exe" /v PATH') do set ACCESSM="%%x %%y"
"%ACCESSM%\MSACCESS.EXE \"Database Path\" /X macro-name"
@ECHO ON