Generando insert data en stored procedure sql

Posted on 9 of marzo, 2016 by admin in Sql server

Alguna vez necesitamos  exportar data desde una base de datos a otra.  Hay muchas formas de hacer este proceso, en este caso veremos la forma de hacerlo mediante un stored procedure esta idea es original de markkang. Este artículo fue tomado de codeproject.

Estaba revisando los artículos de codeproject y este llamo mi atención.

Veamos como con un storeprocedure podemos hacer este proceso:

–=============================================

–Create basic stored procedure template

–=============================================

 

–Drop stored procedure if it already exists

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N’dbo’

AND SPECIFIC_NAME = N’SP_CREATEINSERTSCRIPT’

)

DROP PROCEDURE [dbo].[SP_CREATEINSERTSCRIPT];

GO

 

CREATE PROCEDURE [dbo].[SP_CREATEINSERTSCRIPT]

@tablename NVARCHAR(256),

@con NVARCHAR(400),

@ignoreidentityCol bit =0,

@isDebug bit = 0

AS

 

–DECLARE @tablename NVARCHAR(256);

–DECLARE @con NVARCHAR(400);

–DECLARE @ignoreidentityCol bit =0;

–DECLARE @isDebug bit = 1;

 

–SET @tablename = ‘[Policy].[PL_PLAN]';

 

DECLARE @sqlstr NVARCHAR(MAX);

DECLARE @valueStr1 NVARCHAR(MAX);

DECLARE @colsStr NVARCHAR(MAX);

 

SELECT @sqlstr=’SELECT ”INSERT INTO ‘ + @tablename;

SELECT @valueStr1=”;

SELECT @colsStr='(‘;

SELECT @valueStr1=’VALUES (”+';

 

IF RTRIM(LTRIM(@con)) = ”

SET @con=’1=1′;

 

SELECT @valueStr1 = @valueStr1 + col + ‘+”,”+’

–,@colsStr = @colsStr +'[‘+name+’],’

,@colsStr = @colsStr + QUOTENAME(name,”) +’,’

FROM (

SELECT

CASE

/* xtype=173 ‘binary’

xtype=165 ‘varbinary’*/

WHEN sc.xtype in (173,165) THEN ‘CASE WHEN [‘+sc.name+’] is null THEN ”NULL” ELSE ‘+’CONVERT(NVARCHAR(‘+CONVERT(NVARCHAR(4),sc.[length]*2+2)+’),[‘+sc.name +’])’+’ END’

/*xtype=104 ‘bit’*/

WHEN sc.xtype =104 THEN ‘CASE WHEN [‘+sc.name+’] is null THEN ”NULL” ELSE ‘+’CONVERT(NVARCHAR(1),[‘+sc.name +’])’+’ END’

/*xtype=61 ‘datetime’

xtype=58 ‘smalldatetime’*/

WHEN sc.xtype in(58,61) THEN ‘CASE WHEN [‘+sc.name+’] is null THEN ”NULL” ELSE ‘+”’N”””+’+’CONVERT(NVARCHAR(23),’+sc.name +’,121)’+ ‘+””””’+’ END’

/*xtype=175 ‘char’

xtype=36 ‘uniqueidentifier’

xtype=167 ‘varchar’

xtype=231 ‘nvarchar’

xtype=239 ‘nchar’*/

WHEN sc.xtype in (36,175,167,231,239) THEN ‘CASE WHEN [‘+sc.name+’] is null THEN ”NULL” ELSE ‘+”’N”””+’+’REPLACE([‘+sc.name+’],””””,””””””)’ + ‘+””””’+’ END’

/*xtype=106 ‘decimal’

xtype=108 ‘numeric’*/

WHEN sc.xtype in(106,108) THEN ‘CASE WHEN [‘+sc.name+’] is null THEN ”NULL” ELSE ‘+’CONVERT(NVARCHAR(‘+CONVERT(NVARCHAR(4),sc.xprec+2)+’),[‘+sc.name +’])’+’ END’

/*xtype=59 ‘real’

xtype=62 ‘float’*/

WHEN sc.xtype in (59,62) THEN ‘CASE WHEN [‘+sc.name+’] is null THEN ”NULL” ELSE ‘+’CONVERT(NVARCHAR(23),’+sc.name +’,2)’+’ END’

/*xtype=48 ‘tinyint’

xtype=52 ‘smallint’

xtype=56 ‘int’

xtype=127 ‘bigint’

xtype=122 ‘smallmoney’

xtype=60 ‘money’*/

WHEN sc.xtype in (48,52,56,127,122,60) THEN ‘CASE WHEN [‘+sc.name+’] is null THEN ”NULL” ELSE ‘+’CONVERT(NVARCHAR(23),[‘+sc.name +’])’+’ END’

ELSE ”’NULL”’

END    AS col,

sc.colid,

sc.name

FROM sys.syscolumns as sc

WHERE sc.id = OBJECT_ID(@tablename)

AND sc.xtype <> 189

AND sc.xtype <> 34

AND sc.xtype <> 35

AND (COLUMNPROPERTY(sc.id,sc.name,’IsIdentity’) = 0 OR @ignoreidentityCol = 0)

)as t

ORDER BY colid;

 

SET @colsStr= LEFT(@colsStr,LEN(@colsStr)-1) + ‘) ‘;

SET @valueStr1= LEFT(@valueStr1,LEN(@valueStr1)-3) + ‘);”';

 

SELECT  @sqlstr = @sqlstr + @colsStr + @valueStr1 +’ AS sql FROM ‘ + @tablename + ‘ WHERE 1=1 AND ‘ +

ISNULL(@con, ‘1=1′);

 

IF @isDebug = 1

BEGIN

PRINT ‘1.columns string: ‘+ @colsStr;

PRINT ‘2.values string: ‘ + @valueStr1

PRINT ‘3.’+ @sqlstr;

END

 

EXEC( @sqlstr );

 

GO

 

—- =============================================

—- Example to execute the stored procedure

—- =============================================

EXECUTE [dbo].[SP_CREATEINSERTSCRIPT]

@tablename ='[Policy].[PL_PLAN]’,

@con =”,

@ignoreidentityCol =0,

@isDebug = 1;

 

 

El resultado sería el siguiente:

articulo19