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

Source: Internet
Author: User
Tags first row

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.