Goodness, yes, you can't process that with BULK INSERT. The ideal solution is to get your Unix command changed so that it outputs a file with rows and columns. Essentially, the processing that you're trying to do above should be done by the Unix command. Is that an option?
John
Don't be so sure, yet. 😉 The UNIX output may have the leading spaces I asked about built in.
Oh, I see - two blanks followed by column 3 and then column 4. Sneaky!
John
1.
yes output is exactly
ServerNameÄServer.dbÄPowerUserÄRole
SERVER1ÄSERVER1.TestÄuser1Ä1
ÄÄuser_XÄ1
SERVER1ÄSERVER1.Test2ÄUser3Ä1
ÄÄUser_XÄ1
ÄÄUser_YÄ3
SERVER3ÄSERVER3.Test4ÄUser1Ä2
ÄÄUser_XÄ1
2. Unix command ...
command is unix script ,I have to use custom build PowerShell cmdlet that call those Unix scripts, return of this cmdlet could be assigned to objects of you choice
for now I simply do inside my powershell script
$x = (Run-OurUnixCmd -command "getpriv -server ServerX -user").split(" `n")
and then I work with $x ...
No need for PowerShell to import to SQL Server, then. Just do a BULK INSERT using a space as an assigned delimiter. If you have an IDENTITY column on the receiving table, the order of the input will be preserved and you can simple do a "data smear" to copy the data down in columns 1 and 2.