I have multiple remote machines on a network that have a csv file on them stored in the same location that has been created by a powershell script.
Whats the best way to insert that data into a Microsoft Sql Server express database?
5
-
Depends on the dbms product used...jarlh– jarlh2015-11-05 08:39:26 +00:00Commented Nov 5, 2015 at 8:39
-
Microsoft Sql Server expressIcelander– Icelander2015-11-05 08:40:33 +00:00Commented Nov 5, 2015 at 8:40
-
Which version of SQL Server Express? Could you please paste a portion of your CSV? Are these files identically in structure on all machines? Are the of identical structure in all lines?Gottfried Lesigang– Gottfried Lesigang2015-11-05 08:49:04 +00:00Commented Nov 5, 2015 at 8:49
-
I am using Microsoft SQL Server Express with Visual Studio Community 2015.Icelander– Icelander2015-11-05 09:05:45 +00:00Commented Nov 5, 2015 at 9:05
-
Sample code isSystemSyncTime SystemSyncDate ComputerName 18:57:32 05-11-15 MINOIcelander– Icelander2015-11-05 09:05:55 +00:00Commented Nov 5, 2015 at 9:05
Add a comment
|
1 Answer
One way of doing this is to collect all the CSV-files from all the servers to your script server. And then run the SQL INSERT query from the script server to put your data in an SQL table.
As described in one of my previous answers you can use this module Invoke-SQLCmd2 to do this.
The other way, when you don't want to collect all the CSV-files first, is running the SQL INSERT query from every server that has a CSV-file. To do this you have to connect to the other server and then import the module from the script server, so you can use it:
Import-Module -Name '\\SCRIPTSERVER\C$\Windows\system32\WindowsPowerShell\v1.0\Modules\Invoke-SQL'
2 Comments
Icelander
Next question is can the remote machine insert data directly into the sql database. I would like to know a good way to insert data from a remote machine into a sql database
DarkLite1
Answer updated, you can import the module from any server once it's installed on your script server for example.