Bulk Insert Files From Template With File Name –Powershell




Alter table  [dbo].[myRegions]
drop column filename  


 --1Generate XML format file 
 DECLARE @sql VARCHAR(4000) 
SET @sql='bcp test1.dbo.myRegions format nul -c -x -f  "C:\temp\test\myFormatmyRegions.xml" -T -t\t -S'+ @@servername
exec master..xp_cmdshell @sql

--Add a filename column to the target table
Alter table  [dbo].[myRegions]
Add filename varchar(200) null

 

 --    -t"|" for pipe delimited
 --   -t\t for tab delimited
 --   -t, for comma delimited


 



Import-Module -Name 'SQLPS' -DisableNameChecking  


$workdir="C:\temp\test\"
$svrname = "MC047012\MSSQL2014"


Try
{

#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0

 

$files = Get-ChildItem $workdir
 
for ($i=0; $i -lt $files.Count; $i++) {
$dt = (get-date).AddDays(-$i).ToString("yyyMMdd") 
$strFileName = "raw_US_$($dt).txt"
If (Test-Path "C:\temp\test\$strFileName"){

$query = @" 
BULK INSERT test1.dbo.myRegions from '$($workdir)$($strFileName)' WITH (FIELDTERMINATOR = '\t', FIRSTROW = 2, FORMATFILE = 'C:\temp\test\myFormatmyRegions.xml');
Update test1.dbo.myRegions SET filename= '$($strFileName)' WHERE filename is null;

"@


Invoke-Sqlcmd -ServerInstance $svr.Name  -Database master  -Query  $query 

}
}
}

Catch [Exception]
 {
 write-host "--$strFileName "$_.Exception.Message
 }





 

http://social.msdn.microsoft.com/Forums/en-US/3d195bb5-89b0-4ec4-bf50-bcd9b2ca9662/can-i-use-bulk-import-to-add-fields-to-a-table?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s