sql将一个表中的数据插入到另一个表中的方法
这篇文章主要介绍了sql将一个表中的数据插入到另一个表中的方法,需要的朋友可以参考下
id="cproIframe_u1892994_2" width="580" height="90" src="http://pos.baidu.com/acom?adn=3&at=231&aurl=&cad=1&ccd=24&cec=GBK&cfv=17&ch=0&col=zh-CN&conBW=0&conOP=1&cpa=1&dai=2&dis=0<r=https%3A%2F%2Fwww.baidu.com%2Flink%3Furl%3DioOSy9-6T1AhGa7xRuP47_eSSKLMhLOKr7HqR8JWc693WXJuvSlUsxIIE-C5hmjv%26wd%3D%26eqid%3Dfe51cd230009044e00000005555bf361%26ie%3Dutf-8<u=http%3A%2F%2Fwww.jb51.net%2Farticle%2F48363.htm&lu_161=0&lunum=6&n=jb51_cpr&pcs=1306x530&pis=10000x10000&ps=490x188&psr=1440x900&pss=1306x491&qn=aa90b2af2c437463&rad=&rsi0=580&rsi1=90&rsi5=4&rss0=%23FFFFFF&rss1=%23F7FCFF&rss2=%230000ff&rss3=%23444444&rss4=%23008000&rss5=&rss6=%23e10900&rss7=&scale=&skin=tabcloud_skin_3&stid=5&td_id=1892994&titFF=%E5%AE%8B%E4%BD%93&titFS=12&titTA=left&tn=text_default_580_90&tpr=1432089464968&ts=1&version=2.0&xuanting=0&dtm=BAIDU_DUP2_SETJSONADSLOT&dc=2&di=u1892994&ti=sql%E5%B0%86%E4%B8%80%E4%B8%AA%E8%A1%A8%E4%B8%AD%E7%9A%84%E6%95%B0%E6%8D%AE%E6%8F%92%E5%85%A5%E5%88%B0%E5%8F%A6%E4%B8%80%E4%B8%AA%E8%A1%A8%E4%B8%AD%E7%9A%84%E6%96%B9%E6%B3%95_MsSql_%E8%84%9A%E6%9C%AC%E4%B9%8B%E5%AE%B6&tt=1432089464954.335.596.597" align="center,center" marginwidth="0" marginheight="0" scrolling="no" frameborder="0" allowtransparency="true">
列名不一定要相同,只要你在HH中列出要插入列的列表跟select from mm表中的选择的列的列表一一对应就可以了,当然两边的数据类型应该是兼容的。
insert into hh (fielda,fieldb,fieldc) select fieldx,fieldy,fieldz from mm
---更新计量点中不存在的数据,将台帐中的信息转移到计量点中
insert into MetricPoints (MeterID,MetricPointName,[Description],DepartmentID,MediumID)
(SELECT m.MeterID,m.MetricItems+m.InstallPlace as m_MetricPointName,m.MetricItems,m.DepartmentID,m.MediumID
FROM Meters m WHERE NOT EXISTS (SELECT 1 FROM MetricPoints WHERE MetricPoints.MeterID = m.MeterID ) and m.MediumID =2)
声名:a,b ,都是表
--b表存在(两表结构一样)
insert into b select * from a
若两表只是有部分(字段)相同,则
insert into b(col1,col2,col3,col4,...) select col1,col2,col3,col4,... from a where...
把表a插入到表b中去。
--b表不存在
select * into b from a
//
select (字段1,字段2,...) into b from a
insert into hh (fielda,fieldb,fieldc) select fieldx,fieldy,fieldz from mm
复制代码代码如下:
---更新计量点中不存在的数据,将台帐中的信息转移到计量点中
insert into MetricPoints (MeterID,MetricPointName,[Description],DepartmentID,MediumID)
(SELECT m.MeterID,m.MetricItems+m.InstallPlace as m_MetricPointName,m.MetricItems,m.DepartmentID,m.MediumID
FROM Meters m WHERE NOT EXISTS (SELECT 1 FROM MetricPoints WHERE MetricPoints.MeterID = m.MeterID ) and m.MediumID =2)
声名:a,b ,都是表
复制代码代码如下:
--b表存在(两表结构一样)
insert into b select * from a
若两表只是有部分(字段)相同,则
复制代码代码如下:
insert into b(col1,col2,col3,col4,...) select col1,col2,col3,col4,... from a where...
把表a插入到表b中去。
复制代码代码如下:
--b表不存在
select * into b from a
//
select (字段1,字段2,...) into b from a