Char(13) + Char(10) in field

[simian336]

I have this piece from a cte insert into a table.

I need to store Char(13) + Char(10) as part of the results. But when I copy and paste values I only get spaces.

Any ideas?

insert into Test.dbo.namelist
select [Last__bName], [First__bName], [Email__bAddress], [User__bID], ID_Count,
'Update testtable' + Char(13) + Char(10) +
'set [User__bID] = ''' + [User__bID] +
''', [Email__bAddress] = ''' + [Email__bAddress] +
''', [Last__bName] = ''' + [Last__bName] +
''', [First__bName] = ''' + [First__bName] + Char(13) + Char(10) +
''' Where [Last__bName] = ''' + [Last__bName] +
''' and [First__bName] = ''' + [First__bName] +'''' as sql
from cte;


Desired result...

Update testtable
set [User__bID] = 'jeffriea', [Email__bAddress] = bla@bla.com', [Last__bName] = 'Adams', [First__bName] = 'Jeff'
Where [Last__bName] = 'Adams' and [First__bName] = 'Jeff'


Thanks

Simi

 

[gmmastros]

Looks OK to me.

Are you testing this in SQL Server Management Studio? If so, you won't see the CR/LF unless you set the output to text.

Right click in the query window, click Results To -> Results to Text and then run your query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Hey George,

Yes, it works in the text window but not if I copy and paste it from the grid window.

Simi

 

[gmmastros]

That's because the grid window has limitations. CR/LF's are not displayed properly.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom