Several ways exists to import CSV (excel) data into your SQL Server Database.

One is of course, using SSIS. The other one – similar to SSIS – is using import/export function in SSMS. With wizard, you will be able to import data.

Futher on, BULK INSERT (BCP) statement is to all DBA and developers very close way of importing data. Another T-SQL statement is selecting from OPENDATASOURCE; simple and fast way of doing this (also you might want to check Linked Servers). In c# there is straightforward class SqlBulkCopy in System.Data.SqlClient namespace. Going into script language, PowerShell is also a fast and neat way to import CSV into your SQL Server. Script Guy server four ways how to do it with PS. I usually use c# namespace and same class.

Another way, going back to T-SQL, is using OLE Automation stored procedure, using standard set of sp_OAMethod. And also natively compiled stored procedures will also bring you same functionality.

With R integration in SQL Server with Microsoft R Server, importing CSV data in SQL Server can be done with sp_execute_external_script as well. With following snippet you can import data from csv into T-SQL

EXECUTE sp_execute_external_script    
        @language = N'R'    
    , @script = N'
                mydata <- data.frame(read.csv("C:\\MyFolder\\Book1.csv", 
sep=";", HEADER=FALSE))
                colnames(mydata)[1] <- "numb"
                colnames(mydata)[2] <- "charc"
                OutputDataset <- mydata;'

WITH RESULT SETS (( 
                     numb INT
                    ,charc VARCHAR(10)
                    ));

Or even shorter input file directly assigned to OutputDataSet:

EXECUTE sp_execute_external_script    
        @language = N'R'    
       ,@script=N'OutputDataSet<- read.csv("C:\\MyFolder\\Book1.csv")
            colnames(OutputDataSet)[1] <- "numb"
            colnames(OutputDataSet)[2] <- "charc;'
WITH result sets ((numb int),(charc VARCHAR(10)));

Creating stored procedure (with above execute statement we create procedure call ImportCSV ) with this script you can store data directly into table:

DECLARE @result TABLE (cifra varchar(10))
INSERT INTO @result
EXECUTE ImportCSV
SELECT * FROM @result

And this is my Book1.csv sample file:

So, another way to store data from CSV into SQL Server.

Happy R-SQLing