How to insert enters by script

Question

I have a powerhshell script who inserted values to the text column. In my query command are something like this:

query = "UPDATE mytable 
SET mytable.mycolumn = 'FILE1
FILE2
FILE3
FILE4
FILE...
FROM mytable 
WHERE someconditions..."

When I select data form my database (after update above), I expected something like this:

SELECT mycolumn FROM mytable

mycolumn
FILE1
FILE2
FILE3
FILE4
FILE...

But in database I have

mycolumn
FILE1            FILE2                 FILE3          FILE4         FILE...

When modify my query to:

query = "UPDATE mytable SET mytable.mycolumn = 'FILE1`r`n FILE2`r`nFILE3...

result is

FILE1`r`n FILE2`r`nFILE3...

Looks like you are trying to concat them... so you expect your one row of data in that column to look like FILE1 FILE2 FILE3 as a string? Or are you wanting a row for every file name? — S3S, Jan 30 '17 at 15:41
It's not clear what you're looking for. Do you expect the attribute `mycolumn` for a _single tuple_ to contain all of the strings separated by newlines? Or do you expect _multiple tuples_, each of which has one of the strings in the attribute `mycolumn`? — Jeff Zeitlin, Jan 30 '17 at 15:45
In one row must be multiple valuses... as separator must be enter... — exo, Jan 30 '17 at 15:51
http://stackoverflow.com/questions/31057/how-to-insert-a-line-break-in-a-sql-server-varchar-nvarchar-string And be sure to set your results to text versus grid to see this in action — S3S, Jan 30 '17 at 15:57

score 0 · Answer 1 · answered Jan 30 '17 at 16:02

Try using:

query = "UPDATE mytable" + 
"SET mytable.mycolumn = 'FILE1' + CHAR(13) + CHAR(10) + " +
"'FILE2' + CHAR(13) + CHAR(10) + " +
"'FILE3'  + CHAR(13) + CHAR(10) + " +
"'FILE4'  + CHAR(13) + CHAR(10) + " +
"'FILE'...  + CHAR(13) + CHAR(10) + " +
"FROM mytable " + 
"WHERE someconditions..."

How to insert enters by script

1 Answers1