SQL批量插入数据

2024-05-14

1. SQL批量插入数据

一、针对批量插入数据，如果量不是太多，可以多条SQL语句运行就可以了，
类似下面的语句，当然可以使用excel 编辑后，复制到查询器中运行，
insert into table(a,b) values('1','a')
insert into table(a,b) values('2','b')
insert into table(a,b) values('3','c')

二、大量数批量插入，即数据表的移植，数据备份转换之类的，就需要工具，比如MSSQL的DTS工具，pb的数据通道 等等。这里介绍一下 DTS工具。

1、在SQL安装目录下开启导入和导出数据，即DTS。



2、选择一个批量的数据，可以是表，也可以是带分隔符的文件，或excel文档之类，如图中选择，导入的格式


3、选择导入的目标


4、选择导入方式


5.具体的导入规则

SQL批量插入数据

2. SQLSERVER数据库中批量导入数据的几种方法

我们通过SQL脚本来插入数据，常见如下四种方式。
方式一：一条一条插入，性能最差，不建议使用。
INSERT INTO Product(Id,Name,Price) VALUES(newid(),'牛栏1段',160);INSERT INTO Product(Id,Name,Price) VALUES(newid(),'牛栏2段',260);......
方式二：insert bulk
语法如下：

BULK INSERT [ [ 'database_name'.][ 'owner' ].]{ 'table_name' FROM 'data_file' }WITH  ([ BATCHSIZE [ = batch_size ] ],[ CHECK_CONSTRAINTS ],[ CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ],[ DATAFILETYPE [ = 'char' | 'native'| 'widechar' | 'widenative' ] ],[ FIELDTERMINATOR [ = 'field_terminator' ] ],[ FIRSTROW [ = first_row ] ],[ FIRE_TRIGGERS ],[ FORMATFILE = 'format_file_path' ],[ KEEPIDENTITY ],[ KEEPNULLS ],[ KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ],[ LASTROW [ = last_row ] ],[ MAXERRORS [ = max_errors ] ],[ ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ],[ ROWS_PER_BATCH [ = rows_per_batch ] ],[ ROWTERMINATOR [ = 'row_terminator' ] ],[ TABLOCK ],)  

相关参数说明：

BULK INSERT[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]FROM 'data_file'[ WITH([ [ , ] BATCHSIZE = batch_size ]    --BATCHSIZE指令来设置在单个事务中可以插入到表中的记录的数量[ [ , ] CHECK_CONSTRAINTS ]     --指定在大容量导入操作期间，必须检查所有对目标表或视图的约束。若没有 CHECK_CONSTRAINTS 选项，则所有 CHECK 和 FOREIGN KEY 约束都将被忽略，并且在此操作之后表的约束将标记为不可信。[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]  --指定该数据文件中数据的代码页[ [ , ] DATAFILETYPE ={ 'char' | 'native'| 'widechar' | 'widenative' } ]  --指定 BULK INSERT 使用指定的数据文件类型值执行导入操作。[ [ , ] FIELDTERMINATOR = 'field_terminator' ]  --标识分隔内容的符号[ [ , ] FIRSTROW = first_row ]    --指定要加载的第一行的行号。默认值是指定数据文件中的第一行[ [ , ] FIRE_TRIGGERS ]     --是否启动触发器[ [ , ] FORMATFILE = 'format_file_path' ][ [ , ] KEEPIDENTITY ]   --指定导入数据文件中的标识值用于标识列[ [ , ] KEEPNULLS ]    --指定在大容量导入操作期间空列应保留一个空值，而不插入用于列的任何默认值[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ][ [ , ] LASTROW = last_row ]   --指定要加载的最后一行的行号[ [ , ] MAXERRORS = max_errors ]   --指定允许在数据中出现的最多语法错误数，超过该数量后将取消大容量导入操作。[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]  --指定数据文件中的数据如何排序[ [ , ] ROWS_PER_BATCH = rows_per_batch ][ [ , ] ROWTERMINATOR = 'row_terminator' ]   --标识分隔行的符号[ [ , ] TABLOCK ]     --指定为大容量导入操作持续时间获取一个表级锁[ [ , ] ERRORFILE = 'file_name' ]   --指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件。)]   

