根据基本表结构及其数据生成 INSERT ... 的 SQL

原创 2003年03月09日 23:18:00

create proc spGenInsertSQL
@TableName as varchar(100)
as
--declare @TableName varchar(100)
--set @TableName = 'orders'
--set @TableName = 'eeducation'
DECLARE xCursor CURSOR FOR
SELECT name,xusertype
FROM syscolumns
WHERE (id = OBJECT_ID(@TableName))
declare @F1 varchar(100)
declare @F2 integer
declare @SQL varchar(8000)
set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''
OPEN xCursor
FETCH xCursor into @F1,@F2
WHILE @@FETCH_STATUS = 0
BEGIN
    set @sql =@sql +
              + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + '  else '+' end
              + 'replace(ISNULL(cast(' + @F1 + ' as varchar),''NULL''),'''''''','''''''''''')'
              + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + '  else '+' end
              + char(13) + ''','''
    FETCH NEXT FROM xCursor into @F1,@F2
END
CLOSE xCursor
DEALLOCATE xCursor
set @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName
--print @sql
exec (@sql)


--第二版:2003.03.08
go


alter proc SPGenInsertSQL (@tablename varchar(256))
as
begin
  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)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
           when xtype in (167)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                  when xtype in (231)
                       then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                  when xtype in (175)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                  when xtype in (239)
                       then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + '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)
end

go
--第三版: 2003.3.9

ALTER   proc SPGenInsertSQL (@tablename varchar(256))
as
begin
  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)
                       then '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 ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+''''''''' + ' end'
                  when xtype in (231,239)
                       then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+''''''''' + ' 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:
using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
//using Microsoft.ApplicationBlocks.Data

public class Class1
{
 [STAThread] //应 ClipBoard 需要
 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;

  //下面引用 Microsoft SqlHelper 得到 SqlDataReader
  SqlDataReader x = SqlHelper.ExecuteReader(ConnectionString,System.Data.CommandType.Text, sql);
  System.Console.WriteLine("/nuse SqlDataReader :");
  System.Windows.Forms.Clipboard.SetDataObject(GenerateInsertInToSQLData(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);
  //下面引用 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 = ""; //用于记录上次的位数
  System.Console.Write("/n正在进行第    ");
  foreach (DataTable dt in ds.Tables)
  {
   bool b = true;
   if (sb.Length > 0)
    sb.Append("/n");
   string s = "";
   foreach (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.DataType.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" :
       goto 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/b" + ++j + " 次," + System.DateTime.Now);
    bs = new string('/b',Digits(j) + System.DateTime.Now.ToString().Length + 1); //19 为日期时间字符串长度, 1 是 ","
    
   }
  }
  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 = ""; //用于记录上次的位数
  System.Console.Write("/n正在进行第    ");
  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 < sdr.FieldCount; i++)
    {
     B = true;
     if (r != "" && !r.EndsWith(","))
      r += ","; //数据行
     if (s != "" && !s.EndsWith(",") && b)
      s += ","; //字段列表
     switch (sdr.GetDataTypeName(i))
     {
      case "bit" :
       r += sdr.IsDBNull(i) ? "null" : ((bool) sdr[i]) ? "1" : "0";
       break;
      case "bigint" :
       goto case "int";
      case "smallint" :
       goto case "int";
      case "tinyint" :
       goto case "int";
      case "decimal" :
       goto case "int";
      case "numeric" :
       goto case "int";
      case "money" :
       goto case "int";
      case "smallmoney" :
       goto case "int";
      case "float" :
       goto case "int";
      case "real" :
       goto case "int";
      case "int" :
       r += sdr.IsDBNull(i) ? "null" : sdr[i].ToString();
       break;
      case "datetime" :
       goto case "varchar";
      case "smalldatetime" :
       goto case "varchar";
      case "char" :
       goto case "varchar";
      case "text" :
       goto case "varchar";
      case "varchar" :
       r += sdr.IsDBNull(i) ? "null" : "'" + sdr[i].ToString().Replace("'","''") + "'";
       break;
      case "nchar" :
       goto case "nvarchar";
      case "ntext" :
       goto case "nvarchar";
      case "nvarchar" :
       //sb.Append(sdr.IsDBNull(i) ? "null" : "N'" + sdr[i].ToString().Replace("'","''") + "'");;
       r += sdr.IsDBNull(i) ? "null" : "N'" + sdr[i].ToString().Replace("'","''") + "'";
       break;
      default :
       if (IgnoreBigColumn)
       {
        B = false;
       }
       else
       {
        r += "null";
       }
       break;
     }
     if (b && B)
     {
      s += "[" + sdr.GetName(i) + "]";
     }
    }
    sb.Append("insert into [Table" + k + "] (" + s + ") values (" + r + ")");
    b = false;
    System.Console.Write(bs + "/b/b/b" + ++j + " 次," + System.DateTime.Now);
    bs = new string('/b',Digits(j) + System.DateTime.Now.ToString().Length + 1); //19 为日期时间字符串长度, 1 是 ","
   }
   k ++;
  } while(sdr.NextResult());
  return sb.ToString();
 }

 static int Digits(int n) //数字所占位数
 {
  n = System.Math.Abs(n) ;
  n = n/10;
  int i = 1;
  while (n > 0)
  {
   n = n / 10;
   i++;
  }
  return i;
 }
}

// 下面是 Microsoft SqlHelper :

// ===============================================================================
// Microsoft Data Access Application Block for .NET
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//
// SQLHelper.cs
//
// This file contains the implementations of the SqlHelper and SqlHelperParameterCache
// classes.
//
// For more information see the Data Access Application Block Implementation Overview.
// ===============================================================================
// Release history
// VERSION DESCRIPTION
//   2.0 Added support for FillDataset, UpdateDataset and "Param" helper methods
//
// ===============================================================================
// Copyright (C) 2000-2001 Microsoft Corporation
// All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
// FITNESS FOR A PARTICULAR PURPOSE.
// ==============================================================================

MS SQLServer表数据生成Insert语句

数据库数据生成insert(MSSQL版),可将表中的数据生成insert或者update的sql脚本。比如您维护两个数据库,其中一个数据库中增加的数据也希望能够在另外一个数据中进行执行。...
  • swordmanli
  • swordmanli
  • 2016年09月17日 12:34
  • 285

sqlserver2000下生成表结构脚本和导入导出数据内容

1 表结构生成sql脚本 在指定的表【bjgl_code】(以下均以bjgl_code为例)上点击右键,选择【所有任务】-》【生成sql脚本】,在右下角的【将要写入脚本的对象】中选择bjgl_code...
  • yunxian_19
  • yunxian_19
  • 2016年12月02日 16:46
  • 2991

SQL Server 数据库基本操作——表的创建与修改

写这篇sql server 数据库操作主要是记录自己在学习过程中的一些心得体会以及备忘,由于是自学所以难免有很多地方可能是错误的,在此仅供初学者学习参考。创建表结构 CREATE TABLECREAT...
  • qq_36733722
  • qq_36733722
  • 2017年03月05日 22:37
  • 262

如何将sqlserver表中的数据导出sql语句或生成insert into语句

drop proc proc_insert go create proc proc_insert (@tablename varchar(256)) as begin set nocount...
  • duanmu19891209
  • duanmu19891209
  • 2015年01月08日 13:43
  • 6549

SQL语句修改数据库表结构

一、查看表结构     1、查看表的基本结构语句:       DESCRIBE `DEPT`;     2、查看表详细结构语句:       SHOW CREATE TABLE `DEPT`; 二、...
  • bai_juaner
  • bai_juaner
  • 2013年03月13日 19:54
  • 1654

pl/sql 导出insert语句和pl/sql导出表结构

1.先选择你需要导出的数据,然后选择如图所示按钮; 2.点击之后会出先下拉框,选择‘SQL file’; 3.点击之后会弹出一个对话框,让你选择文件保存位置,选择位置,给文件起名,点击保存即可。...
  • zcouy
  • zcouy
  • 2016年12月19日 15:24
  • 920

关于sqlserver的自动生成包含数据的insert脚本问题

关于sqlserver的自动生成包含数据的insert脚本问题 最近在一个项目上,需要数据的增量导入。项目上用的是sqlserver数据库。好久没有直接sql语句操作数据库了。感觉有点手生了 解决此...
  • lingxyd_0
  • lingxyd_0
  • 2016年05月28日 18:43
  • 4217

SQL SERVER 生成表结构的语句

通过表名生成表结构
  • t134679
  • t134679
  • 2015年09月01日 15:44
  • 802

SqlServer导出表结构生成脚本及表中数据脚本

在SqlServer中对表操作是最基本的,有时候为了为了避免操作失误带来的问题,先要备份一遍表结构及表中的内容,这样当真的发生误操作时可以很快的将数据恢复,所以说非常有用,自己知道的有点晚,记录下来希...
  • xiaouncle
  • xiaouncle
  • 2016年10月13日 15:01
  • 3122

sql server中表数据生成insert into插入语句

在导出表生成脚本时很想把表里面的数据也全部生成脚本,这种感觉不知道大家有木有,反正小编经常这样搞,废话补多少,分享两个方法实现。  1利用工具,现在已经是工具的时代,喝茶扯淡分分钟把活干完的快节奏中,...
  • list_123456
  • list_123456
  • 2017年08月31日 22:48
  • 168
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:根据基本表结构及其数据生成 INSERT ... 的 SQL
举报原因:
原因补充:

(最多只允许输入30个字)