Carriage Returns and Microsoft SQL Server Management Studio

It appears that Microsoft SQL Server Management Studio (replacement for Enterprise Manager and Query Analyzer) in MS SQL Server 2005 does not support carriage returns (or any variation of it) using the Open Table view. This view allows for easy editing of data. The exception of this is data that has carriage return in it. You can’t paste, shift-enter or anything else using this interface.

The other annoying thing is that when showing the results of a query in the grid, if there is carriage returns in the data it simply ignores them when displaying the data. To see the carriage returns, you will need to change the results view from grid to text.

So, the question is, what is the easiest way to insert or update data that has carriage returns in it. The answer is simple. Use SQL, forget the easy to use GUI.

Below is an example:

insert into MyTable(MyCol) values ('line one

line two

line three');

update MyTable set MyCol = 'line one

line two

line three

line four'

where ID = 1;