方式三：INSERT INTO xx select...

INSERT INTO Product(Id,Name,Price) SELECT NEWID(),'牛栏1段',160UNION ALLSELECT NEWID(),'牛栏2段',180 UNION ALL...... 

方式四：拼接SQL
INSERT INTO Product(Id,Name,Price) VALUES(newid(),'牛栏1段',160),(newid(),'牛栏2段',260)......

3. 如何在SQL Server中批量导入数据

方案一、循环导入

实现方式是利用数据库访问类调用存储过程，利用循环逐条插入。很明显，这种方式效率并不高



方案二、使用Bulk插入

bulk方法主要思想是通过在客户端把数据都缓存在Table中，然后利用SqlBulkCopy一次性把Table中的数据插入到数据库，效率非常高


方案三：

利用SQLServer2008的新特性--表值参数（Table-Valued Parameter）。表值参数是SQLServer2008才有的一个新特性，使用这个新特性，我们可以把一个表类型作为参数传递到函数或存储过程里。


方案四：

对于单列字段，可以把要插入的数据进行字符串拼接，最后再在存储过程中拆分成数组，然后逐条插入。查了一下存储过程中参数的字符串的最大长度，然后除以字段的长度，算出一个值，很明显是可以满足要求的，只是这种方式跟第一种方式比起来，似乎没什么提高，因为原理都是一样的。


方案五：

考虑异步创建、消息队列等等。这种方案无论从设计上还是开发上，难度都是有的。

如何在SQL Server中批量导入数据

4. 处理数据批量生成sql插入语句

