按单号导出寄售结算单特定表数据

    星空有些客户账套过大或涉及机密，不适合传账套，如果从SQL Server 导出数据，又只能针对特定表，本文介绍按条件（比如单据编号）导出相关数据。

--创建存储过程[sp_CreateInsertScript]

--=============================================

-- Author: Mark Kang

-- Company: www.ginkia.com

-- Create date: 2016-03-06

-- Description: Generat the insert sql script according to the data in the specified table.

--              It does not support the columns with timestamp,text,image.

-- Demo : exec sp_CreateInsertScript '[dbo].[Country]','[continent_name]=''North America'''

-- Change History:

--         1.2016-03-06 Created and published

--         2.2016-03-08 Based on Mike's suggestions, I optimized the codes

--         3.2019-03-09 1)Add code lines to avoid error when @con is empty string

--                      2)Based on Lukas Macedo's suggetstions, add surrounding brackets for column name

--                      3)Simplify WHEN...CASE

-- =============================================

CREATE PROC [dbo].[sp_CreateInsertScript] (

  @tablename NVARCHAR(256) -- table name

  ,@con NVARCHAR(400) -- condition to filter data

  ,@ignoreIdentityCol bit=0 --indicate if ignore columne with identity

  ,@isDebug bit=0 --indicate if this is used to debug. when 1,output the internal sql string

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @sqlstr NVARCHAR(MAX);

DECLARE @valueStr1 NVARCHAR(MAX);

DECLARE @colsStr NVARCHAR(MAX);

SELECT @sqlstr='SELECT ''INSERT '+@tablename;

SELECT @valueStr1='';

SELECT @colsStr='(';

SELECT @valueStr1='VALUES (''+';

IF RTRIM(LTRIM(@con))=''

SET @con='1=1';

SELECT @valueStr1=@valueStr1+col+'+'',''+' 

,@colsStr=@colsStr+'['+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 syscolumns AS sc 

      WHERE sc.id = object_id(@tablename) 

      AND sc.xtype <>189 --xtype=189 'timestamp' 

      AND sc.xtype <>34 --xtype=34 'image' 

      AND sc.xtype <>35 --xtype= 35 'text'

      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);

SET NOCOUNT OFF

END

GO

--执行存储过程(寄售结算单主表)，复制结果出来

EXEC sp_CreateInsertScript 'T_SAL_CONSIGSETTLE','FBILLNO = ''XSJSD003636'''

--将查询到的FID 替换后面的 1111，2222（寄售结算单结算明细和选单明细数据）

SELECT FID

FROM T_SAL_CONSIGSETTLE

WHERE FBILLNO = 'XSJSD003636'

EXEC sp_CreateInsertScript 'T_SAL_CONSIGSETTLEENTRY','FID = 1111'

EXEC sp_CreateInsertScript 'T_SAL_CONSIGSETTENTRYROW','FID = 2222'

--将查询到的FENTRYID 替换后面括号里面的333，444（寄售结算单匹配发货数据）

SELECT STUFF(

(

SELECT ',' + CONVERT(VARCHAR(6), OE.FENTRYID)  

FROM T_SAL_CONSIGSETTLE O

INNER JOIN T_SAL_CONSIGSETTLEENTRY OE ON OE.FID = O.FID

WHERE FBILLNO = 'XSJSD003636'

FOR XML PATH('')

), 1, 1, '') AS FENTRYID

EXEC sp_CreateInsertScript 'T_SAL_CONSIGSETTENTRYDEL','FENTRYID IN (333, 444)'

上文存储过程'sp_CreateInsertScript '的定义转载至博客园链接： Sql server中用现有表中的数据创建Sql的Insert插入语句 - 码客风云 - 博客园 (cnblogs.com)