Description: COPY DATA FROM TABLE TO TABLE
Link:
http://www.codekeep.net/snippets/69774736-33e8-4023-ab73-2f80e664158f.aspxUSE [master]
GO
/****** Object: StoredProcedure [dbo].[COPY_TABLE] Script Date: 10/17/2011 11:44:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***
OBJET : STORED PROCEDURE
AUTHOR : ELAMEEN ELSAYED
DESC : MOVE TABLE'S ENTIRE DATA TO ANOTHER TABLE
VERSION 2011/10/08 : @PRINT VARIABLE TO PRINT QUERY,
@BASE VARIABLE TO SPECIFY FROM WHICH TABLE THE LIST OF COLUMNS ARE GOING TO BE ACQUIRED
VERSION 2011/10/16 : CHECK IF THE TABLE HAS IDENTITY FIRST,
@APPEND VARIABLE TO APPEND ROWS
***/
ALTER PROC [dbo].[COPY_TABLE] (
@SRC_DB NVARCHAR(100),
@SRC_SCHMA NVARCHAR(100),
@SRC_TBL NVARCHAR(100),
@DSTN_DB NVARCHAR(100),
@DSTN_SCHMA NVARCHAR(100),
@DSTN_TBL NVARCHAR(100),
@PRINT BIT=1,
@BASE BIT=0,
@APPEND BIT=0
)
AS
BEGIN
DECLARE @LQS NVARCHAR(MAX), @LQS2 NVARCHAR(MAX),@COLS_TABLE NVARCHAR(MAX),@DB_COLS_TABLE NVARCHAR(MAX)
IF @BASE=1
SELECT @DB_COLS_TABLE=@DSTN_DB,@COLS_TABLE=@DSTN_TBL
ELSE
SELECT @DB_COLS_TABLE=@SRC_DB,@COLS_TABLE=@SRC_TBL
SELECT @LQS='
DECLARE @COLS_LST NVARCHAR(MAX),@CMD NVARCHAR(MAX)
SELECT @COLS_LST=STUFF((SELECT '',''+COLUMN_NAME
FROM '+@DB_COLS_TABLE+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='''+@COLS_TABLE+'''
FOR XML PATH('''')),1,1,'''');'+
CASE WHEN @APPEND=0 THEN +' DELETE FROM '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+';' ELSE '' END +
' SELECT @CMD=''SET IDENTITY_INSERT '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+' ON;INSERT INTO '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+' (''+@COLS_LST+'')
SELECT ''+@COLS_LST+'' FROM '+@SRC_DB+'.'+@SRC_SCHMA+'.'+@SRC_TBL+';SET IDENTITY_INSERT '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+' OFF;'';
EXEC SP_EXECUTESQL @CMD;
'
--CHECK IF DOESN'T HAVE IDENTITY
DECLARE @IDENT_FLG BIT;
SELECT @LQS2 ='
USE '+@DSTN_DB+';
SELECT @IDENT_FLG=COALESCE(OBJECTPROPERTY(OBJECT_ID('''+@DSTN_TBL+''') , ''TableHasIdentity''),0);
'
EXEC SP_EXECUTESQL @LQS2,N'@IDENT_FLG BIT OUTPUT',@IDENT_FLG OUTPUT
IF @IDENT_FLG=0
BEGIN
--WITHOUT IDENTITY
SELECT @LQS='
DECLARE @COLS_LST NVARCHAR(MAX),@CMD NVARCHAR(MAX)
SELECT @COLS_LST=STUFF((SELECT '',''+COLUMN_NAME
FROM '+@DB_COLS_TABLE+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='''+@COLS_TABLE+'''
FOR XML PATH('''')),1,1,'''');'+
CASE WHEN @APPEND=0 THEN +' DELETE FROM '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+';' ELSE '' END +
'SELECT @CMD=''INSERT INTO '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+' (''+@COLS_LST+'')
SELECT ''+@COLS_LST+'' FROM '+@SRC_DB+'.'+@SRC_SCHMA+'.'+@SRC_TBL+';'';
EXEC SP_EXECUTESQL @CMD;
'
END
IF @PRINT =1
PRINT @LQS
ELSE
EXEC SP_EXECUTESQL @LQS
END