Command line operations for SQL

1. Write this command in SSMS to fetch data from table and insert into the testing.xlsx in a given location.

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\testing.xlsx;', 'SELECT * FROM [Sheet1$]') select * from tbl_student

2. Write this command in command line to exporting data from table/view/query and insert into the any  file in a given location, it also called as  Bulk copy command.

Firstly you need to go to the below path to run BCP.exe command.

BCP Location for SQL Server 2012 – C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn

BCP Location for SQL Server 2014 – C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\120\Tools\Binn

BCP Location for SQL Server 2015 – C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn

BCP Location for SQL Server 2019 – C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn



Then RUN below BCP Command to Bulk copy.

bcp "SELECT id, name, description FROM tbl_student" queryout  E:\DBA\StudentTest.xls -S OMS-MEL-TEST -T -d db_student_Test -U on.user -P on.user -c

3. Write this command in command line to delete the unwanted files and folders from given path table and insert into the testing.xlsx i given location.

forfiles /p "E:\Data2\A1Files\V2" /s /d -872 /c "cmd /c del /s /q @file" 

4. Copy file from common folder to particular path by the help of batch file..

xcopy "\\10.100.0.707\scan\studentupdate\update\*.dll" "D:\Data\One\Enterprise\" /y