Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

Execute SQL Query / SQL Command on SQL Server Using the Command Prompt

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
25 Jan 2012CPOL 73.2K   3   2
How to execute SQL Query / SQL Command on SQL Server using command prompt.

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:


SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Indonesia Indonesia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralReason for my vote of 5 nice tip! Pin
beginner201112-Feb-12 15:40
beginner201112-Feb-12 15:40 
Reason for my vote of 5
nice tip!
GeneralReason for my vote of 5 really nice article Pin
Nikhil_S7-Feb-12 0:01
professionalNikhil_S7-Feb-12 0:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.