How to insert data into SQL Server table using CSV as source files using Powershell.

·         Open powershell window and use the below code.

#Export the Output of CSV to SQL Tables

$csvFile = get-ChildItem "D:\CSVFiles\*.csv"      

$sqlsvr = "SQL Server Host Name"           

$database = "Database Name"                 

$table = "Table Name"                                 

$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Server = $Sqlsvr; Database = $database; User ID= username; Password= Password"

$conn.Open()

$cmd = $conn.CreateCommand()

Import-Csv $csvFile | %{$cmd.CommandText = "INSERT INTO $table (Emp_Id, Emp_name, Mobile) VALUES ('$($_.Emp_Id)','$($_.Emp_name)','$($_.Mobile)')"

$cmd.ExecuteNonQuery()

}

·         $csvFile = Define the location/path of .csv files

·         $sqlsvr = Host name of SQL Server (E.g. MSSQLSERVER)

·         $database = Name of database (E.g. HR)

·         $table = Name of table which is to be upload. (E.g. Employee)

·         Change the variables as per the requirement like SQL Server, Database, Tables and Username, password to establish connection with SQL Server.

·         In the Insert into statement define the column names of the respective table accordingly to get it update.