Recently I came across the need to insert the same unique identifier into two separate columns in the same insert statement. It turns out that it is a relatively easy solution but something that took a little bit of ingenuity.
So it’s not often, in fact I can’t think of many instances where you would need to insert the same unique identifier into two different columns in a single table in a single insert statement. As you may or may not know the easiest way to get a new unique identifier in T-SQL is using the command NEWID(). However, the problem is that if you make this call twice in a single statement you’ll get two unique identifiers.
Now if you are doing a single record insert you can randomly generate the unique identifiers before hand and then just code them into your insert statement. In my case however I was selecting from a table and inserting into another where I needed to generate the new unique ID and insert it into two columns.
The solution ended up being eloquently simple, I found that if I cross joined to a table alias that simply involves a reference to NEWID() it would allow me to reference that column via the table reference and insert that generated ID into as many columns as I want into a single statement.
So a sample statement would be as follows:
SELECT ID.[NewID], ID.[NewID] FROM (SELECT '' AS [empty]) as TEST CROSS JOIN (SELECT NEWID() AS [NewID]) AS ID --Versus SELECT NEWID(), NEWID() FROM (SELECT '' AS [empty]) as TEST
Now it may seem like the cross join would cause extra rows from the select statement. However, since it is only a single record it will be a simple one to one join for every row. Each row will generate a new unique identifier, but only one per row.
Hopefully if you run into this situation this will help you.