How to output SQL SQLCMD result to a file in Windows?

sqlcmd has a -o parameter for the output file.
And in any DOS command you can always send the outcome of a command into a file by using the > operator:

sqlcmd -S servername -E -i C:\scripts\myscript.sql > C:\logs\results.txt 

Select all Open in new window

Yes, I know about the redirection within DOS, but how do I get SQL Server to write to the log when the task script is complete.  For example, the script that I have that loads data to a table, I want to be able to record the number of records that was loaded correctly, identifiy those records that did not load correctly, and at what time the script task was completed.    Is it possible within SQLCMD to do this (like Oracle's sqlplus command)?

I modified my batch file to have the redirection symbols ">>".  When I execute my batch file, I only get the Create message, followed by the date and time; the output of the script file is not capture.  How do I go about getting the output to go to the output file?  I want to give the user a message, stating whether the excution of the scripts were successful or not?

Below is the code for the batch and script files:

BATCH FILE
=========

echo on
set directory=c:\Users\chiefadmin4\log\
set logfile=oAllTbl.txt

set logfile_dest= "%directory%%logfile%"
echo logfile_dest=%logfile_dest%

if not exist %directory% (
  mkdir %directory%
  echo Created Log directory)

echo Creating Tables %date% %time% >> %logfile_dest% 

sqlcmd -S SQLTEST3 -E -d HCDA -Q "exec uspCreateAllTables @dataSrc=%1, @yr=%2" >> %logfile_dest%

Select all Open in new window

SQL Script:
=========

USE [HCDA]
GO
/****** Object:  StoredProcedure [dbo].[CreateAllTables]    Script Date: 4/6/2018 12:01:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =======================================================================
-- Author:		XXXXXXXXX
-- Create date: April 1, 2018
-- Description:	Creates all required tables for a specific data source
-- =======================================================================

ALTER PROCEDURE [dbo].[CreateAllTables] 
	-- Parameters for stored procedure
	@dataSrc char(5), 
	@yr int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SET ANSI_PADDING ON;

    
	exec [dbo].uspCreateChargeTbl      @dataSrc, @yr;
	exec [dbo].uspCreateDiagnosisTbl   @dataSrc, @yr;
	exec [dbo].uspCreateEpisodeTbl     @dataSrc, @yr;
	exec [dbo].uspCreateHacTbl         @dataSrc, @yr;
	exec [dbo].uspCreateHcpcsTbl       @dataSrc, @yr;
	exec [dbo].uspCreatePoaTbl         @dataSrc, @yr;
	exec [dbo].uspCreatePqiTbl         @dataSrc, @yr;
	exec [dbo].uspCreateProcedureTbl   @dataSrc, @yr;

END

Select all Open in new window

You didn't use the -o parameter. You should use it to have the output sent to a file.

I did try the -o parameter with the sql command line modified to:  

sqlcmd -S SQLTEST3 -E -d HCDA -e -Q "exec uspCreateAllTables @dataSrc=%1, @yr=%2" -o %logfile_dest% >> %logfile_dest%

Select all Open in new window

This is the output that I received ====>      Creating Tables Fri 05/04/2018 14:54:25.96  
As you can see, nothing else was redirected to the output file.

When I change the code line so that it does not have the -o parameter, as follows:

sqlcmd -S SQLTEST3 -E -d HCDA -e -Q "exec uspCreateAllTables @dataSrc=%1, @yr=%2" >> %logfile_dest%

Select all Open in new window

I then get the following result:
     Creating Tables Fri 05/04/2018 10:39:48.51  
     exec uspCreateAllTables @dataSrc=ST_NJ, @yr=20

How do I go about getting the rest of the scripts (that are called by uspCreateAllTables) to be appended to the output file?  I need a trailing log of all the scripts that are called and their results (i.e., successfully executed or failed due to errors).

Thank you.

Thank you the sp_help command enable me to send the structure to the output file.  Would you know if there is a command that enables me to send out the number of records that was loaded successfully onto the table (via BULK INSERT)?

I was on vacation, but your suggestions helped me with my problems.  Thank you for your assistance.