星空有些客户账套过大或涉及机密,不适合传账套,如果从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)
XSJSD003636.zip(1.95KB)
本文转载自:https://www.cnblogs.com/markkang/
作者:MarkKang
原文链接:https://www.cnblogs.com/markkang/p/11828496.html
推荐阅读