Use same GUID twice per row

Question

I am building an application to transfer data from an SQL server to an offsite location via ftp and XML files.

I am building the XML data for each file via a query with FOR XML PATH('path'), TYPE.

I'm going to use a GUID to generate the filename as well as use as an identiifier within the file, currently my SQL to get the table is as follows (simplified):

SELECT LVL1.inv_account_no
     , LVL1.cus_postcode
     , CONVERT(varchar(255),NEWID()) + '.xml' as FileName
     , (SELECT (SELECT CONVERT(varchar(255),NEWID()) FOR XML PATH('ident'), TYPE),  (

                SELECT.... [rest of very long nested select code for generating XML]

SQL Fiddle Example

This is giving me:

Account Postcode  FileName                              xCol
AD0001  B30 3HX   2DF21466-2DA3-4D62-8B9B-FC3DF7BD1A00  <ident>656700EA-8FD5-4936-8172-0135DC49D200</ident>
AS0010  NN12 8TN  58339997-8271-4D8C-9C55-403DE98F06BE  <ident>78F8078B-629E-4906-9C6B-2AE21782DC1D</ident>

Basically different GUID's for each row/use of NEWID().

Is there a way I can insert the same GUID into both columns without incrementing a cursor or doing two updates?

Vaud

Have you considered adding the value as a field in your table? – Dipteral

There isn't a guaranteed way. It is non deterministic how many times it will be evaluated even for SELECT N AS N1, N AS N2 FROM (SELECT NEWID())T(N) – Daviddavida

@BabakNaffas, I did think about adding the GUID into the table, the only issue is that it's the product of multiple joins meaning the single GUID in the header could refer to 1 to n rows, I could only work out how to add a GUID to each row, then I'd have to find a way to aggregate this down reliably. – Vaud

Answer 1

Try something like this:

SELECT GeneratedGuid, GeneratedGuid
FROM YourTable
LEFT JOIN (SELECT NEWID() AS GeneratedGuid) AS gg ON 1 = 1

"GeneratedGuid" has a different GUID for every row.

Sickert

Answer 2

You could use a Common Table Expression to generate the NEWID for each resulting row.

Here is the SQL Fiddle : http://www.sqlfiddle.com/#!3/74c0c/1

CREATE TABLE TBL (
   ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  GCOL VARCHAR(255),
  XCOL XML)

create table tbl2 (
  id int identity(1,1) not null primary key,
  foo int not null )

insert into tbl2 (foo) values
(10),(20),(30)

; WITH cte_a as ( select NEWID() as ID )
INSERT INTO TBL
SELECT CONVERT(varchar(255),cte_a.ID )
, (SELECT CONVERT(varchar(255),cte_a.ID) FOR XML PATH('Output'), TYPE)
from tbl2, cte_a

Jactitation

I think as per Martin's warning comment to the question, you're not guaranteed whether each reference to cte_a.ID will return the same value or whether it will lead to NEWID() being invoked for each reference. – Seif

Yes, it is non-deterministic but the optimizer won't call the method twice for the same row in the above example. As long as you don't reference the CTE with 2 different aliases, the NEWID() will only be evaluated once per line. I admit I could not find any literature that support my conclusion but it works and it worked in all the scenario I tested as long as there is only 1 reference to the CTE in the FROM clause. – Jactitation

Answer 3

Create a temporary variable and assign a NEWID() to it. Then you can use this variable as many times in your SELECT or INSERT queries. Value of temporary variable remain same till it's scope. In following example @gid is a temporary variable and assigned a GUID value as VARCHAR(36)

DECLARE @gid varchar(36)
SET @gid = CAST(NEWID() AS VARCHAR(36))
INSERT INTO [tableName] (col1, col2) VALUES(@gid, @gid)

after executing the above query col1 and col2 from [tablename] will have same guid value.

Upcountry

A short explanation on your snippet and why it helps would be useful to understand the snippet. – Cenac

@Cenac I hope the explanation is helpful for you – Upcountry