SQL产生BCP脚本

WITH
DATA AS(

SELECT
		publication_name = N'',

		publisher = N'ServerSource.DBName.dbo.TBName',

		subscriber_server = N'ServerTarget',
		subscriber_database = N'DBName',
		subscriber_schema = N'dbo',
		subscriber_object = N'TBName',

		row_filter = null,
		column_filter = NULL,

		insert_cmd = NULL,
		delete_cmd = NULL,
		update_cmd = NULL
),
REQ AS(
	SELECT
		publication_name,

		PUB.publisher,

		subscriber_server = SUBSRV.subscriber_server,
		subscriber_database = SUBDB.subscriber_database,
		subscriber_schema, subscriber_object,

		row_filter, column_filter,

		insert_cmd, delete_cmd, update_cmd
	FROM DATA
		OUTER APPLY(
			SELECT
				subscriber_server = LTRIM(RTRIM(T.c.value('.[1]', 'sysname')))
			FROM(
				SELECT subscriber_server = CONVERT(xml, 
														N'<c><![CDATA['
															+ REPLACE(DATA.subscriber_server, N',', N']]></c><c><![CDATA[')
															+ N']]></c>')
			)A
				CROSS APPLY A.subscriber_server.nodes('/c') T(c)
		)SUBSRV
		OUTER APPLY(
			SELECT
				subscriber_database = LTRIM(RTRIM(T.c.value('.[1]', 'sysname')))
			FROM(
				SELECT subscriber_database = CONVERT(xml, 
														N'<c><![CDATA['
															+ REPLACE(DATA.subscriber_database, N',', N']]></c><c><![CDATA[')
															+ N']]></c>')
			)A
				CROSS APPLY A.subscriber_database.nodes('/c') T(c)
		)SUBDB
		CROSS APPLY(
			SELECT
				publisher = QUOTENAME(ISNULL(PARSENAME(publisher, 4), PARSENAME(publisher_top, 4)))
							+ N'.' + QUOTENAME(ISNULL(PARSENAME(publisher, 3), PARSENAME(publisher_top, 3)))
							+ N'.' + QUOTENAME(ISNULL(PARSENAME(publisher, 2), PARSENAME(publisher_top, 2)))
							+ N'.' + PARSENAME(publisher, 1)
			FROM(
				SELECT
					publisher_top = LTRIM(RTRIM(T.c.value('../*[1]', 'sysname'))),
					publisher = LTRIM(RTRIM(T.c.value('.[1]', 'sysname')))
				FROM(
					SELECT publisher = CONVERT(xml, 
															N'<c><![CDATA['
																+ REPLACE(DATA.publisher, N',', N']]></c><c><![CDATA[')
																+ N']]></c>')
				)A
					CROSS APPLY A.publisher.nodes('/c') T(c)
			)AA
		)PUB
	WHERE PUB.publisher > N''
),
SRV AS(
	SELECT group_name = N'ServerGroup', subscriber_server = N'SERVER1' UNION ALL
	SELECT group_name = N'ServerGroup', subscriber_server = N'SERVER2' UNION ALL
	SELECT group_name = N'ServerGroup', subscriber_server = N'SERVER3'
),
REQLST AS(
	SELECT DISTINCT
		publication_name,

		publisher_server = PARSENAME(publisher, 4),
		publisher_database = UPPER(PARSENAME(publisher, 3)),
		publisher_schema = ISNULL(PARSENAME(publisher, 2), N'dbo'),
		publisher_object = PARSENAME(publisher, 1),

		subscriber_server = UPPER(CASE 
									WHEN ISNULL(SRV.subscriber_server, REQ.subscriber_server) > N'' THEN PARSENAME(ISNULL(CONVERT(sysname, SRV.subscriber_server), REQ.subscriber_server), 1)
									ELSE PARSENAME(publisher, 4)
								END), 
		subscriber_database = UPPER(CASE
									WHEN subscriber_database > N'' THEN PARSENAME(subscriber_database, 1)
									ELSE PARSENAME(publisher, 3)
								END), 
		subscriber_schema = ISNULL(CASE
									WHEN subscriber_schema > N'' THEN PARSENAME(subscriber_schema, 1)
									ELSE PARSENAME(publisher, 2)
								END,  N'dbo'),
		subscriber_object = CASE
								WHEN subscriber_object > N'' THEN PARSENAME(subscriber_object, 1)
								ELSE PARSENAME(publisher, 1)
							END,

		row_filter,
		column_filter
	FROM REQ
		LEFT JOIN SRV
			ON REQ.subscriber_server = SRV.group_name
),
REQBCP AS(
	SELECT DISTINCT
		rowid = DENSE_RANK() OVER(PARTITION BY publisher_server, publisher_database, publisher_schema, publisher_object
									ORDER BY subscriber_server, subscriber_database, subscriber_schema, subscriber_object),
		publisher_server, publisher_database, publisher_schema, publisher_object,
		subscriber_server, subscriber_database, subscriber_schema, subscriber_object,
		row_filter, column_filter,
		out_file = REPLACE(publisher_server + N'.' + publisher_database + N'.' + publisher_schema + N'.' + publisher_object, N'\', N'_'),
		filter_flag = CASE
						WHEN row_filter > N'' OR column_filter > N'' THEN 1
						ELSE 0
					END
	FROM REQLST
)
SELECT
	bcp_out = CASE
				WHEN rowid > 1 THEN N''
				ELSE N'BCP '
					+ CASE
						WHEN filter_flag = 0
							THEN publisher_database + N'.' + publisher_schema + N'.' + publisher_object
								+ N' Out'					
						ELSE N' "SELECT '
								+ CASE WHEN column_filter > N'' THEN column_filter ELSE N'*' END
								+ N' FROM ' + QUOTENAME(publisher_database) + N'.' + QUOTENAME(publisher_schema) + N'.' + QUOTENAME(publisher_object)
								+ N' WITH(NOLOCK)'
								+ CASE WHEN row_filter > N'' THEN N' WHERE(' + row_filter + N')' ELSE N'' END
								+ N'" QueryOut'
					END
					+ N' "' + out_file + N'"'
					+ N' /N /U User /P pwd'
					+ N' /S ' + publisher_server
			END,
		bcp_in = N'BCP '
				+ subscriber_database + N'.' + subscriber_schema + N'.' + subscriber_object
				+ N' In ' + N'"' + out_file + N'"'
				+ N' /N /E /q /k /U User /P pwd /b 50000'
				+ N' /S ' + subscriber_server,
	*,
	publish_path = publisher_server + N'.' + publisher_database + N'.' + publisher_schema + N'.' + publisher_object
					+ N'->'
					+ subscriber_server + N'.' + subscriber_database + N'.' + subscriber_schema + N'.' + subscriber_object
FROM REQBCP
ORDER BY publisher_server, publisher_database, publisher_schema, publisher_object, rowid