Insert data from CSV file into SQL Server table

Introduction:

Here, I will explain how to insert data from CSV file into SQL Server table.

Description:

Recently, I did one task with requirement to insert bulk data from CSV file into SQL Server table. As you know, this is very simple task if we have same number of columns with same sequence in CSV file as table.

But to accomplish this task, I had CSV file containing same number of columns with same sequence as table EXCEPT ID column of table which was Identity Column with auto increment value. I had tried with various scripts but 1 column was missing in CSV file from table so NO scripts were allowing me to insert data into table.

To resolve error, I simply added dummy ID column into CSV file and data inserted properly into table.

Student table in SQL Server :

Original CSV file (Student.CSV) :

Updated CSV file :

You can use following script to process CSV file and insert data into table :

BULK

INSERT Student -- table name

FROM 'E:\Student.csv' -- Path of CSV file

WITH

(

          FIRSTROW = 2, -- This is used to start reading process from Row 2 of CSV file.

          FIELDTERMINATOR = ',',

          ROWTERMINATOR = '\n'

)

Data in Student table after running script :

Restrict Database Access of User in SQL Server

Introduction: 

Here, I will explain steps to allow only single database access to user in SQL server.

Description:

While publishing SQL server database on hosting, I have found that I can do operation (Insert, Update, Delete, Select) in my published databases only. Here, interesting thing was that even I cannot open new Query windows for other User's database. We can also do same thing in SQL Server. To do this, please follow below steps :

Connect in SQL Server.

Go to Object Explorer.

From Object Explorer, open Security tab / Logins tab. Right click on Logins tab and click on New Login... option which will open window as shown below :

In General tab, set Login name, select SQL Server authentication and set password and uncheck Enforce password policy option.

In User Mapping tab, select database (You can select any number of databases) for which you like to restrict access for User from "Users mapped to this login" section and select all roles EXCEPT "db_denydatareader" and "db_denydatawriter" from "Database role membership for : Selected Database name" section (You can select any number of roles from list).

After selecting database and roles, click OK.