Jun 092011
 

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.

  2 Responses to “How to insert the same NewID unique identifier into multiple columns”

  1. Why do the Empty and the Cross Join and not this:

    SELECT a.ID, a.ID
    FROM
    (
    SELECT NEWID() AS ID
    ) a

    • Well in my case that I used this implementation I was selecting values from a different table to insert into a new record.

      So I had to do a join to get my values alongside my new unique identifiers. I decided to do a cross join because it was the most simple solution. I could have done an inner or outer join but would have had to join on some arbitrary value.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.