USE MASTER
GO
IF OBJECT_ID('sp_bcp') IS NULL
EXEC ('CREATE PROCEDURE sp_bcp AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE sp_bcp
@ObjectName VARCHAR(256) = NULL,
@DestinationServer VARCHAR(256) = NULL,
@DestinationObject VARCHAR(256) = NULL,
@TempFolder VARCHAR(256) = 'C:\Temp\'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF ((@ObjectName IS NULL
OR @DestinationObject IS NULL)
AND OBJECT_ID('tempdb..#Input') IS NULL)
OR @DestinationServer IS NULL
BEGIN
PRINT '-- Single object bcp creation
EXEC sp_bcp
@ObjectName = ''SchemaName.TableName'',
@DestinationServer = ''SQLSERVERNAME'',
@DestinationObject = ''Databasename.SchemaName.TableName'',
@TempFolder = ''C:\Temp\''
-- Multiple object bcp creation
IF OBJECT_ID(''TEMPDB..#Input'') IS NOT NULL DROP TABLE #Input
CREATE TABLE #Input (SourceObject VARCHAR(256) NOT NULL, DestinationObject VARCHAR(256) NOT NULL)
INSERT #Input (SourceObject, DestinationObject)
SELECT ''SchemaName.TableName1'' AS SourceObject, ''Databasename.SchemaName.TableName1'' AS DestinationObject
UNION ALL
SELECT ''SchemaName.TableName2'' AS SourceObject, ''Databasename.SchemaName.TableName2'' AS DestinationObject
UNION ALL
SELECT ''SchemaName.TableName3'' AS SourceObject, ''Databasename.SchemaName.TableName3'' AS DestinationObject
-- OR
INSERT #Input (SourceObject, DestinationObject)
SELECT s.name + ''.'' + t.name AS SourceObject, ''Temporary.dbo.'' + t.name AS DestinationObject
FROM sys.tables t (NOLOCK) INNER JOIN sys.schemas s (NOLOCK) ON s.schema_id = t.schema_id
WHERE s.name = ''tCommon''
EXEC sp_bcp
@DestinationServer = ''SQLSERVERNAME'',
@TempFolder = ''C:\Temp\''
'
RETURN
END
DECLARE @SourceDatabase VARCHAR(256) = DB_NAME(),
@BCPOut VARCHAR(MAX),
@BCPIn VARCHAR(MAX),
@DelFiles VARCHAR(MAX),
@TempFilePostfix VARCHAR(256) = '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), GETDATE(), 121), ' ', '_'), ':', ''), '-', ''),
@SQL VARCHAR(MAX)
IF OBJECT_ID('tempdb..#Input') IS NULL
BEGIN
CREATE TABLE #Input (SourceObject VARCHAR(256) NOT NULL,
DestinationObject VARCHAR(256) NOT NULL)
INSERT #Input
(SourceObject,
DestinationObject)
SELECT @ObjectName,
@DestinationObject
END
IF OBJECT_ID('tempdb..#Temp') IS NULL
CREATE TABLE #Temp (SourceObject VARCHAR(256) NOT NULL,
SchemaName VARCHAR(256) NULL,
Name VARCHAR(256) NULL,
HasIdentity BIT NULL,
DestinationObject VARCHAR(256) NOT NULL,
DestinationDatabase VARCHAR(256) NULL,
DestinationSchema VARCHAR(256) NULL,
DestinationTable VARCHAR(256) NULL,
FileName VARCHAR(1000) NULL)
INSERT #Temp
(SourceObject,
SchemaName,
Name,
HasIdentity,
DestinationObject,
DestinationDatabase,
DestinationSchema,
DestinationTable,
FileName)
SELECT i.SourceObject,
b.SchemaName,
b.TableName,
b.Hasidentity,
i.DestinationObject,
PARSENAME(DestinationObject, 3) AS DestinationDatabase,
PARSENAME(DestinationObject, 2) AS DestinationSchema,
PARSENAME(DestinationObject, 1) AS DestinationTable,
@TempFolder + @SourceDatabase + '_' + b.SchemaName + '_' + b.TableName + @TempFilePostfix + '.bcp' AS FileName
FROM #Input i
OUTER APPLY (SELECT s.name AS SchemaName,
t.name AS TableName,
CASE WHEN EXISTS ( SELECT *
FROM sys.columns c (NOLOCK)
WHERE c.OBJECT_ID = t.OBJECT_ID
AND C.is_identity = 1 ) THEN 1
ELSE 0
END AS HasIdentity
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
WHERE t.OBJECT_ID = OBJECT_ID(i.SourceObject)) b
SELECT @SQL = ISNULL((SELECT 'Failed to parse destination object(s), acceptable format is Database.Schema.Table : '
+ Util.dbo.StringConcat(DestinationObject, ', ') + '
'
FROM #Temp
WHERE DestinationDatabase IS NULL
OR DestinationSchema IS NULL
OR DestinationTable IS NULL), '')
+ ISNULL((SELECT 'Failed to locate object(s) in current database ' + @SourceDatabase + ' : ' + Util.dbo.StringConcat(SourceObject, ', ')
FROM #Temp
WHERE SchemaName IS NULL), '')
IF @SQL <> ''
BEGIN
RAISERROR(@SQL,16,1)
RETURN 30
END
PRINT '---- CREATE TABLE ON ' + @DestinationServer + '----'
SELECT @SQL = Util.dbo.StringConcat('EXEC sp_script
@ObjectName = ''' + SourceObject + ''',
@NewDatabaseName = ''' + DestinationDatabase + ''',
@DropTable = 1,
@NewDataCompression = ''ROW'',
@NewFileGroup = '''',
@NewPartition = '''',
@NewSchemaName = ''' + DestinationSchema + ''',
@NewTableName = ''' + DestinationTable + ''',
@CreateClusteredIndex = 1,
@CreateTable = 1,
@PrintSQL = 1', '
'),
@BCPOut = Util.dbo.StringConcat('"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" [' + @SourceDatabase + '].[' + SchemaName + '].['
+ Name + '] out ' + FileName + ' -n -S ' + @@SERVERNAME + ' -T', '
'),
@BCPIn = Util.dbo.StringConcat('"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" [' + DestinationDatabase + '].['
+ DestinationSchema + '].[' + DestinationTable + '] in ' + FileName + ' -n -S ' + @DestinationServer + ' -T'
+ CASE WHEN HasIdentity = 1 THEN ' -E'
ELSE ''
END, '
'),
@DelFiles = Util.dbo.StringConcat('DEL ' + FileName, '
')
FROM #Temp
EXEC (@SQL)
PRINT ''
PRINT '---- BCP OUT DATA ----'
EXEC Util.dbo.PrintLargeText
@BCPOut
PRINT ''
PRINT '---- BCP IN DATA ----'
EXEC Util.dbo.PrintLargeText
@BCPIn
PRINT ''
PRINT '---- DELETE FILES ----'
EXEC Util.dbo.PrintLargeText
@DelFiles
IF OBJECT_ID('tempdb..#Input') IS NOT NULL
DROP TABLE #Input
GO
EXEC sys.sp_ms_marksystemobject
sp_bcp
GO