SQL Server Bulk-generates INSERT statements from tables or views and bcp exports to text

--DECLARE variable DECLARE @TableName varchar, @RunStr varchar (max), @RunRC varchar (max), @FinalRun varchar (max)-- Creating a data carrier in the master library can also be established in other databases, but it must be an entity table, because BCP export does not support temporary table 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 ')--table or view name definition: SELECT @TableName = ' Reseller '--piecing together table or view field information, Add single quote for character or time type ', to prevent special field names from adding [] 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 (@Tabl ename) and (ObjectProperty (c.id, N ' isusertable ') = 1 OR objectproperty (c.id,n ' ISVIew ') =1) ORDER by C.colorder-Generate batch values statement: SET @RunRC =lower (' Insert Master.dbo.tempinsertstr (rowtext) Select ' + ' ' [email protected]+ ' + '), ' + ' from ' [email protected] '--Generate a single INSERT statement:--set @RunStr =lower ('--select ' + ') Insert INTO ' [email protected]+ ' values (' + ' [email protected]+ ' + '); + ' from ' [email protected]]--exec master.dbo.PrintLongText @RunStr--exec master.dbo.PrintLongText @RunRC EXEC (@R UNRC)--the last paragraph to remove the semicolon; info UPDATE master.dbo.tempinsertstr SET rowtext=stuff (Rowtext,len (Rowtext), 1, '; ') WHERE rowid= (SELECT MAX (ROWID) from Master.dbo.tempinsertstr)-first row rowtext for reserved update to insert INTO ... values statement: Update ma Ster.dbo.tempinsertstr SET rowtext= ' insert INTO ' +lower (@TableName) + ' values ' WHERE rowid=0--Execute bcp export to txt text command SELECT @Fi Nalrun= ' EXEC master. xp_cmdshell ' BCP ' select Rowtext from Master.dbo.tempinsertstr ' queryout E:\txt_test\ ' [email protected]+ '. sql-c  -T ","-t-c "EXEC (@FinalRun) DROP TABLE master.dbo.tempinsertstr; --The above code is compiledTranslated into a function, you can add a print SQL parameters, easy to debug-parameter 1: Table name or view name parameter 2:BCP Export the txt text to the output Path parameter 3: The default 0 for direct execution, 1 only to print the SQL to execute, do not execute code--exec USP_ Tablebatchinsert ' tablename ', ' E:\txt_test\ ', 1--Attach an extra-long text print stored procedure: CREATE PROC [dbo]. [Printlongtext]  (@SQL VARCHAR (MAX)) As DECLARE @PrintText VARCHAR (8000) = ' while LEN (@SQL) > 4000 BEGIN--fix bug with white space at the end of SELECT @PrintText = left (@S  QL, 4000) + ' | ' Select @PrintText = Left (@PrintText, LEN (@PrintText)-CHARINDEX (CHAR (Ten), REVERSE (@PrintText))) Select @SQL = Right (@SQ L, Len (@SQL)-Len (@PrintText)) SELECT @PrintText =left (@PrintText, Len (@PrintText)-2) WHERE right (@PrintText, 2) in (CH AR (+char), CHAR (+char)) SELECT @PrintText =left (@PrintText, LEN (@PrintText)-1) WHERE Right (@PrintText, 1) In (char), char (Ten))--select @PrintText =right (@PrintText, LEN (@PrintText)-1) WHERE left (@PrintText, 1) in (char (13), CHAR (Ten)) PRINT @PrintText--print '/*** len= ' + cast (len (@PrintText) as varchar) + ' datalength= ' + cast (datalen Gth (@PrintText) as varchar) + ' ***/' END Select @[email protected] SELECT @PrintText =left (@PrintText, LEN (@PrintText)-2) WHERE right (@PrintText, 2) in ( char (+char), char (+char)) SELECT @PrintText =left (@PrintText, LEN (@PrintText)-1) WHERE Right (@PrintText, 1 ) in (char), char (Ten)) SELECT @PrintText =right (@PrintText, LEN (@PrintText)-1) WHERE left (@PrintText, 1) in (char (13),   CHAR (Ten)) PRINT @PrintText

　　

SQL Server Bulk-generates INSERT statements from tables or views and bcp exports to text