Often mentioned that when processing data with the method of dynamically generating SQL statements, processing statements are long and unable to handle this problem:
/ * - Data Test Environment - * / if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[tb]') And ObjectProperty (ID, n'uSERTABLE ') = 1) Drop Table [TB] Go
Create Table TB (Unit Name VARCHAR (10), Date DateTime, Sales INT) INSERT INTO TB SELECT 'A unit', '2001-01-01', 100 Union All Select 'B Unit', '2001-01-02 ', 101 Union All Select' C Unit ',' 2001-01-03 ', 102 Union All Select' D Unit ',' 2001-01-04 ', 103 Union All Select' E Unit ',' 2001-01- 05 ', 104 Union All Select' F Union ',' 2001-01-06 ', 105 Union All SELECT' G unit ',' 2001-01-07 ', 106 Union All Select' H Units ',' 2001-01 -08 ', 107 Union All Select' I Unit ',' 2001-01-09 ', 108 Union All Select' J Unit ',' 2001-01-11 ', 109
/ * - Requirements Date A unit B unit C unit d Unit E unit F unit G unit H unit I unit J unit ------------------- - - ---- - ---- -------- ---- ---- ------ 2001-01-01 100 0 0 0 0 0 0 0 0 02001 -01-02 0 0 0 0 0 0 0 0 0 0 02001-01-03 0 0 102 0 0 0 0 0 0 02001-01-04 0 0 0 103 0 0 0 0 0 02001-01-05 0 0 0 0 104 0 0 0 0 0 0 0 105 0 0 0 0 0 0 0 0 106 0 0 0 0 0 0 0 0 107 0 0 0 0 0 0 0 0 0 0 0 0 0 2001 01 -09 0 0 0 0 0 0 0 0 0 0 02001-01-11 0 0 0 0 0 0 0 0 0 0 109 - * /
/ * - Regular processing method * / declare @SQL varchar (8000) set @ SQL = 'SELECT date = Convert (varchar (10), date, 120)' select @ SQL = @ SQL ', [' unit name '] = SUM (Case Unit Name When' '' Unit Name '' '' Sale ELSE 0 End) 'from (Select Distinct Unit Name from TB) Aexec (@ SQL ' From Tb Group by Convert (VARCHAR (10 ), Date, 120) ') / * - Problem: If there are a lot of units, the value of @ SQL will be truncated, thus error. * /
/ * - Three solutions are given below: - * /
- / * - Method 1. Multiple variable processing
- Define variables, estimate how many variables need to be saved all data declare @ sql0 varchar (8000), @ SQL1 varchar (8000) -, ... @ SQLN varchar (8000)
- Generate data processing temporary table select id = Identity (int, 0, 1), groupid = 0, value = ', [' unit name '] = SUM (Case unit name when' ' unit name " '' THEN Sales ELSE 0 End 'Into #Temp from (SELECT DISTINCT Unit Name from TB) A
- Group Temporary table, judgment, how many units can be combined into a string of no more than 8000, here to assume 5 Update #Temp Set Groupid = ID / 5 --5 for each group
- Generate SQL statement processing strings - initialization Select @ SQL0 = ', @ SQL1 =' '- ...--, @ SQLN
- Get the processing string select @ SQL0 = @ SQL0 value from #temp where groupid = 0 - the first variable select @ SQL1 = @ SQL1 value from #temp where groupid = 1 - second variable --select @ SQLN = @ SQLN Value from #temp where groupid = n - Nth variable
- Query Exec ('SELECT Date = Convert (Varchar (10), Date, 120)' @ SQL0 @ SQL1 - ... @ SQLN 'From Tb Group by Convert (VARCHAR (10), Date, 120 ) ')
- Delete Temporary Table Drop Table #TEMP
/ * Advantages: The more flexible, the amount of data is required to increase the variable. Do not change the other part of the disadvantage: to estimate the data processed, it is estimated that there is an error * / - * /
- / * - Method 2. BCP ISQL
- Because of BCP ISQL, you need this information declare @servername varchar (250), @ username varchar (250), @PWD varchar (250) SELECT @ ServerName = 'zj' - server name, @ username = '' - User Name, @ PWD = '' - Password Declare @tbname Varchar (50), @ SQL VARCHAR (8000)
- Create data processing temporary table set @tbname = '[## Temp _' convert (varchar (40), newid ()) '] set @ SQL =' Create Table ' @ tbname ' (value varchar (8000) )) INSERT INTO ' @ TBNAME ' VALUES ('' Create View ' Stuff (@ TBNAME, 2, 2,' ') ' AS SELECT Date = Convert (Varchar (10), Date, 120) ')' EXEC (@SQL)
Set @ SQL = 'INSERT INTO' @ TBNAME 'SELECT', ['' Unit Name ''] = SUM (Case Unit Name When '' '' Unit Name '' '' " Sales ELSE 0 End '' from (SELECT DISTINCT unit name from TB) a'exec (@SQL)
Set @ SQL = 'INSERT INTO' @ TBNAME 'VALUES (' 'from Tb Group by Convert (VARCHAR (10), Date, 120)' ')' EXEC (@SQL)
- Generate files that create views, pay attention to use files: c: /TEMP.TXTSET @ SQL = 'bcp "' @ TBNAME '" OUT "C: /TEMP.TXT" / S "' @ Servername '" / U "' @ Username '" / p "' @ PWD '" / c'exec master..xp_cmdshell @SQL
- Delete Temporary Table Set @ SQL = 'Drop Table' @ TBNameExec (@SQL)
- Call the ISQL generation data processing view set @ TBNAME = stuff (@ TBNAME, 2, 2, '') set @ SQL = 'isql / s "' @ servername case @username when '' Ten '" / e' Else '"/ u"' @ username '"/ p"' @ PWD '"' end '/ d"' db_name () '"/i"c:/Temp.txt "'Exec Master. .xp_cmdshell @SQL
- Call view, display processing result set @ SQL = 'SELECT * from' @ TBNAME 'DROP VIEW' @ TBNameExec (@SQL)
/ * Advantages: Procedures Automatic Processing, there is no problem of determining errors: complex, more steps, easy erroneous, and require a certain operator privilege * / - * /
- / * - Method 3. Multiple variable processing, the advantages of method 1 and method 2, solve the problem in method 1 requires human judgment, automatically definition according to the amount of data to be processed, while avoiding Method 2 cumbersome
Declare @sqlhead varchar (8000), @ SQLEND VARCHAR (8000), @ SQL1 VARCHAR (8000), @ SQL2 VARCHAR (8000), @ SQL3 VARCHAR (8000), @ SQL4 VARCHAR (8000), @ I Int, @ Ic varchar (20)
- Generate data processing temporary table select id = Identity (int, 0, 1), gid = 0, a = ', [' unit name '] = SUM (Case unit name when' ' unit name " '' THEN Sales ELSE 0 End 'Into # from (SELECT DISTINCT Unit Name from TB) A
- Judging how many variables need to be handled to process SELECT @ i = max (len (a)) from #print @I @ i = 7800 / @ i
- Group Temporary table update # set gid = id / @ iSELECT @ i = max (gid) from # #
- Generate data processing statement select @sqlhead = '' 'SELECT date = Convert (varchar (10), date, 120)' ', @ SQLEND =' 'from Tb Group by Convert (VARCHAR (10), date, 120) '' ', @ SQL1 =', @ SQL2 = 'SELECT', @ SQL3 = '', @ SQL4 = ''
While @i> = 0 SELECT @ IC = CAST (@i as varchar), @ i = @ i-1, @ SQL1 = '@' @ IC 'varchar (8000),' @ SQL1, @ SQL2 = @ SQL2 '@' @ IC '=' '', ', @ SQL3 =' SELECT @ ' @ IC ' = @ ' @ IC ' a from # where gid = ' @ IC char (13) @ SQL3, @ SQL4 = @ SQL4 ', @' @ icselect @ SQL1 = 'DECLARE' Left (@ SQL1, LEN (@ SQL1) -1) Char (13), @ SQL2 = Left (@ SQL2, LEN (@ SQL2) -1) char (13), @ SQL3 = Left (@ SQL3, LEN (@ SQL3) -1), @ SQL4 = Substring (@ SQL4, 2, 8000)
- Execute EXEC (@ SQL1 @ SQL2 @ SQL3 'EXEC (' @ SQLHEAD ' @ SQL4 ' ' @ SQLEND ') ')
- Delete Temporary Table Drop Table # - * /
Method 3, the key to modifying the following two sentences, others basically do not have to change:
- Generate data processing temporary table, modify A = back content as the corresponding processing statement select id = identity (int, 0, 1), gid = 0, a = ', [' code '] = SUM (Case B) .c_code when '' ' Code ' '' 'Ten B.Value else 0 end)' Into # from #class
- Generate data processing statement, assign @ sqlhead, @ SQLEND to the corresponding handling statement head and tail select @sqlhead = '' 'select a.id, a.name, a.code' ', @ SQLEND =' ' 'from #depart a, # value b Where a.code = b.d_code group by a.id, a.code, a.name' ', @ SQL1 =' ', @ SQL2 =' SELECT ', @ SQL3 = ', @ SQL4 =' '
