create proc spGenInsertSQL @ TableName as varchar (100) as - declare @TableName varchar (100) - set @TableName = 'orders' - set @TableName =' eeducation'DECLARE xCursor CURSOR FORSELECT name, xusertypeFROM syscolumnsWHERE (id = OBJECT_ID (@TableName)) declare @ F1 varchar (100) declare @ F2 integerdeclare @SQL varchar (8000) set @sql = 'SELECT' 'INSERT INTO' @TableName 'VALUES (' '' OPEN xCursorFETCH xCursor into @ F1, @ F2while @@ fetch_status = 0begin set @SQL = @ SQL Case When @ F2 in (35, 58, 99, 167, 175, 231, 239, 61) TEN ' CASE WHEN' @ F1 'Is Null Then' '' Else ' '' '' 'end ' Else ' ' end 'Replace (Isnull (Cast (' @ f1 'as varchar),' '' '' '' ',' ',' ' '' '' '' '' '' ' Case When @ F2 in (35, 58, 99, 167, 175, 231, 239, 61) TEN' CASE WHEN ' @ F1 ' Is Null Ten '' '' Else '' '' '' 'end ' else ' ' end char (13) '', '' Fetch next from xcursor @ f1, @ f2ndclose xcursordeallocate xcursorset @SQL = left (@ SQL, LEN (@SQL) - 5) ' ' ' ) '' From ' @ TableName - Print @sqlexec (@SQL)
- Second Edition: 2003.03.08GO
alter proc SPGenInsertSQL (@tablename varchar (256)) asbegin declare @sql varchar (8000) declare @sqlValues varchar (8000) set @sql = '(' set @sqlValues = 'values (' ' ' select @sqlValues = @sqlValues COLS ' ' ',' ' ', @ SQL = @SQL '[' Name ']' from (SELECT Case WHEN XTYPE IN (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127) THEN 'Case When' Name 'Is Null Then' 'Null' 'Else' 'Cast (' Name 'As Varchar)' 'End' When Xtype In (58, 61) TEN 'CASE WHEN' NAME 'Is Null Then' 'NULL' 'ELSE' '' '' '' '' ' ' CAST (' Name ' As Varchar) ' ' '' '' '' ' 'end' WHEN XTYPE IN (167) TEN 'CASE WHEN' NAME 'IS NULL THEN' '' '' 'Else' '' '' '' ' ' 'Replace (' Name ',' '' '' '' ',' '' '' '' '' '' '' '' '' 'End' When Xtype In (231) TEN 'CASE WHEN' Name 'Is Null Ten' 'Null' 'Else' '' '' N ' '' ''
' ' '', '' '' '', '' '' '' '' '' ' ' '' '' '' End 'When Xtype in (175) THEN 'CASE WHEN' NAME 'IS NULL THEN' '' ELSE ' ' '' '' '' 'Cast (Replace (' Name ',' '" '' '' ',' '' '' '' '') AS CHAR (' CAST (Length As Varchar) ') '' '' '' '' end 'WHEN Xtype In (239) THEN 'CASE WHEN' NAME 'IS NULL THEN' 'NULL' '' '' '' '' '' '' ' ' CAST (Replace (' Name ', '' " '' '' ',' '' '' '' '') AS Char (' Cast (Length As Varchar) ') '' '' '' '' End 'Else' '' Null '' End As Cols, Name from Syscolumns Where ID = Object_ID (@tablename)) T set @SQL = 'SELECT' 'INSERT INTO [' @tablename ']' Left (@ SQL, LEN @SQL) -1) ')' Left (@Sqlvalues, Len (@SqlValues) -4) ')' from ' @ TableName --Print @SQL EXEC (@SQL) Endgo - Third Edition : 2003.3.9
ALTER proc SPGenInsertSQL (@tablename varchar (256)) asbegin declare @sql varchar (8000) declare @sqlValues varchar (8000) set @sql = '(' set @sqlValues = 'values (' ' ' select @sqlValues = @sqlValues COLS ' ' ',' ' ', @ SQL = @SQL '[' Name ']' from (SELECT Case WHEN XTYPE IN (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127) THEN 'Case When' Name 'Is Null Then' 'Null' 'Else' 'Cast (' Name 'As Varchar)' 'End' When Xtype In (58, 61) TEN 'CASE WHEN' NAME 'Is Null Then' 'NULL' 'ELSE' '' '' '' '' ' ' CAST (' Name ' As Varchar) ' ' '' '' '' ' 'end' WHEN XTYPE IN (167, 175) THEN 'CASE WHEN' NAME 'IS NULL THEN' '' '' ELSE ' ' '' '' '' ' ' REPLACE (' Name ', ' '' '' '' '' '' '' '' '' '' '' '' End 'When Xtype In (231, 239) TEN' CASE WHEN ' Name 'Is Null Ten' 'NULL' ' Else ' ' '' n '' ''
'' ' ', '' '' '', '' '' '' '' '' ' ' '' '' '' ' ' END 'ELSE' '' 'NULL' 'End As Cols, Name from Syscolumns Where ID = Object_ID (@tablename) And Autoval Is Null) T set @sql =' SELECT '' INSERT INTO [' @tablename '] ' Left (@ SQL, LEN (@SQL) -1) ')' Left (@SqlValues, Len (@sqlvalues) -4) ')' from ' @ TableName Print @SQL EXEC (@SQL) / * SELECT * from syscolumns where id = Object_id ('test') And autoval is null * / end -
C # DataSet SqldataReader: use system; using system.xml; using system.data.sqlclient; using system.collections; // using microsoft.ApplicationBlocks.DATA
Public class class1 {[stathread] // should need static void main (String [] args) {system.console.writeline ("PLS Enter Server:"); string s = system.console.readline (); system.console .Writeline ("PLS Enter Database:"); string d = system.console.readline (); System.Console.writeline ("PLS Enter User:"); string u = system.console.readline (); system.console .Writeline ("PLS Enter Password:"); string p = system.console.readline (); System.Console.writeline ("PLS Enter SQL:"); string sql = system.console.readLine (); //
// DataReader // String SQL = "SELECT TOP 2 * from Products"; // SQL = "SELECT * from ORDERS / N SELECT * from [ORDER DETAILS]"; String Connectionstring = @ "Server =" S "; Database = " D "; User ID = " U "; Password = " P; // get the following references Microsoft SqlHelper SqlDataReader SqlDataReader x = SqlHelper.ExecuteReader (ConnectionString, System.Data.CommandType.Text, sql) System.console.writeline ("/ NUSE SQLDATAREADER:"); System.Windows.Forms.clipboard.SetDataObject (X, False), TRUE;
System.console.writeline ("/ NPLS Paste (Ctrl V)!"); System.Console.Readline ();
// DataSet System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection (ConnectionString); // get the following references Microsoft SqlHelper DataSet DataSet ds = SqlHelper.ExecuteDataset (sc, System.Data.CommandType.Text, sql ); // sqlparameter [] spa = sqlhelperparametercache.getspparameterSet (SC, "zsp_calendar"); //spa[0].value = system.datetime.parse ("1995-09-09"); // DataSet DS = SQLHELPER .ExecuteDataset (sc, CommandType.StoredProcedure, "zsp_calendar", spa); System.Console.WriteLine ( "/ nuse DataSet:"); System.Windows.Forms.Clipboard.SetDataObject (GenerateInsertInToSQLData (ds, true), true); System.console.writeline ("/ NPLS Paste (Ctrl V)!"); System.Console.Readline ();
} Public static string generateInsertintosqldata (DataSet DS, Bool IgnorebigColumn) {system.text.StringBuilder SB = new system.text.StringBuilder (); int J = 0; string bs = ""; // Used to record the last number of bits System.console.write ("/ N is being conducted") {bool b = true; if (sb.length> 0) sb.append ("/ n"); string S = ""; DATAROW DR IN DT.ROWS) {BOOL B; STRING R = ""; if (Sb.Length> 0) sb.append ("/ n"); Foreach (Datacolumn DC in Dt.columns) {B = true; if (r! = "" &&! R.Endswith (",")) R = ","; if (s! = "" &&! S.endswith (",") && b) S = ","; switch (dc.dattype.fullname) {case "system.boolean": R = DR [DC] == SYSTEM.DBNULL.VALUE? "NULL": ((BOOL) DR [DC] )? "1": "0"; Break; Case "System.Decimal": goto case "system.int32"; case "system.double": goto case "system.int32"; case "system.int16": goto Case "System.Int32"; Case "System.Int64": g OTO case "system.int32"; CASE "system.single": goto case "system.int32"; case "system.uint16": goto case "system.int32"; case "system.uint32": goto case "system. INT32 "; case" system.uint64 ": goto case" system.int32 "; case" system.int32 ": R = DR [DC] == system.dbnull.value?" Null ": DR [DC] .tostring ();
Break; case "system.char": goto case "system.string"; Case "system.datetime": goto case "system.string"; case "system.string": R = DR [DC] == System. DBNULL.VALUE? "NULL": "'" DR [DC] .tostring (). Replace ("'", "'') " '"; break; default: IF (ignorebigcolumn) {b = false; } Else {r = "null";} Break;} if (b && b) {s = "[" DC.COLUMNNAME "]";}} sb.append ("INSERT INTO [" DT. Tablename "] (" S ") VALUES (" R ")"); b = false; system.console.write (BS "/ B / B" J "times, " System.datetime.now); bs = new string ('/ b', digits (j) system.datetime.now.tostring (). Length 1); // 19 is the date time string length, 1 a ","}} return sb.ToString ();} public static string GenerateInsertInToSQLData (SqlDataReader sdr, bool IgnoreBigColumn) {System.Text.StringBuilder sb = new System.Text.StringBuilder (); int j = 0; int k = 0; String BS = ""; // Used to record last time The number of digits system.console.write ("/ N is being performed"); do {bool b = true; if (sb.length> 0) sb.append ("/ n"); string s = ""; while (SDR.READ ()) {BOOL B; String R = ""; if (sb.Length> 0) sb.append ("/ n"); for (int i = 0; i '",' '") "'); R = SDR.ISDBNULL (i)?" NULL ":" n '" SDR [i] .tostring (). Replace ("' "," '' ") " '"; Break; default: IF (ignorebigcolumn) {b = false;} else {r =" null ";} Break;} f (b && b) {s =" [" SDr.GetName (i) "]";}} Sb.Append ("INSERT INTO [TABLE" K "] (" S ") VALUES (" R ")"); b = false; System.console.write (BS "/ B / B" J "times," system.datetime.now); BS = new string ('/ b', DIGITS (J) System. DateTime.now.toString (). Length 1); // 19 is the date time string length, 1 is ","} k ;} while (sdr.nextresult ()); return sb.toString (); } static int digits (int N) // Number of places {n = system.math.abs (n = n / 10; int i = 1; while (n> 0) {n = N / 10 ; I ;} return i;}} // below is Microsoft SQLHELPER: / / =========================================================================================================================================================================================== ================================================= // Microsoft Data Access Application Block for .NET // http://msdn.microsoft .com / library / en-us / dnbda ///////// for more information See The Data Access Application Block Implementation Overview. / / ======================================= ==================================================================================================================================================================================================================================================================================== 2.0 Added Support for FillDataSet, UpdatedataSet and "Param" Helper methods //// ================================== ==============================================
