当前位置：文江博客话题详情

如何使用 Powershell 将共享点列表插入 Oracle DB

发布于 2024-11-29 17:29:55 字数 125 浏览 0 评论 0原文

我想从共享点列表中检索一些列表项，然后将其加载到 Oracle 表中。使用 powershell 是否可行？任何想法或想法将不胜感激。谢谢 ** 在插入 Oracle 之前在 powershell 中准备表的最佳方法是什么？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眼中杀气 2024-12-06 17:29:55

我已经使用 SharePoint 和 SQL Server 完成了此操作。首先，您需要查询 SharePoint 列表，如我的博客文章中所述：

http://sev17.com/2009/02/using-oledb-with-sharepoint-lists/

其次，您需要插入到表中。在 SQL Server 中，我将使用 SQLBulkCopy 类，Oracle 也有类似的类，但它可能同样容易从 SharePoint 返回的 DataTable 生成和执行插入语句。这件作品需要做一些工作。查看 $dt 变量并生成插入语句。例如

$dt | foreach {"INSERT myOracleTable VALUES ('$($.Field1)','$($.Field2)');"}

您可以生成一个文件并使用 SQLPLus 来执行该文件：

$dt | foreach {"INSERT myOracleTable VALUES ('$($.Field1)','$($.Field2)');"} >>> ./out.sql

或者，我有一个由两部分组成的系列，用于从 Powershell 查询 Oracle：

http:// /sev17.com/tag/oracle/

编辑添加的示例：

#Select
$connString = ‘Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://sharepoint.acme.com/IT/DBAdmin/;LIST={a113df9b-e56e-49d2-b786-03d170d18dbc};‘
$spConn = new-object System.Data.OleDb.OleDbConnection($connString)
$spConn.open()
$qry=‘Select * from list’
$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$spConn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
$da.fill($dt) > $null
$dt

I've done this with SharePoint and SQL Server. There's two parts first you'll want to query a SharePoint list as documented in my blog post:

http://sev17.com/2009/02/using-oledb-with-sharepoint-lists/

Second you'll want insert into a table. In SQL Server I would use the SQLBulkCopy class, there's a similar class for Oracle, but its probably just as easy to generate and execute insert statements from the DataTable returned from SharePoint. This piece will require a bit of work. Take a look at $dt variable and generate insert statemens. As an example

$dt | foreach {"INSERT myOracleTable VALUES ('$($.Field1)','$($.Field2)');"}

You could generate a file and use SQLPLus to execute the file:

$dt | foreach {"INSERT myOracleTable VALUES ('$($.Field1)','$($.Field2)');"} >> ./out.sql

Or, I've got a two-part series for querying Oracle from Powershell here:

http://sev17.com/tag/oracle/

Edit addded example:

#Select
$connString = ‘Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://sharepoint.acme.com/IT/DBAdmin/;LIST={a113df9b-e56e-49d2-b786-03d170d18dbc};‘
$spConn = new-object System.Data.OleDb.OleDbConnection($connString)
$spConn.open()
$qry=‘Select * from list’
$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$spConn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
$da.fill($dt) > $null
$dt

回复收藏 0 原文