处理数据批量生成sql插入语句
最近在做一个天气预报模块，首先需要将客户端公网ip转换成所在城市，然后将所在城市名转换成对应的城市代码，在网上找到了城市代码，但是需要处理一下，看了看，有三百多城市及对应的城市代码，想存到数据库。就想着做一个数据处理自动生成sql语句的工具，提高效率。
  1 直辖市
  2     "北京","上海","天津","重庆"
  3     "101010100","101020100","101030100","101040100"
  4 
  5 特别行政区
  6     "香港","澳门"
  7     "101320101","101330101"
  8 
  9 黑龙江
 10     "哈尔滨","齐齐哈尔","牡丹江","大庆","伊春","双鸭山","鹤岗","鸡西","佳木斯","七台河","黑河","绥化","大兴安岭"
 11     "101050101","101050201","101050301","101050901","101050801","101051301","101051201","101051101","101050401","101051002","101050601","101050501","101050701"
 12     
 13 吉林
 14     "长春","延吉","吉林","白山","白城","四平","松原","辽源","大安","通化"
 15     "101060101","101060301","101060201","101060901","101060601","101060401","101060801","101060701","101060603","101060501"
 16     
 17 辽宁
 18     "沈阳","大连","葫芦岛","盘锦","本溪","抚顺","铁岭","辽阳","营口","阜新","朝阳","锦州","丹东","鞍山"
 19     "101070101","101070201","101071401","101071301","101070501","101070401","101071101","101071001","101070801","101070901","101071201","101070701","101070601","101070301"
 20     
 21 内蒙古
 22     "呼和浩特","呼伦贝尔","锡林浩特","包头","赤峰","海拉尔","乌海","鄂尔多斯","通辽"
 23     "101080101","101081000","101080901","101080201","101080601","101081001","101080301","101080701","101080501"
 24 
 25 河北
 26     "石家庄","唐山","张家口","廊坊","邢台","邯郸","沧州","衡水","承德","保定","秦皇岛"
 27     "101090101","101090501","101090301","101090601","101090901","101091001","101090701","101090801","101090402","101090201","101091101"
 28     
 29 河南
 30     "郑州","开封","洛阳","平顶山","焦作","鹤壁","新乡","安阳","濮阳","许昌","漯河","三门峡","南阳","商丘","信阳","周口","驻马店"
 31     "101180101","101180801","101180901","101180501","101181101","101181201","101180301","101180201","101181301","101180401","101181501","101181701","101180701","101181001","101180601","101181401","101181601"
 32     
 33 山东
 34     "济南","青岛","淄博","威海","曲阜","临沂","烟台","枣庄","聊城","济宁","菏泽","泰安","日照","东营","德州","滨州","莱芜","潍坊"
 35     "101120101","101120201","101120301","101121301","101120710","101120901","101120501","101121401","101121701","101120701","101121001","101120801","101121501","101121201","101120401","101121101","101121601","101120601"
 36     
 37 山西
 38     "太原","阳泉","晋城","晋中","临汾","运城","长治","朔州","忻州","大同","吕梁"
 39     "101100101","101100301","101100601","101100401","101100701","101100801","101100501","101100901","101101001","101100201","101101101"
 40     
 41 江苏
 42     "南京","苏州","昆山","南通","太仓","吴县","徐州","宜兴","镇江","淮安","常熟","盐城","泰州","无锡","连云港","扬州","常州","宿迁"
 43     "101190101","101190401","101190404","101190501","101190408","101190406","101190801","101190203","101190301","101190901","101190402","101190701","101191201","101190201","101191001","101190601","101191101","101191301"
 44     
 45 安徽
 46     "合肥","巢湖","蚌埠","安庆","六安","滁州","马鞍山","阜阳","宣城","铜陵","淮北","芜湖","毫州","宿州","淮南","池州"
 47     "101220101","101221601","101220201","101220601","101221501","101221101","101220501","101220801","101221401","101221301","101221201","101220301","101220901","101220701","101220401","101221701"
 48     
 49 陕西
 50     "西安","韩城","安康","汉中","宝鸡","咸阳","榆林","渭南","商洛","铜川","延安"
 51     "101110101","101110510","101110701","101110801","101110901","101110200","101110401","101110501","101110601","101111001","101110300"
 52     
 53 宁夏
 54     "银川","固原","中卫","石嘴山","吴忠"
 55     "101170101","101170401","101170501","101170201","101170301"
 56     
 57 甘肃
 58     "兰州","白银","庆阳","酒泉","天水","武威","张掖","甘南","临夏","平凉","定西","金昌"
 59     "101160101","101161301","101160401","101160801","101160901","101160501","101160701","101050204","101161101","101160301","101160201","101160601"
 60     
 61 青海
 62     "西宁","海北","海西","黄南","果洛","玉树","海东","海南"
 63     "101150101","101150801","101150701","101150301","101150501","101150601","101150201","101150401"
 64     
 65 湖北
 66     "武汉","宜昌","黄冈","恩施","荆州","神农架","十堰","咸宁","襄阳","孝感","随州","黄石","荆门","鄂州"
 67 "101200101","101200901","101200501","101201001","101200801","101201201","101201101","101200701","101200201","101200401","101201301","101200601","101201401","101200301"
 68     
 69 湖南
 70     "长沙","邵阳","常德","郴州","吉首","株洲","娄底","湘潭","益阳","永州","岳阳","衡阳","怀化","韶山","张家界"
 71     "101250101","101250901","101250601","101250501","101251501","101250301","101250801","101250201","101250701","101251401","101251001","101250401","101251201","101250202","101251101"
 72     
 73 浙江
 74     "杭州","湖州","金华","宁波","丽水","绍兴","衢州","嘉兴","台州","舟山","温州"
 75     "101210101","101210201","101210901","101210401","101210801","101210501","101211001","101210301","101210601","101211101","101210701"
 76     
 77 江西
 78     "南昌","萍乡","九江","上饶","抚州","吉安","鹰潭","宜春","新余","景德镇","赣州"
 79     "101240101","101240901","101240201","101240301","101240401","101240601","101241101","101240501","101241001","101240801","101240701"
 80     
 81 福建
 82     "福州","厦门","龙岩","南平","宁德","莆田","泉州","三明","漳州"
 83     "101230101","101230201","101230701","101230901","101230301","101230401","101230501","101230801","101230601"
 84     
 85 贵州
 86     "贵阳","安顺","赤水","遵义","铜仁","六盘水","毕节","凯里","都匀"
 87     "101260101","101260301","101260208","101260201","101260601","101260801","101260701","101260501","101260401"
 88     
 89 四川
 90     "成都","泸州","内江","凉山","阿坝","巴中","广元","乐山","绵阳","德阳","攀枝花","雅安","宜宾","自贡","甘孜州","达州","资阳","广安","遂宁","眉山","南充"
 91     "101270101","101271001","101271201","101271601","101271901","101270901","101272101","101271401","101270401","101272001","101270201","101271701","101271101","101270301","101271801","101270601","101271301","101270801","101270701","101271501","101270501"
 92     
 93 广东
 94     "广州","深圳","潮州","韶关","湛江","惠州","清远","东莞","江门","茂名","肇庆","汕尾","河源","揭阳","梅州","中山","德庆","阳江","云浮","珠海","汕头","佛山"
 95     "101280101","101280601","101281501","101280201","101281001","101280301","101281301","101281601","101281101","101282001","101280901","101282101","101281201","101281901","101280401","101281701","101280905","101281801","101281401","101280701","101280501","101280800"
 96     
 97 广西
 98     "南宁","桂林","阳朔","柳州","梧州","玉林","桂平","贺州","钦州","贵港","防城港","百色","北海","河池","来宾","崇左"
 99     "101300101","101300501","101300510","101300301","101300601","101300901","101300802","101300701","101301101","101300801","101301401","101301001","101301301","101301201","101300401","101300201"
