As I was still puzzle why the SMO *.TruncateData() was missing in SQL Server 2014, I needed to find a quick workaround to continue with my data migration.
Of course, here comes T-SQL scripting to the rescue:
[sourcecode language=”sql”]
Truncate Table databasename.schema.tablename
GO
[/sourcecode]
Basically, subtitute the SMO .TruncateData() with few lines of T-SQL statement, and have PowerShell run the code against SQL Server. The script is shown below:
[sourcecode language=”powershell”]
## —————————————– ##
## – Using SMO steps to work with tables:
$SQLServerInstanceName = ‘TSQLDSP01’; $global:SQLServerDatabasename = ‘devMaxText’;
$global:DbSchema = ‘dbo
[system.reflection.assembly]::LoadWithPartialName(“Microsoft.SQLServer.Smo”) | Out-Null;
$SQLSrvObj = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLServerInstanceName;
$tables = $SQLSrvObj.Databases[$global:SQLServerDatabaseName].tables;
$global:cnt = 1;
foreach ($t in $tables.name)
{
$tsqry = @”
Truncate Table $($global:SQLServerDatabasename).$($global:DbSchema).$($t)
Go
“@;
#$SQLSrvObj.Databases[$SQLServerDatabaseName].ExecuteNonQuery($tsqry);
Write-Verbose “[$($global:cnt.ToString(“0000″))]Truncate Table dgSAP_Old.dbo.$($t) Process” -Verbose;
$global:cnt++;
};
[/sourcecode]
This *script will assist in the third-party application to load date back to the tables for our migration.
*Note: Just in case, I commented out the line having the .ExecuteNonQuery() method.