Using SQL to write SQL

/*
You may be asked to create users for your company's inhouse application. We're not talking SQL Server Logins and Database Users, no. We're talking security credentials that most web applications administer through tables seemingly common to most businesses like the [Users] table.

If you were to get hit with one of these requests, your first reaction might be to ask whether a process or scripts for this sort of thing already exist. Depending on the size and age of your company and the quality of your predecessor DBAs, there might not be any processes or scripts available and so you must create one. Sure you COULD go to the GUI and then script your changes out but you still have to manually enter the data values for the new record. Plus using the GUI is a much less efficient way to get these users into every environment in which they are needed (thanks to the human element and our tendency to error).

I recently got one of these requests in which the author of the ticket specified three new users to be created and modelled after another user (permission-wise). Our application keeps user information in a series of tables with the prefix User. In order for a user to have access to the application, they will need a record in the [User] table.

No brainer, so far?

To INSERT into the User table we might use something like:
*/

INSERT [User](User_Group_ID,User_Name,Password_Hash,Full_Name,Email_Address,[...])
VALUES ('3121', 'sqlpoet', 'B44DDA1DADD351948FCACE1856ED97366E679239',[...])

/*
Even with a manually written insert statement like this one we still have to get the data of the user we are required to model these new ones after. Since I did some preliminary querying on the the table, I know that our model user's User_ID = 11106
*/

SELECT * FROM [User] WHERE User_ID = 11106

/*
Using the data resulting from the query above, we plug in the common data values changing variable datavalues such as User_Name and then copy and paste each value into its cooresponding place in the INSERT statement.

But what if we re-wrote our SELECT to write the INSERT for us?
*/

SELECT

'INSERT [User](User_Group_ID,User_Name,Password_Hash,Full_Name,Email_Address,[...])

VALUES(''' + convert(varchar(20),User_Group_ID) + ''', ''' + [User_Name] + ''', ''' + char(10) +
Password_Hash + ''', ''' + Full_Name + ''', ''' + Email_Address + ''', ''' + [...] + ''')'

FROM [User] WHERE User_ID = 11106 --our model user

/*
The result of the above query, done correctly, will yield a statement that with a little editing can be copied and pasted into your change control script so that these users easily move from environment to environment whenever executed. Or you can execute them to create these users now...its up to you. Happy TSQLing.

SQL Poet
*/