How to connect sqlserver from command prompt using sqlcmd / bulk insert

Hi all,

I need to insert large amount of records into sql server. The script is ready in file. The insert query is ready but the record is around 15 Lacs.

SQL server gets time out every time I run the file from “SQL Server Management Studio”.

Then I thought to do it from command prompt (whether anything can be done using cmd) as in my previous article I have used to replace text file from command prompt.

Then I came to know that you can do everything in sql server using command prompt. The main thing is you need to go to below path in command prompt. C:\Program Files\Microsoft SQL Server\100\Tools\Binn.
You just need to use below commands to connect to sql server using cmd.

sqlcmd -S lpc:ComputerName\<instancename> -U username -P password

Remember that, -S, -U -P are all case sensitive. Now you write any query but to fire the query you need to type go and then enter.

e.g. once you have typed above command correct, you’ll see
1> in your command prompt. You can write below thing.

1> select * from tbl1 then enter and then type Go then enter… it’ll show you result.

But, I need to run the script file so to run the file use below command.

sqlcmd -S lpc:ComputerName\<instancename> -U username -P password -i <MyScript.sql>

This will run this script file and give you output in command window. Suppose you need to get output in different file then you can use below command.

sqlcmd -S lpc:ComputerName\<instancename> -U username -P password -i <MyScript.sql> -o <MyOutput.txt>

If you want to know all the things of sqlcmd then use below command. It’ll give you list of all the tags.

sqlcmd -?

Again I came into another problem. I need to insert 15 Lac record and the script will not allow to run 1000 record per query. Now I am hope less with the command prompt as well

Then I came to know about bulk insert. I have another file with .dat extension and that file contains records like below

1;xyz;test
2;abc;temp

Create one table in sql server Table1.

Create table Table1(id bigint, name varchar(50), column1 varchar(100))

Run below query for bulk insert

BULK INSERT Table1
FROM ‘C:\filename.dat’
WITH
(
FIELDTERMINATOR =’;',
ROWTERMINATOR = ‘\n’
)

And it worked very well. By this way it has inserted all 15 Lac records in just 30 sec into database.

Happy Programming

Advertisement