Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Set-based Insert from PoSh to SQL table Expand / Collapse
Author
Message
Posted Tuesday, May 22, 2012 1:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:36 AM
Points: 402, Visits: 859
Hi all,

I've been trying to find a way to do set-based inserts into a SQL Server.

As near as I can tell, there are only 2 ways:
1) convert the data to be inserted to XML, pass that SQL Server, either a sproc or a file;
2) export to file and BULK INSERT in SQL Server.

Neither of these are especially appealing.

Is there not something equivalent to
$Table.Insert($NewData)

Save-IntoSQLTable appears to be on the right track, but also seems to want only to work with traces.

Any ideas?




Post #1304432
Posted Tuesday, May 22, 2012 1:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
Anything you can do in .NET you can do in PoSH. Lookup Table-valued Parameters, and how to use them to insert sets of data using one in a Stored Procedure. Most if not all of the examples will be in C# or VB.net, but like I said, you can port that code to PoSH. TVPs or the .NET class SqlBulkCopy are the two best ways I know of to insert batches of data from memory directly into SQL Server. If you write the data to disk that opens up all the bulk load methods of course, like bcp, BULK INSERT, SSIS, etc.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1304487
Posted Wednesday, May 23, 2012 8:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:36 AM
Points: 402, Visits: 859
Oooooohhh. Me like TVP!

Thanks opc!





Post #1304971
Posted Wednesday, July 04, 2012 5:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:10 PM
Points: 158, Visits: 755
USe Chad Miller´s Out-DataTable and Write-DataTable

it use Bulk Insert.


$hell your Experience !!!
Post #1324961
Posted Wednesday, July 04, 2012 6:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:36 AM
Points: 402, Visits: 859
Thanks Laerte!

Found you a couple of months ago, I appreciate your work.

I also found these functions last week.

P



Post #1324972
Posted Wednesday, July 04, 2012 6:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:10 PM
Points: 158, Visits: 755
Anytime Man :)

$hell your Experience !!!
Post #1324974
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse