UPDATE/INSERT SQL Table using Powershell

Question

So I need to update an existing Table with data from a text file each time the scripts runs. My current code drops the table and creates a new one each time it is ran which cause about a 10 second delay which I need avoid. I would like to instead Update the data without re-creating the table (hopefully alleviating the delay?). I understand the UPDATE or MERGE command can be used for this but haven't been able to figure out the syntax.

Function CreateAnalystTable($location, $file, $extension, $server, $database)
{
    $full = $location + $file + $extension
    $all = Get-Content $full
    $columns = $all[0]
        $columns = $columns.Replace(";","] VARCHAR(255), [")
    $table = "CREATE TABLE " + $file + "([" + $columns + "] VARCHAR(255))"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $buildTable = New-Object System.Data.SqlClient.SqlCommand
    $insertData = New-Object System.Data.SqlClient.SqlCommand
    $connection.ConnectionString = "Data Source=" + $server + ";Database=" + $database + ";integrated security=true"
    $buildTable.CommandText = $table
    $buildTable.Connection = $connection
    ## Added to function
    $x = 0
    $insertData.CommandText = "EXECUTE stp_CommaBulkInsert @1,@2"
    $insertData.Parameters.Add("@1", $full)
    $insertData.Parameters.Add("@2", $file)
    $insertData.Connection = $connection
    $connection.Open()
    $buildTable.ExecuteNonQuery()
    $connection.Close()
    ## Added to function
    $x = 1
    if ($x = 1)
    {
        $connection.Open()
        $insertData.ExecuteNonQuery()
        $connection.Close()
    }
}
CreateAnalystTable -location "PATH" -file "FILE" -extension ".txt" -server "SERVER" -database "DB"

SQL:

CREATE PROCEDURE stp_CommaBulkInsert
@file NVARCHAR(250), @table NVARCHAR(250)
AS
BEGIN
 DECLARE @f NVARCHAR(250), @t NVARCHAR(250), @s NVARCHAR(MAX)
 SET @f = @file
 SET @t = @table

 SET @s = N'BULK INSERT ' + @t + '
  FROM ''' + @f + '''
  WITH (
   FIELDTERMINATOR = '';''
   ,ROWTERMINATOR = ''0x0a''
   ,FIRSTROW=2
  )'

 EXEC sp_executesql @s
END

Any help is appreciated

[This](http://stackoverflow.com/questions/16979860/sql-update-table-from-a-text-file) may help you. — BJones, Aug 25 '15 at 22:05

score 0 · Answer 1 · answered Aug 25 '15 at 22:24

0

I haven't done powershell coding, but this bit of code will always run and execute, though I don't see why it is needed. Unless you need to run your stp twice.

$x = 1
if ($x = 1)
{
    $connection.Open()
    $insertData.ExecuteNonQuery()
    $connection.Close()
}

For the UPDATE statement, I would use this site as an example of a basic UPDATE statement.

http://www.w3schools.com/sql/sql_update.asp

Just change your $table and $column to accommodate the format, and it should run.

answered Aug 25 '15 at 22:24

jcameron47

65
11

Sorry forgot to mention i'm going to be looping it, i need the data in the table to update repeatedly as fast as possible. maybe sql isn't best for this? any other ideas if not? – Logan Lower Aug 25 '15 at 22:52
Databases are great to get and set what you need, but if you are going to run basic updated data, I would run that in the shell, or another program, until you are done. Then I would update the database once completed. – jcameron47 Aug 25 '15 at 22:56

UPDATE/INSERT SQL Table using Powershell

1 Answers1