Assign variable in a select inside an insert

Question

I'm trying to get the assignment of a variable inside a select which runs inside an insert.

INSERT INTO myTable
            (Value1,Value2,Value3)
            VALUES
            (
            (
                    SELECT 'SomeText' + @MyGuid = lower(convert(varchar(36), newid())) + '"Some more text" />' AS Value1,
                    @MyGuid AS Value2,
                    columnX AS Value3 FROM myOtherTable));

Essentially, I need to use the same variable twice, but the assignment gets flagged with

Incorrect syntax near '='.

I tried changing the assignment syntax, but it did not help. I need the Guids to be the same. Is it possible to do so?

Siyual · Accepted Answer · 2016-04-29 13:45:38Z

1

You can do this with a CTE by selecting the NEWID() value for each row beforehand, then transforming it per column on the INSERT:

;With ToInsert As
(
    Select  lower(convert(varchar(36), newid())) As MyGuid,
            ColumnX As Value3
    From    MyOtherTable
)
Insert  MyTable
        (Value1, Value2, Value3)
Select  'SomeText' + MyGuid + '"Some more text" />' As Value1,
        MyGuid As Value2,
        Value3
From    ToInsert

answered Apr 29, 2016 at 13:45

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Assign variable in a select inside an insert

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related