Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

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

, 25 Jan 2012
Rate this:
Please Sign up or sign in to vote.
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:

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)

Share

About the Author

IkhwanKrisnadi

Indonesia Indonesia
No Biography provided

Comments and Discussions

 
GeneralReason for my vote of 5 nice tip! Pinmemberbeginner201112-Feb-12 15:40 
GeneralReason for my vote of 5 really nice article Pinmembernikhi _singh7-Feb-12 0:01 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 25 Jan 2012
Article Copyright 2012 by IkhwanKrisnadi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid