SQL Server根据表或视图批量生成插入语句并BCP导出成文本

--声明变量 DECLARE @TableName VARCHAR(100),@RunStr VARCHAR(MAX),@RunRC VARCHAR(MAX),@FinalRun VARCHAR(MAX)  --在master库创建数据载体，也可以建立在其他数据库，但一定要是实体表，因为BCP导出不支持临时表 IF OBJECT_ID('master.dbo.tempinsertstr') IS NOT NULL DROP TABLE master.dbo.tempinsertstr CREATE TABLE master.dbo.tempinsertstr(rowid INT IDENTITY(0,1),rowtext VARCHAR(MAX)) INSERT master.dbo.tempinsertstr(rowtext) VALUES('reserved')  --表或视图名称定义: SELECT @TableName='Reseller'  --拼凑表或视图字段信息，针对字符或时间类型的加单引号''，为防止特殊字段名都加了[] SELECT @RunStr=ISNULL(@RunStr+'+'',''+','')+'ISNULL('+CASE WHEN T.name IN('nvarchar','varchar','char','text','datetime','date','datetime2') THEN  '''''''''+LTRIM('+CASE WHEN T.name='text' THEN 'CAST(['+c.name+'] as varchar(max)))+'''''''',''NULL'')' ELSE '['+c.name+'])+'''''''',''NULL'')' END  ELSE  'LTRIM(['+c.name+']),''NULL'')'  END  FROM dbo.syscolumns C INNER JOIN dbo.systypes T on C.xusertype=T.xusertype WHERE C.id = object_id(@TableName) AND (OBJECTPROPERTY(C.id, N'IsUserTable') = 1 OR OBJECTPROPERTY(C.id,N'IsView')=1) ORDER BY C.colorder  --生成批量values语句: SET @RunRC=LOWER(' insert master.dbo.tempinsertstr(rowtext) select '+'''(''+'+@RunStr+'+''),'''+' from '+@TableName)  --生成单条插入语句: --SET @RunStr=LOWER(' --select '+'''insert into '+@TableName+' values(''+'+@RunStr+'+'');'''+' from '+@TableName)  --EXEC master.dbo.PrintLongText @RunStr --EXEC master.dbo.PrintLongText @RunRC  EXEC(@RunRC)  --最后一段去除分号;信息 UPDATE master.dbo.tempinsertstr SET rowtext=STUFF(rowtext,LEN(rowtext),1,';') WHERE rowid=(SELECT MAX(rowid) FROM master.dbo.tempinsertstr)  --首行rowtext为reserved更新为insert into ... values语句: UPDATE master.dbo.tempinsertstr SET rowtext='insert into '+LOWER(@TableName)+' values' WHERE rowid=0  --执行BCP导出为txt文本命令 SELECT @FinalRun=' EXEC master..xp_cmdshell ''BCP "select rowtext from master.dbo.tempinsertstr" queryout E:\txt_test\'+@TableName+'.sql -c -t "," -T -C'' '  EXEC(@FinalRun)  DROP TABLE master.dbo.tempinsertstr;  --以上代码编译成函数，可以多加一个打印sql的参数，便于调试 --参数1：表名或视图名 参数2:BCP导出txt文本所要输出路径 参数3：默认0为直接执行，1只打印要执行的sql，不执行代码 --EXEC usp_TableBatchInsert 'tablename','E:\txt_test\',1  --附上超长文本打印存储过程： CREATE PROC [dbo].[PrintLongText](@SQL VARCHAR(MAX))  AS  DECLARE @PrintText VARCHAR(8000) = ''  WHILE LEN(@SQL) > 4000  BEGIN   --修正末尾是空格出现的bug  SELECT @PrintText = LEFT(@SQL, 4000) + '|'  SELECT @PrintText = LEFT(@PrintText, LEN(@PrintText) - CHARINDEX(CHAR(10), REVERSE(@PrintText)))   SELECT @SQL = RIGHT(@SQL, LEN(@SQL) - LEN(@PrintText))     SELECT @PrintText=LEFT(@PrintText,LEN(@PrintText)-2)  WHERE RIGHT(@PrintText,2) IN (CHAR(13)+CHAR(10), CHAR(10)+CHAR(13))    SELECT @PrintText=LEFT(@PrintText,LEN(@PrintText)-1)  WHERE RIGHT(@PrintText,1) IN (CHAR(13),CHAR(10))   --SELECT @PrintText=RIGHT(@PrintText,LEN(@PrintText)-1) WHERE LEFT(@PrintText,1) IN (CHAR(13),CHAR(10))     PRINT @PrintText      --PRINT '/*** len=' + cast(len(@PrintText) as varchar) + ' datalength=' + cast(datalength(@PrintText) as varchar) + '***/'  END  SELECT @PrintText=@SQL  SELECT @PrintText=LEFT(@PrintText,LEN(@PrintText)-2) WHERE RIGHT(@PrintText,2) IN (CHAR(13)+CHAR(10), CHAR(10)+CHAR(13))  SELECT @PrintText=LEFT(@PrintText,LEN(@PrintText)-1) WHERE RIGHT(@PrintText,1) IN (CHAR(13),CHAR(10))  SELECT @PrintText=RIGHT(@PrintText,LEN(@PrintText)-1) WHERE LEFT(@PrintText,1) IN (CHAR(13),CHAR(10))  PRINT @PrintText