INSERT INTO tblAssetRelations from Excel file?

[dianmo]

Hello,

i have 2 questions regarding insertion into a table to lansweeper.
so i have made this sql statement to insert assets into the tblAssetRelations table for their relationship, which hopefully is correct,

INSERT INTO tblAssetRelations (ParentAssetID, ChildAssetID, Type)
VALUES (
(SELECT tblAssets.AssetID FROM tblAssets WHERE tblAssets.AssetName = "?Column1"),
(SELECT tblAssets.AssetID FROM tblAssets WHERE tblAssets.AssetName = "?Column2"),
3
);

1. question: where in Lansweeper can i test this query to see if everything is correct.
2. the attributes ?Column1 and ?Column2 are columns from an Excel sheet with assetnames, how can i (automate) this query for all 1000 rows of my excel sheet.

I will be provided with a 2nd instance of Lansweeper so i can test some things in case somthing breaks...

I hope someone can help me with this problem, thanks in advance!

[dianmo]

i have searched around and modified a script for powershell to fulfill my needs, will this work?

## Modules needed to run this code
install-module sqlserver -AllowClobber -Force
Import-Module sqlserver  
Install-Module ImportExcel

### Connects the SQL DB
Set-Location SQLSERVER:\sql\SQLSERVERNAME\INSTANACENAME\databases\DBNAME

### assuming the headers are column1 and column2
$excelAssets = Import-Excel C:\temp\info.xslx
foreach($row in $excelAssets ){
 $field1 = "'" + $row.column1 + "'"
 $field2 = "'" + $row.column2 + "'"
 $sqlQuery = "INSERT INTO tblAssetRelations (ParentAssetID, ChildAssetID, Type)
VALUES (
	(SELECT tblAssets.AssetID FROM tblAssets WHERE tblAssets.AssetName = $field2),
	(SELECT tblAssets.AssetID FROM tblAssets WHERE tblAssets.AssetName = $field1),
	12
	);"
 Invoke-Sqlcmd -Query $sqlQuery
 }

i hope someone can help me regarding that line:
Set-Location SQLSERVER:\sql\SQLSERVERNAME\INSTANACENAME\databases\DBNAME (its just a placeholder for now)

is it really that easy to connect to the db? arent some credentials necessary? and where can i find the path to the instance for my db?

[drobertson]

You can use the "DatabaseMaintenance.exe" file in the 'Tools' folder of your lansweeper installation to run some basic sql commands on your lansweeper DB.. (click on script execution tab in the program)


However, I would write a powershell script that does all of that. You can insert data into an SQL database using powershell and you can also import data to sql from an excel/csv file using powershell.... Writing the script would take a little time but if you research how to insert to sql from csv with powershell you should be able to accomplish what you want.


Here is a powershell function I wrote to connect with sql, it may provide you some guidance.

function RunMSSQLQuery {

    param(
        [Parameter(Mandatory = $true)][String] $mssql_server_instance,
        [Parameter(Mandatory = $true)][String] $mssql_databasename,
        [Parameter(Mandatory = $true)][String] $mssql_query
     )

$dbquery = $mssql_query

#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
#$connectionTemplate = "Data Source={0};Integrated Security=false;Initial Catalog={1};User ID=$ms_uid;Password =$ms_pwd;"

$connectionString = [string]::Format($connectionTemplate, $mssql_server_instance, $mssql_databasename)
#Establish connection to SQL server
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
#Execute database query command
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $dbquery
$command.Connection = $connection
$command.CommandTimeout = 0
#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$EXECUTE_QUERY = $SqlAdapter.Fill($DataSet)
$connection.Close()

if ($EXECUTE_QUERY)
{
Write-Host `n"Query executed: "$dbquery
}

 return  $dbquery
}