SQL to APS Automation

-- SMP SQL Server to APS Import Automation
-- Alain Dormehl
-- July 2015
-- Twitter: APSolutely

-- These scripts have been tested on all major releases of SQL Server from 2008 R2 and up. 
-- They can also be automated to loop through all tables and execute the master..xp_cmdshell
-- Destination APS Appliance AU3, running over INFINIBAND 
-- This is hands down the quickest way to transfer large amounts of tables as quickly as possible 
-- from any SMP sql environment into the APS. 

--******************************************************************************
--*   Copyright (C) 2015 Alain Dormehl
--*   All rights reserved. 
--*
--*
--*   You may alter this code for your own *non-commercial* purposes. You may
--*   republish altered code as long as you include this copyright and give due credit. 
--*
--*
--*   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
--*   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
--*   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
--*   PARTICULAR PURPOSE. 
--*
--******************************************************************************
--VARIABLE DECLARATION 
DECLARE@distributionVARCHAR(25)= 'id'--LEAVE BLANK FOR REPLICATE,HOW DO YOU WANT TO DISTRIBUTE IF IT IS A PDW  
DECLARE@pdwIndexVARCHAR(50)= 'id'--LEAVE BLANK FOR HEAP, COLUMN NAME FOR CI AND CCI FOR CLUSTERED COLUMN STORE
DECLARE@sourceDBVARCHAR(50)= 'TestDB'--DATABASE THAT YOU WANT TO CREATE BCP OUTPUTS FOR    
DECLARE@OverrideCharCHAR(1)         = '$'--CERTAIN SPECIAL CHARACTERS CANNOT BE USED IN THE BCP QUERYOUT TXT FILE (I.E '$')
DECLARE@DirectoryVARCHAR(256)    = 'xx:\BCP\'--DIRECTORY WHERE BCP QUERYOUT WILL EXPORT AND DWLOADER.EXE WILL USE TO IMPORT
DECLARE@ServerVARCHAR(50)= 'SMPSQL'--SMP SQL SERVER INSTANCE NAME OR IP
DECLARE@UsernameVARCHAR(50)= 'TestUser'--USERNAME FOR SMP SQL ENVIRONMENT, LEAVE EMPTY FOR WINDOWS AUTHENTICATE
DECLARE@PasswordVARCHAR(50)= 'Password'--PASSWORD FOR SMP SQL ENVIRONMENT, LEAVE BLANK FOR WINDOWS AUTHENTICATE
DECLARE@PDWNameVARCHAR(100)= 'PDW1-SQLCTL01'--USE PDW DNS NAME, DWLOADER.EXE IS NOT OVERLY FOND OF IP. SHOULD ALREADY BE CONFIGURED FOR IB
DECLARE@PDWFinalTableVARCHAR(150)= 'TestDB'--NAME OF THE FINAL TABLE IN THE APS
DECLARE@ImportMethodVARCHAR(50)= 'reload'--DWLOADER.EXE IMPORT METHOD (RELOAD, APPEND)
DECLARE@PDWUsernameVARCHAR(50)= 'TestUser'
DECLARE@PDWPasswordVARCHAR(50)= 'Password'

