mjhjmtu

I want to append the output of the below loop one below the other, but in output file i get only the result of 'abc3'

Find the script below, Please let me know the change, thanks in advance.

for i in abc1 abc2 abc3
do
SELECT_QUERY_1="select substring('$i',4,len('$i')-7) as Table_Name,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = '$i' and ORDINAL_POSITION <= (select max(ORDINAL_POSITION)-4 from INFORMATION_SCHEMA.COLUMNS where table_name = '$i')"

echo -e `/opt/mssql-tools/bin/sqlcmd -S server_name -U username -P user -d db -Q "$SELECT_QUERY_1" -o "out.txt"`
sed "s/^[ t]*//" -i out.txt

mv out.txt /home/results/out.txt

done

change the line
mv out.txt /home/results/out.txt

to

cat out.txt >> /home/results/out.txt

to get all the three results in one file. Because with the mv command, the file is overwritten every time. That`s why you only get the last result.

If you do not want to keep the old data in that file, just need to

rm /home/results/out.txt

at the begining of the script

echo -e `/opt/mssql-tools/bin/sqlcmd ... -o "out.txt"`

Here, you're using command substitution to catch the output of sqlcmd, and then printing it again with echo. This is seldom useful, you could probably just run /opt/mssql-tools/bin/sqlcmd ... -o "out.txt" without the command substitution or echo. _{(There's is a small difference though, unquoted command substitution+echo transforms all whitespace to single spaces.)}

Now, assuming what you want is to concatenate all the outputs of all invocations of sqlcmd to a single file, you could use an appending output (>>) to direct the result directly to the final file.

If sqlcmd outputs normally to stdout, you could just do:

/opt/mssql-tools/bin/sqlcmd -S server_name -U username -P user 
 -d db -Q "$SELECT_QUERY_1" | sed "s/^[ t]*//" >> /home/results/out.txt

If it doesn't support that, you can use /dev/stdout as the output file:

/opt/mssql-tools/bin/sqlcmd -S server_name -U username -P user 
 -d db -Q "$SELECT_QUERY_1" -o /dev/stdout | sed "s/^[ t]*//" >> /home/results/out.txt