0

The problem I am facing lies with the use of the invoke-sqlcmd command below

$result = Invoke-Sqlcmd -InputFile $sqlfile -ServerInstance $DB

The example sql within $sqlfile is:

SELECT TOP 2 FROM [TABLE1]
SELECT TOP 3 FROM [TABLE2]

The kind of output I am after would be a single result with both tables one after the other.

For example..

TABLE1
data1
data2
TABLE2
data1
data2
data3

Is this even possible?

I have attempted the same using Invoke-Sqlcmd2 thinking that a PSCustomObject data type would be easier to play with..

$result = Invoke-Sqlcmd2 -InputFile $sqlfile-ServerInstance $DB -As PSObject

However this seems to only process the first select query and ignore the second.

Inammathe
  • 103
  • 1
  • 5

1 Answers1

0

Have you considered doing a CROSS JOIN in the SQL File ? Consider the following from: here

You would do something like:

SELECT * FROM Table1
CROSS JOIN Table2

And this will give you all data associated between both tables regardless if there is a relationship or not. If you want to JOIN via relationship, check some of the other SQL Joins, such as LEFT JOIN, INNER JOIN, etc

Community
  • 1
  • 1
James Kirsch
  • 72
  • 6
  • If you want to add specific columns, you would just replace the * with the column names, such as col1, col2 .. etc – James Kirsch Dec 30 '15 at 00:50
  • Apologies, I should have been more specific with the SQL. A real life example of how this would work would not really work with any joins. For example, `select transtime, amount, accountid from #mytable1` `select count(accountid),sum(amount) from #mytable1` The sum data wouldn't really work being joined unfortunately. In this instance, the purpose of the two outputs would be to 1.) Display some data from the temporary table #mytable1 2.) Display the count of the accountid column and summary of the amount column. You are correct though, that solution would work in other cases – Inammathe Dec 30 '15 at 01:09
  • Ahh alright, in this case it might be worthwhile to look into Stored Procedures if they will suit your case, as far as hooking these up with Powershell I am not too familiar with, [this](http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values/43767) might help for some of the SQL – James Kirsch Dec 30 '15 at 02:19