100     
101 云南
102     "昆明","保山","楚雄","德宏","红河","临沧","怒江","曲靖","思茅","文山","玉溪","昭通","丽江","大理"
103     "101290101","101290501","101290801","101291501","101290301","101291101","101291201","101290401","101290901","101290601","101290701","101291001","101291401","101290201"
104     
105 海南
106     "海口","三亚","儋州","琼山","通什","文昌"
107     "101310101","101310201","101310205","101310102","101310222","101310212"
108     
109 新疆
110     "乌鲁木齐","阿勒泰","阿克苏","昌吉","哈密","和田","喀什","克拉玛依","石河子","塔城","库尔勒","吐鲁番","伊宁"
111     "101130101","101131401","101130801","101130401","101131201","101131301","101130901","101130201","101130301","101131101","101130601","101130501","101131001"
112     
113 西藏
114     "拉萨","阿里","昌都","那曲","日喀则","山南","林芝"
115     "101140101","101140701","101140501","101140601","101140201","101140301","101140401"
116     
117 台湾
118     "台北","高雄"
119     "101340102","101340201"

城市代码

一看上去很乱的，而且对应关系是每个省城市一行，代码一行，分别用引号引起，用逗号分隔，每行间都没有符号分隔，省名没有用引号。首先是想着把省名去掉，因为每个城市名都是不相同的。想着每两行两行的去处理，但是也要费不少功夫，还容易出错。就想个索性一次性的全处理的算法。

   ps：界面很简单，上面是输入数据，中间是转换，下面是输出数据。

