sql 批量插入多条记录代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 sql 批量插入多条记录
作者:Dezai.CN / 发布于2011/6/23/ 522
<div>
为了方便测试，我们通常在数据库中插入许多记录，以便测试。但如果手工添加会很麻烦，所以一般会采用批量添加的方式。

注：
　　1）例子的表结构如下：
<blockquote>
<table style="border-collapse: collapse" border="0" cellspacing="0" cellpadding="0" width="302" height="133">
    <colgroup><col style="width: 77pt" width="102" /><col style="width: 110pt" width="147" /></colgroup>
    <tbody>
        <tr style="height: 13.5pt" height="18">
            <td style="width: 77pt; height: 13.5pt" height="18" width="102">column name</td>
            <td style="width: 110pt" width="147">data type</td>
        </tr>
        <tr style="height: 13.5pt" height="18">
            <td style="height: 13.5pt" height="18">ID</td>
            <td>uniqueidentifier</td>
        </tr>
        <tr style="height: 13.5pt" height="18">
            <td style="height: 13.5pt" height="18">userNo</td>
            <td>nvarchar(50)</td>
        </tr>
        <tr style="height: 13.5pt" height="18">
            <td style="height: 13.5pt" height="18">password</td>
            <td>nvarchar(255)</td>
        </tr>
        <tr style="height: 13.5pt" height="18">
            <td style="height: 13.5pt" height="18">lastName</td>
            <td>nvarchar(255)</td>
        </tr>
        <tr style="height: 13.5pt" height="18">
            <td style="height: 13.5pt" height="18">firstName</td>
            <td>nvarchar(255)</td>
        </tr>
    </tbody>
</table>
</blockquote>
　　2) 以下代码在ms sql2005 中测试过。


<strong>1、一次性插入手动编写的多条记录。</strong>
例，下边的代码一次性插入2条记录 。
<div style="width: 900px" class="cnblogs_code">
<div><!--<br/  />
<br/  />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br/  />
http://www.CodeHighlighter.com/<br/  />
<br/  />
--><span style="color: #0000ff">insert</span><span style="color: #000000"> </span><span style="color: #0000ff">into</span><span style="color: #000000"> PW_User(id,userNo,password,firstName,lastName)
</span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff00ff">NewID</span><span style="color: #000000">(),</span><span style="color: #ff0000">'</span><span style="color: #ff0000">test00001</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">test00001</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">张</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">一</span><span style="color: #ff0000">'</span><span style="color: #000000">
</span><span style="color: #0000ff">UNION</span><span style="color: #000000"> </span><span style="color: #808080">ALL</span><span style="color: #000000"> 
</span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff00ff">NewID</span><span style="color: #000000">(),</span><span style="color: #ff0000">'</span><span style="color: #ff0000">test00002</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">test00002</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">张</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">二</span><span style="color: #ff0000">'</span></div>
</div>


<strong>2、一次插入语句生成的多条记录。</strong>

例，下边的代码一次性插入100条记录。
<div style="width: 900px" class="cnblogs_code"><img style="display: none" id="code_img_closed_19472707-068f-4193-8447-e33b4e7a5b12" class="code_img_closed" alt="" src="http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" /><img id="code_img_opened_19472707-068f-4193-8447-e33b4e7a5b12" class="code_img_opened" src="http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="" /><span class="cnblogs_code_collapse">代码</span>
<div id="cnblogs_code_open_19472707-068f-4193-8447-e33b4e7a5b12">
<div><!--<br/  />
<br/  />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br/  />
http://www.CodeHighlighter.com/<br/  />
<br/  />
--><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@i</span><span style="color: #000000"> </span><span style="color: #0000ff">int</span><span style="color: #000000">,</span><span style="color: #008000">@value</span><span style="color: #000000"> </span><span style="color: #0000ff">nvarchar</span><span style="color: #000000">
</span><span style="color: #0000ff">begin</span><span style="color: #000000">
</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@i</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">初始化 </span><span style="color: #008080">
</span><span style="color: #0000ff">while</span><span style="color: #000000"> </span><span style="color: #008000">@i</span><span style="color: #808080"><=</span><span style="color: #800000; font-weight: bold">100</span><span style="color: #000000"> 
</span><span style="color: #0000ff">begin</span><span style="color: #000000">
</span><span style="color: #0000ff">INSERT</span><span style="color: #000000"> </span><span style="color: #0000ff">INTO</span><span style="color: #000000"> PW_User(id,userNo,password,firstName,lastName)
</span><span style="color: #0000ff">VALUES</span><span style="color: #000000">(</span><span style="color: #ff00ff">NewID</span><span style="color: #000000">(),</span><span style="color: #ff0000">'</span><span style="color: #ff0000">test0000</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #ff00ff">cast</span><span style="color: #000000">(</span><span style="color: #008000">@i</span><span style="color: #000000"> </span><span style="color: #0000ff">as</span><span style="color: #000000"> </span><span style="color: #0000ff">nvarchar</span><span style="color: #000000">),</span><span style="color: #ff0000">'</span><span style="color: #ff0000">test0000</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #ff00ff">cast</span><span style="color: #000000">(</span><span style="color: #008000">@i</span><span style="color: #000000"> </span><span style="color: #0000ff">as</span><span style="color: #000000"> </span><span style="color: #0000ff">nvarchar</span><span style="color: #000000">),</span><span style="color: #ff0000">'</span><span style="color: #ff0000">张</span><span style="color: #ff0000">'</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">0000</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #ff00ff">cast</span><span style="color: #000000">(</span><span style="color: #008000">@i</span><span style="color: #000000"> </span><span style="color: #0000ff">as</span><span style="color: #000000"> </span><span style="color: #0000ff">nvarchar</span><span style="color: #000000">)</span><span style="color: #000000">)
</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@i</span><span style="color: #808080">=</span><span style="color: #008000">@i</span><span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">
</span><span style="color: #0000ff">end</span><span style="color: #000000">
</span><span style="color: #0000ff">end</span><span style="color: #000000">
</span></div>
</div>
</div>
</div>
试试其它关键字
　批量插入　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)