ストアドプロシージャでテーブルまるコピーする。
注：（自動計算列除き）
ストアドプロシージャ
--ストアドプロシージャを定義する
CREATE PROCEDURE [TABLE_ALL_COPY]
@rtn_message NVARCHAR(MAX) OUTPUT, -- 共通パラメータ：エラーメッセージ
@rtn_error_line INTEGER OUTPUT, -- エラー行
@rtn_error_number INTEGER OUTPUT, -- エラー番号を保持する変数
@src_db_link varchar(200), -- DBリンク 例：[XX.XXX.XXX.XX].[XXX].[dbo].
@src_table_name varchar(200), -- Src テーブル名
@dest_db_link varchar(200), -- DBリンク 例：[XX.XXX.XXX.XX].[XXX].[dbo].
@dest_table_name varchar(200) -- Dest テーブル名
AS
BEGIN
/*********************************************************************
変数定義
**********************************************************************/
DECLARE
@cnt INTEGER, -- 処理件数を格納する変数
@executeSql NVARCHAR(MAX), -- 実行用SQL
@srcdblink NVARCHAR(200), -- Src DB リンク 例：[XX.XXX.XXX.XX].[XXX].[dbo].
@srctablename NVARCHAR(200), -- Src テーブル名
@CntSrc INT, -- Src カウント
@desttablename NVARCHAR(200), -- Dest テーブル名
@destdblink NVARCHAR(200), -- Dest DB リンク 例：[XX.XXX.XXX.XX].[XXX].[dbo].
@CntDest INT, -- Dest カウント
@HasIdentity BIT, -- Identityカラム
@ColumnStr NVARCHAR(MAX), -- テーブルカラム文字
@SqlCount NVARCHAR(MAX), -- テンプレートSQL ：アカウント
@SqlHasTable NVARCHAR(MAX), -- テンプレートSQL ：テーブル有無確認
@SqlTableColumn NVARCHAR(MAX), -- テンプレートSQL ：テーブルカラム
@MarkDbLink NVARCHAR(MAX), -- 置換文字列：ＤＢリンク
@MarkCountBody NVARCHAR(MAX), -- 置換文字列：件数取得する
@MarkSysTabley NVARCHAR(MAX), -- 置換文字列：システムテーブルの場合
--FETCH用
@TABLE_NAME NVARCHAR(MAX), @COLUMN_NAME NVARCHAR(MAX) , @IS_IDENTIT BIT
/*********************************************************************
パラメータ事前処理
**********************************************************************/
SET @rtn_message = N'正常終了'
SET @rtn_error_line = 0
SET @rtn_error_number = 0
SET @ColumnStr = N''
SET @HasIdentity = 0
SET @CntSrc = 0
SET @CntDest = 0
SET @cnt = 0
SET @srcdblink = LTRIM(RTRIM(ISNULL(@src_db_link, '')))
SET @srctablename = LTRIM(RTRIM(ISNULL(@src_table_name, '')))
SET @destdblink = LTRIM(RTRIM(ISNULL(@dest_db_link, '')))
SET @desttablename = LTRIM(RTRIM(ISNULL(@dest_table_name, '')))
SET @MarkDbLink = '/*DBLINK*/'
SET @MarkCountBody = '/*SQLCOUNTBODY*/'
SET @MarkSysTabley = '.[dbo]'
/*********************************************************************
SQL設定
**********************************************************************/
-- 件数カウント
SET @SqlCount = '
SELECT
@SubRowCount = COUNT(*)
FROM (
/*SQLCOUNTBODY*/
)a
'
-- テーブル有無確認
SET @SqlHasTable = '
SELECT
@SubRowCount = COUNT(*)
FROM ' + @MarkDbLink + 'SYS.TABLES
WHERE NAME = @TABLENAME
'
-- テーブルカラム取得
SET @SqlTableColumn = '
SELECT
T.NAME AS TABLE_NAME
, C.NAME AS COLUMN_NAME
, C.IS_IDENTITY
FROM
' + @MarkDbLink + 'SYS.TABLES T
LEFT JOIN ' + @MarkDbLink + 'SYS.COLUMNS C
ON T.OBJECT_ID = C.OBJECT_ID
WHERE
T.NAME = @TABLENAME
'
/*********************************************************************
必須確認（パラメータチェック）
**********************************************************************/
-- Src テーブル名
IF @srctablename = N'' BEGIN
SET @rtn_message = 'Src テーブル名がない'
GOTO END_STATE
END
-- Dest テーブル名
IF @desttablename = N'' BEGIN
SET @rtn_message = 'Dest テーブル名がない'
GOTO END_STATE
END
-- 同じテーブル確認
IF @srcdblink = @destdblink AND @srctablename = @desttablename BEGIN
SET @rtn_message = '同じデーブルへのコピーはできない'
GOTO END_STATE
END
-- Src テーブル有無確認
SET @executeSql = REPLACE(@SqlHasTable, @MarkDbLink, @srcdblink)
SET @executeSql = REPLACE(@executeSql, @MarkSysTabley, N'')
-- パラメータありパタン
EXECUTE sp_executesql
@executeSql
, N'@TABLENAME NVARCHAR(MAX), @SubRowCount int OUTPUT'
, @TABLENAME = @srctablename
, @SubRowCount = @cnt OUTPUT
IF @cnt = 0 BEGIN
SET @rtn_message = 'Src テーブルがない'
GOTO END_STATE
END
-- Dest テーブル有無確認
SET @executeSql = REPLACE(@SqlHasTable, @MarkDbLink, @destdblink)
SET @executeSql = REPLACE(@executeSql, @MarkSysTabley, N'')
-- パラメータありパタン
EXECUTE sp_executesql
@executeSql
, N'@TABLENAME NVARCHAR(MAX), @SubRowCount int OUTPUT'
, @TABLENAME = @desttablename
, @SubRowCount = @cnt OUTPUT
IF @cnt = 0 BEGIN
SET @rtn_message = 'Dest テーブルがない'
GOTO END_STATE
END
-- Src カラム数取得
SET @executeSql = REPLACE(@SqlTableColumn, @MarkDbLink, @srcdblink)
SET @executeSql = REPLACE(@SqlCount, @MarkCountBody, @executeSql)
SET @executeSql = REPLACE(@executeSql, @MarkSysTabley, N'')
-- パラメータありパタン
EXECUTE sp_executesql
@executeSql
, N'@TABLENAME NVARCHAR(MAX), @SubRowCount int OUTPUT'
, @TABLENAME = @srctablename
, @SubRowCount = @CntSrc OUTPUT
-- Dest カラム数取得
SET @executeSql = REPLACE(@SqlTableColumn, @MarkDbLink, @destdblink)
SET @executeSql = REPLACE(@SqlCount, @MarkCountBody, @executeSql)
SET @executeSql = REPLACE(@executeSql, @MarkSysTabley, N'')
-- パラメータありパタン
EXECUTE sp_executesql
@executeSql
, N'@TABLENAME NVARCHAR(MAX), @SubRowCount int OUTPUT'
, @TABLENAME = @desttablename
, @SubRowCount = @CntDest OUTPUT
IF @CntSrc <> @CntDest BEGIN
SET @rtn_message = 'カラム数が一致ない'
GOTO END_STATE
END
-- Identity 確認
SET @executeSql = REPLACE(@SqlTableColumn, @MarkDbLink, @destdblink)
SET @executeSql = @executeSql + ' AND C.IS_IDENTITY = 1'
SET @executeSql = REPLACE(@SqlCount, @MarkCountBody, @executeSql)
SET @executeSql = REPLACE(@executeSql, @MarkSysTabley, N'')
-- パラメータありパタン
EXECUTE sp_executesql
@executeSql
, N'@TABLENAME NVARCHAR(MAX), @SubRowCount int OUTPUT'
, @TABLENAME = @desttablename
, @SubRowCount = @cnt OUTPUT
IF @cnt > 0 BEGIN
SET @HasIdentity = 1
END
/*********************************************************************
処理内容
**********************************************************************/
BEGIN TRY
BEGIN TRANSACTION
-- テーブルにある自動計算カラムを除く
SET @executeSql = REPLACE(@SqlTableColumn, @MarkDbLink, @destdblink)
SET @executeSql = REPLACE(@executeSql, @MarkSysTabley, N'')
SET @executeSql = 'DECLARE SqlRowCursor CURSOR FOR '
+ @executeSql
+ 'AND C.IS_COMPUTED <> 1
ORDER BY C.COLUMN_ID
'
-- カーソル定義
-- パラメータありパタン
EXECUTE sp_executesql
@executeSql
, N'@TABLENAME NVARCHAR(MAX)'
, @TABLENAME = @desttablename
--カーソルオープ
OPEN SqlRowCursor
--最初の1行目を取得して変数へ値をセット
FETCH NEXT FROM SqlRowCursor INTO @TABLE_NAME,@COLUMN_NAME,@IS_IDENTIT
--データの行数分ループ処理を実行する
WHILE @@FETCH_STATUS = 0
BEGIN
-- ========= ループ内の実際の処理 ここから===
SET @ColumnStr = @ColumnStr + @COLUMN_NAME
-- ========= ループ内の実際の処理 ここまで===
--次の行のデータを取得して変数へ値をセット
FETCH NEXT FROM SqlRowCursor INTO @TABLE_NAME,@COLUMN_NAME,@IS_IDENTIT
-- 次カラムがある場合
IF @@FETCH_STATUS = 0 BEGIN
SET @ColumnStr = @ColumnStr + ','
END
END
--カーソルを閉じる
CLOSE SqlRowCursor;
DEALLOCATE SqlRowCursor;
-- Dest データをすべてクリア
SET @executeSql = 'DELETE ' + @destdblink + @dest_table_name
EXECUTE sp_executesql @executeSql
SET @cnt = @@ROWCOUNT
SET @rtn_message = @rtn_message + '、既存データ削除：' + CONVERT(NVARCHAR, @cnt) + '件'
-- 実行SQLクリア
SET @executeSql = N''
IF @HasIdentity = 1 BEGIN
SET @executeSql = 'SET IDENTITY_INSERT ' + @destdblink + @desttablename + ' ON;'
END
SET @executeSql = @executeSql
+ 'INSERT INTO '
+ @destdblink + @desttablename + '('
+ @ColumnStr + ')'
+ ' SELECT ' + @ColumnStr
+ ' FROM ' + @src_db_link + @src_table_name
+ ' WITH (NOLOCK);'
+ 'SET @SubCnt = @@ROWCOUNT;'
IF @HasIdentity = 1 BEGIN
SET @executeSql = @executeSql + 'SET IDENTITY_INSERT '+ @destdblink + @desttablename + ' OFF;'
END
EXECUTE sp_executesql
@executeSql
, N'@SubCnt int OUTPUT'
, @SubCnt = @cnt OUTPUT
SET @rtn_message = @rtn_message + '、新規データ追加：' + CONVERT(NVARCHAR, @cnt) + '件'
COMMIT TRANSACTION
RETURN 0
END TRY
/*********************************************************************
エラー処理
**********************************************************************/
BEGIN CATCH
ROLLBACK TRANSACTION
SET @rtn_message = N'';
--エラー情報取得
SELECT @rtn_error_number = ERROR_NUMBER(),
@rtn_error_line = ERROR_LINE(),
@rtn_message = ERROR_MESSAGE()
GOTO END_STATE_CATCH
END CATCH
-- 終了処理 ---------------------------------------------------------------------------------------
END_STATE:
SET @rtn_error_line = 1
END_STATE_CATCH:
--- 何らかの処理
RETURN @rtn_error_line
END
テスト用
USE [XXX]
GO
DECLARE @return_value Int,
@error_message varchar(max),
@error_line int,
@error_number int
SELECT @error_message = NULL
SELECT @error_line = 0
SELECT @error_number = 0
EXEC @return_value = TABLE_ALL_COPY
@rtn_message = @error_message OUTPUT,
@rtn_error_line = @error_line OUTPUT,
@rtn_error_number = @error_number OUTPUT,
@src_db_link = N'[XX.XXX.XX.XX].[XXX].[dbo].',
@src_table_name = N'TABLENAME',
@dest_db_link = '',
@dest_table_name = N'TABLENAME'
SELECT @error_message as N'@error_message',
@error_line as N'@error_line',
@error_number as N'@error_number'
SELECT @return_value as 'Return Value'
GO