后台主要代码：
[csharp] view plain copy

    private void button1_Click(object sender, EventArgs e)  
    {  
        string data = textBox1.Text.Replace("r", "").Replace("n", "").Replace("t", "").Replace(" ", "").Replace(" ", "").Replace("    ", "");  
        MatchCollection matchsdata = matches(data, ""[sS]*?"");  
        string[,] temps = new string[matchsdata.Count / 2, 2];  
        int count0 = 0;  
        int count1 = 0;  
        string input = string.Empty;  
        foreach (Match m in matchsdata)  
        {  
            string tempdata = m.Value.Replace(""", "");  
            try  
            {  
                int tryp = int.Parse(tempdata);  
                temps[count1, 1] = tempdata;  
                count1++;  
            }  
            catch (Exception ex)  
            {  
                temps[count0, 0] = tempdata;  
                count0++;  
            }  
        }  
        for (int i = 0; i < (matchsdata.Count / 2); i++)  
        {  
            input += "insert into tbl_CityCode(c_city,c_code) values( + temps[i, 0] + , + temps[i, 1] + )rn";  
        }  
        textBox2.Text = input;  
    }  
      
    public static MatchCollection matches(string str, string exp)  
    {  
        return Regex.Matches(str, exp, RegexOptions.IgnoreCase);  
    }  

 首先是将输入的数据处理，去除换行符，空格什么的。然后你应该是会得到一行数据，然后通过正则表达式匹配出所有带引号的数据，你会发现需要的数据全部都是用引号引起来的，但是怎样区分城市名和城市代码呢，它们是混在一起的。不用担心，你发现了吗？城市名是字符串，城市代码是一串数字，我们只要将匹配出的数据数组遍历，每一行数据都去转换成int类型，这样城市名的行就会报错，在catch中捕捉，这一行就是城市名，没错的就是城市代码，把数据一次存到一个二维数组，对应的列中就行了。这样就会获得了相对应的城市名和城市代码。生成的sql语句要对应相应的数据库表。
表结构：
转换完了将生成的sql语句放到查询器中执行就ok了。共处理了349个城市。
最后不放心自己的算法，随机抽查了几条数据，没有错误。

5. 怎么样快速向SQL数据库插入大数据量的数据

添加数据需要知道往哪张表添加，以及自己要添加的内容，然后可用insert语句执行。
1、以sqlserver2008r2为例，登录SQL Server Management Studio到指定的数据库。
2、登录后点击“新建查询”。

3、比如要往test表中插入数据，可先用如下语句查看一下表结构及表内数据：

1
select * from test;


4、根据自己的实际情况添加输入，比如要添加一条“16,du小小动”的数据。

1
insert into test (id,name) values (16,'du小小动');

执行成功后会有提示：

5、此时数据库中数据如下，说明添加成功。

怎么样快速向SQL数据库插入大数据量的数据

6. 如何将Excel数据批量导入SQL数据库

把EXCEL数据导入到SQL数据库中：
1、在数据库上点击右键，然后选择“任务”，选择“导入数据”，就看到弹出淡入数据的对话框


2、Excel 上面的字段命名最好跟要导入到最终的那个表的字段相同。假设终表为A表。组装好 如下图：


3、按照操作步骤走下去，最终会生成一个新的表（临时表B表）。
可以借助工具，MSSQL表数据导出成Insert语句的工具 即：将查询出来的这些数据都生成insert into语句。
最终在A表中执行该insert into语句就可以将excel中的数据最终放入数据库中。

7. 如何将sql server数据库的数据批量导出

　　方法/步骤
打开数据库SQL server ,右击数据库选择“任务” “生成脚本”

选择将要导出的数据库

“将编写数据脚本”改为TRUE

选择表

选择下图中表里面要导出表的数据

在这一步要选择将脚本保存到“新建查询”窗口

点击完成，直到生成脚本成功后，点击关闭按钮即可。

如何将sql server数据库的数据批量导出

8. sql server怎么批量导入

1、打开“Microsoft SQL Server Management Studio” 并连接 数据库。
2、在需要导入数据的数据库上右键 - 任务 - 导入数据，打开“SQL Server导入导出向导”


3、点击下一步，选择“数据源”、“服务器名称”、“数据库”，填写“身份验证”信息，点击下一步。

4、选择“目标”、“服务器名称”、“数据库”，填写“身份验证”，点击下一步。

5、指定表复制或查询，点击下一步

6、选源要复制的表或视图，点击下一步

7、选择“立即执行”，点下一步或完成。