;WITH CTE1 
AS
(
SELECT 
ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY TABLE_SCHEMA) AS ID,
TABLE_NAME,
TABLE_SCHEMA,
TABLE_CATALOG,
ColumnData_List,
CreateTableSQL = 'USE '+ @PDWFinalTable +' ; CREATE TABLE [' + @PDWFinalTable + '].[' + TABLE_SCHEMA + '].' + CreateTableSQL 
FROM 
(
SELECT  so.name AS TableName, 
id AS ObjectID,
o.list AS ColumnData_List,
CreateTableSQL = '[' + so.name + '] (' 
+ CASE WHEN RIGHT(RTRIM(LTRIM(o.list)),1) = ',' THEN SUBSTRING(o.list,1,LEN(o.list) - 2) ELSE o.list END 
+ ')' 
+ ' WITH (DISTRIBUTION = ' + CASE WHEN @distribution <> '' THEN  + 'HASH(' + @distribution + ')' ELSE 'REPLICATE' END  
+ CASE WHEN @pdwIndex = '' THEN ')'
   WHEN @pdwIndex = 'CCI' THEN ' , CLUSTERED COLUMNSTORE INDEX)' 
   WHEN o.list LIKE '%' + @pdwIndex + '%' THEN ' ,CLUSTERED INDEX(' + @pdwIndex + '))' 
   ELSE ')'  END 
FROM  sysobjects so
CROSS APPLY
    (SELECT 
'[' + COLUMN_NAME + '] ' 
+ DATA_TYPE 
+ CASE DATA_TYPE WHEN 'sql_variant' THEN ''
 WHEN 'text' THEN ''
 WHEN 'ntext' THEN ''
 WHEN 'xml' THEN ''
 WHEN 'decimal' THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
  ELSE COALESCE('(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN '8000' ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END +')','') END 
  + ' ' 
  + (CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END) + 'NULL ' 
  + ', ' 
    FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = so.name
    ORDER BY ORDINAL_POSITION 
    FOR XML PATH('')) o (list)
CROSS APPLY
    (SELECT '[' + COLUMN_NAME + '], '
     FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
     ORDER BY ORDINAL_POSITION
     FOR XML PATH('')) j (list)
WHERE   xtype = 'U' AND name NOT IN ('dtproperties')
) AS Tablez
LEFT JOIN INFORMATION_SCHEMA.TABLES tblz
ON Tablez.TableName = tblz.TABLE_NAME
),
CTE2
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY TABLE_SCHEMA) AS ID,
TABLE_NAME,
TABLE_SCHEMA,
TABLE_CATALOG,
list AS BCPOut_SelectColumn_List, 
SelectSQL = 'SELECT ' + CASE WHEN RIGHT(RTRIM(LTRIM(list)),1) = ',' THEN SUBSTRING(list,1,LEN(list) - 2) ELSE list END 
+ ' FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM 
(
SELECT  o.list,
name 
FROM  sysobjects so
CROSS APPLY
    (SELECT CASE WHEN DATA_TYPE IN ('datetime2','datetime') THEN 'CONVERT(VARCHAR(8),[' + COLUMN_NAME + '],112) AS [' + COLUMN_NAME + '] , '
 ELSE '[' + COLUMN_NAME + '] , ' END 
    FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = so.name
    ORDER BY ORDINAL_POSITION 
    FOR XML PATH('')) o (list)
WHERE   xtype = 'U' AND name NOT IN ('dtproperties')
) AS Tablez LEFT JOIN INFORMATION_SCHEMA.TABLES tblz 
ON Tablez.name = tblz.TABLE_NAME 
)
SELECT 
TABLE_NAME = CTE1.TABLE_SCHEMA + '.' + CTE1.TABLE_NAME,
BCP_CMD = 'BCP "' + SelectSQL + '" queryout ' + @Directory + REPLACE(CTE1.TABLE_NAME,@OverrideChar,'') + '.txt -S ' + @Server + ' ' + CASE WHEN @Username + @Password = '' THEN '-T ' ELSE ' -U ' + @Username + ' -P ' + @Password  END + ' -e ' + @Directory + 'error.txt -E -k -w -t "~|~" -r \n',
DWL_CMD = 'dwloader.exe -E -M '+ @ImportMethod + ' ' + CASE WHEN @PDWUsername + @PDWPassword = '' THEN '-W' ELSE '-U ' + @PDWUsername + ' -P ' + @PDWPassword END + ' -e UTF16 -i ' + @Directory  + REPLACE(CTE1.TABLE_NAME,@OverrideChar,'') + '.txt -S ' + @PDWName + ' -T [' + @PDWFinalTable + '].[' + CTE1.TABLE_SCHEMA + '].[' + CTE1.TABLE_NAME + '] -R ' + @Directory + 'error.bad -t 0x7E0x7C0x7E -r 0x0D0x0A',
CTE1.CreateTableSQL AS PDWCreateTable
FROM CTE1 INNER JOIN CTE2
ON CTE1.TABLE_SCHEMA + '.' + CTE1.TABLE_NAME = CTE2.TABLE_SCHEMA + '.' + CTE2.TABLE_NAME

/********************************OUTPUT SAMPLE **********************************************/--Table_Name : dbo.database_file

--BCP_CMD : BCP "SELECT [filegroup_id] , [sequence] , [root_path] , [is_add_from_alter] , [percent_allocated_space] FROM [DWConfiguration].[dbo].[database_file]" queryout xx:\BCP\database_file.txt -S SMPSQL  -U TestUser -P Password -e xx:\BCP\error.txt -E -k -w -t "~|~" -r \n

--DWL_CMD : dwloader.exe -E -M reload -U TestUser -P Password -e UTF16 -i xx:\BCP\database_file.txt -S PDW1-SQLCTL01 -T [TestDB].[dbo].[database_file] -R xx:\BCP\error.bad -t 0x7E0x7C0x7E -r 0x0D0x0A

--PDWCreateTable : USE TestDB ; CREATE TABLE [TestDB].[dbo].[database_file] ([filegroup_id] int NOT NULL , [sequence] int NOT NULL , [root_path] nvarchar(1000) NOT NULL , [is_add_from_alter] bit NOT NULL , [percent_allocated_space] float NULL) WITH (DISTRIBUTION = HASH(id) ,CLUSTERED INDEX(id))