To execute a SQL Query or SQL Command using the command prompt, use the sqlcmd command on the command prompt.
The format is:
sqlcmd -S [server_name] -U [login_user_name] -P [login_password] -i [full_path_of_sql_file_that_will_be_executed] -o [full_path_of_output_file_after_sql_executed]
For example: I have a SQL file named "script.sql" and it is on root drive C. "script.sql" contains what is shown below:
USE [WEB_TUTORIAL]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MST_MENU]') AND type in (N'U'))
DROP TABLE [dbo].[MST_MENU]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MST_MENU]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MST_MENU](
[MENU_ID] [varchar](17) NOT NULL,
[MENU_NAME] [varchar](250) NOT NULL,
[MENU_PATH] [varchar](2500) NOT NULL,
[PARENT_ID] [varchar](17) NULL,
[IS_ACTIVE] [int] NOT NULL,
[CREATED_BY] [varchar](250) NOT NULL,
[CREATED_DATE] [datetime] NOT NULL,
[UPDATED_BY] [varchar](250) NOT NULL,
[UPDATED_DATE] [datetime] NOT NULL,
CONSTRAINT [PK_MST_MENU] PRIMARY KEY CLUSTERED
(
[MENU_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
To execute "script.sql", I type the below on the command prompt:
sqlcmd -S IKHWANK-PC,1433\MSSQLSERVER -U sa -P ikhwankrisnadi -i C:\script.sql -o C:\output-script.txt
After "script.sql" is executed, there's a new file named "output-script.txt". Check the database after executing "script.sql" to view the results.
