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.

SQL Export node in IBM SPSS

Use SQL as Export Node in SPSS

Create an ODBC connection on the Modeler server.

Open ODBC and click on System DSN, define the name and the SQL server from the drop down list.

Click Next and either use Windows Authentication or Server authentication, in case of server authentication enter the username and password.

Click next and select the default database from SQL databases where you want to export data.

Click next with the default settings and click Finish & Test Data Source.

Next screens will appear like

To use SQL database in the export node, drag it into the stream canvas and click edit.

From the data Source drop down, click on “Add new database connection”.

New window will pop up with available data sources and select the created data source in ODBC and click Connect button.

Now in case you want to create new table define the name of table and click OK/Run.