Problem Statement: We have a SQL file in a folder on SQL Server, now i want to execute that file using SQL Command. To execute any command line statement, we using XP_CMDShell, extended stored procedure provided by Microsoft. In this article, we will see how to execute command line statements using XP_CMDShell stored procedure.
Solution: We will create a SQL file that will contain below statements in it and put this file at location “D:\Script\Employee.sql“.
Now we will create “Employee” table using below SQL script so that we can insert the above rows into “Employee” table.
Create Table Employee(Eid int,Name Varchar(50))
Lets execute below statement in SSMS(SQL Server Management Studio) to execute above script file.
EXEC MASTER..xp_cmdshell ‘SQLCMD -S Neeraj-Pc\sql2008 -d MyDB -i D:\Script\Employee.sql’
Lets understand parameters of above query.
SQLCMD – is used to invoke SQL command mode.
-S –> is used to specify the Server name(SQL Server)
-d –> is used to specify the database name.
-I –>is used to specify the input file location to be executed.
You can see in the above screenshot that all records inserted successfully in the “Employee” table. I hope you will find this article useful.
896 total views, 4 views today