insert into c ... select a...,b... from a , b where a.cid = b.cid insert into d ... select a...,b... from a , b where a.cid = b.cid
拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) insert into b(a, b, c) select d,e,f from a;
insert into 表C(col1,col2...) select col1,col2... from 表A where .........?
就按照上面的格式来 前后字段一一对应 需要条件的话在后面加insert into b(a, b, c) select d,e,f from a where ..........
declare @startdate datetime declare @enddate datetimeselect @startdate='2009-10-01' select @enddate='2009-10-30'insert into c select cid,dt,account from a where dt between @startdate and @enddateinsert into d select cid,invt,sl from b where cid in (select cid from a where dt between @startdate and @enddate)
insert into c select cid , dt , ent from a where dt = '2009-10-15'insert into d select b.* from a , b where a.cid = b.cid and a.dt = '2009-10-15'
insert into c select cid , dt , ent from a where dt = '2009-10-15' --去掉后三位. insert into d select b.CID , substring(ivent,1,len(ivent)-3),qu from a , b where a.cid = b.cid and a.dt = '2009-10-15'
--把表A插入表C insert into 表C(id,ddate,cuscode) select cid,dt,ent from 表A where DT='2009-10-15'
insert into c select cid , dt , ent from a where [datetime] = '2009-10-15'insert into d select b.CID , substring(ivent,1,len(ivent)-3),qu from a , b where a.cid = b.cid and a.[datetime] = '2009-10-15'
--把表A插入表C insert into 表C(id,ddate,cuscode) select cid,dt,ent from 表A where DT='2009-10-15'--把表B中表B.CID=表A.cid 并且 日期等于查询日期的数据插入表D insert into D(id,ddate,cuscode) select a.cid,substring(b.ivent,1,len(b.ivent)-3),b.qu from A,B where a.cid=b.cid and a.DT='2009-10-15'
create table #t(CID varchar(3), DT datetime,ENT varchar(5)) insert #t select '001', '2009-10-15' , '023' union all select '002' , '2009-10-16' , '198' create table #t1(CID varchar(3), ivent varchar(15),qu int) insert #t1 select '001', '345678000', 2 union all select '001', '456789240', 6 union all select '002', '789567124', 1 union all select '002', '123456564', 2 union all select '002', '345678976', 4 create table #c( id varchar(3),ddata datetime,cuscode varchar(3)) create table #d( ID varchar(3), cinv varchar(15),quantity int) --------------- 语句 ----------------- insert #c(id,ddata,cuscode)select * from #t where dt='2009-10-15' insert #d(id,cinv,quantity)select b.* from #t a ,#t1 b where dt='2009-10-15' and a.cid=b.cid----------------- 结果 --------------- select * from #c select * from #did ddata cuscode ---- ------------------------------------------------------ ------- 001 2009-10-15 00:00:00.000 023 (所影响的行数为 1 行)ID cinv quantity ---- --------------- ----------- 001 345678000 2 001 456789240 6(所影响的行数为 2 行) drop table #t drop table #t1 drop table #c drop table #d
insert into #table select * from ....
--如果两库同机 insert into BNEW..c select cid , dt , ent from a where dt = '2009-10-15'insert into BNEW..d select b.CID , substring(ivent,1,len(ivent)-3),qu from a , b where a.cid = b.cid and a.dt = '2009-10-15'--如果两库不同机,先链接。 不同服务器数据库之间的数据操作--创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' --查询示例 select * from ITSV.数据库名.dbo.表名 --导入示例 select * into 表 from ITSV.数据库名.dbo.表名 --以后不再使用时删除链接服务器 exec sp_dropserver 'ITSV ', 'droplogins ' --连接远程/局域网数据(openrowset/openquery/opendatasource) --1、openrowset --查询示例 select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --生成本地表 select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --把本地表导入远程表 insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) select *from 本地表 --更新本地表 update b set b.列A=a.列A from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b on a.column1=b.column1 --openquery用法需要创建一个连接 --首先创建一个连接创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' --查询 select * FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') --把本地表导入远程表 insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') select * from 本地表 --更新本地表 update b set b.列B=a.列B FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a inner join 本地表 b on a.列A=b.列A --3、opendatasource/openrowset SELECT * FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta --把本地表导入远程表 insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名 select * from 本地表 --然后 insert into 机器名.BNEW..c select cid , dt , ent from a where dt = '2009-10-15'insert into 机器名.BNEW..d select b.CID , substring(ivent,1,len(ivent)-3),qu from a , b where a.cid = b.cid and a.dt = '2009-10-15'
create table A(CID nvarchar(10),DT datetime,ENT nvarchar(10)) go insert into A select '001','2009-10-15','023' union select '002','2009-10-16','198'create table B(CID nvarchar(10),ivent nvarchar(20),qu nvarchar(10)) go insert into B select '001','345678000','2' union select '001','456789240','6' union select '002','789567124','1' union select '002','123456564','2' union select '002','345678976','4' create table C(ID nvarchar(10),ddata datetime,cuscode nvarchar(10)) go create table D(ID nvarchar(10),cinv nvarchar(10),quantity nvarchar(10)) go declare @startdate datetimeselect @startdate='2009-10-15'insert into c select cid,dt,ENT from a where dt = @startdate insert into d select cid,left(ivent,len(ivent)-3),qu from b where cid in (select cid from a where dt = @startdate) select * from c select * from d/* ID ddata cuscode ---------- ------------------------------------------------------ ---------- 001 2009-10-15 00:00:00.000 023(所影响的行数为 1 行)ID cinv quantity ---------- ---------- ---------- 001 345678 2 001 456789 6*/ drop table a,b,c,d
--把表A插入表C insert into 表C(id,ddate,cuscode) select cid,dt,ent from 表A where DT='2009-10-15'--把表B中表B.CID=表A.cid 并且 日期等于查询日期的数据插入表D insert into D(id,ddate,cuscode) select a.cid,substring(b.ivent,1,len(b.ivent)-3),b.qu from A,B where a.cid=b.cid and a.DT='2009-10-15'
表C中还有一列flag 需要插入固定值 1怎么写进去
insert c(flag,id,ddata,cuscode)select 1,* from #t where dt='2009-10-15' insert d(id,cinv,quantity)select b.* from #t a ,#t1 b where dt='2009-10-15' and a.cid=b.cid
插入常量的时候直接写入常量就可以了,例如: insert into 表A(col) select 1
insert into 表C(id,ddate,cuscode,flag) select cid,dt,ent,1 from 表A where DT='2009-10-15'
再问 还需要插入一列 AUTOID 是递增升序排列的怎么加入?
select cid,dt,ent,1,identity(int,1,1) taoistong into #taoistong from 表A where DT='2009-10-15'insert into 表C(id,ddate,cuscode,flag,autoid) select * from #taoistong
insert into d ... select a...,b... from a , b where a.cid = b.cid
insert into b(a, b, c) select d,e,f from a;
select col1,col2... from 表A where .........?
declare @enddate datetimeselect @startdate='2009-10-01'
select @enddate='2009-10-30'insert into c
select cid,dt,account from a where dt between @startdate and @enddateinsert into d
select cid,invt,sl from b where cid in (select cid from a where dt between @startdate and @enddate)
表A CID DT ENT
001 2009-10-15 023
002 2009-10-16 198表B CID ivent qu
001 345678000 2
001 456789240 6
002 789567124 1
002 123456564 2
002 345678976 4表C id ddata cuscode表D id cinv quantity
通过条件查询DT 把表A插入表C 并把表B中表B.CID=表A.cid 并且 日期等于查询日期的数据插入表D例如 查询 2009-10-15 插入结果如下表C id ddata cuscode
001 2009-10-15 023表D id cinv quantity
001 345678 2
001 456789 6 (注意插入的时候表B中的IVENT要去掉后三位数据)
--去掉后三位.
insert into d select b.CID , substring(ivent,1,len(ivent)-3),qu from a , b where a.cid = b.cid and a.dt = '2009-10-15'
insert into 表C(id,ddate,cuscode) select cid,dt,ent from 表A where DT='2009-10-15'
c
select
cid , dt , ent
from
a
where
[datetime] = '2009-10-15'insert into
d
select
b.CID , substring(ivent,1,len(ivent)-3),qu
from
a , b
where
a.cid = b.cid
and
a.[datetime] = '2009-10-15'
insert into 表C(id,ddate,cuscode) select cid,dt,ent from 表A where DT='2009-10-15'--把表B中表B.CID=表A.cid 并且 日期等于查询日期的数据插入表D
insert into D(id,ddate,cuscode)
select a.cid,substring(b.ivent,1,len(b.ivent)-3),b.qu from A,B where a.cid=b.cid and a.DT='2009-10-15'
create table #t(CID varchar(3), DT datetime,ENT varchar(5))
insert #t select
'001', '2009-10-15' , '023' union all select
'002' , '2009-10-16' , '198'
create table #t1(CID varchar(3), ivent varchar(15),qu int)
insert #t1 select
'001', '345678000', 2 union all select
'001', '456789240', 6 union all select
'002', '789567124', 1 union all select
'002', '123456564', 2 union all select
'002', '345678976', 4 create table #c( id varchar(3),ddata datetime,cuscode varchar(3))
create table #d( ID varchar(3), cinv varchar(15),quantity int)
--------------- 语句 -----------------
insert #c(id,ddata,cuscode)select * from #t where dt='2009-10-15'
insert #d(id,cinv,quantity)select b.* from #t a ,#t1 b where dt='2009-10-15' and a.cid=b.cid----------------- 结果 ---------------
select * from #c
select * from #did ddata cuscode
---- ------------------------------------------------------ -------
001 2009-10-15 00:00:00.000 023
(所影响的行数为 1 行)ID cinv quantity
---- --------------- -----------
001 345678000 2
001 456789240 6(所影响的行数为 2 行)
drop table #t
drop table #t1
drop table #c
drop table #d
insert into BNEW..c select cid , dt , ent from a where dt = '2009-10-15'insert into BNEW..d select b.CID , substring(ivent,1,len(ivent)-3),qu from a , b where a.cid = b.cid and a.dt = '2009-10-15'--如果两库不同机,先链接。 不同服务器数据库之间的数据操作--创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' --查询示例
select * from ITSV.数据库名.dbo.表名 --导入示例
select * into 表 from ITSV.数据库名.dbo.表名 --以后不再使用时删除链接服务器
exec sp_dropserver 'ITSV ', 'droplogins ' --连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset --查询示例
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --生成本地表
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --把本地表导入远程表
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
select *from 本地表 --更新本地表
update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1 --openquery用法需要创建一个连接 --首先创建一个连接创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
--查询
select *
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
--把本地表导入远程表
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A --3、opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta
--把本地表导入远程表
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
select * from 本地表 --然后
insert into 机器名.BNEW..c select cid , dt , ent from a where dt = '2009-10-15'insert into 机器名.BNEW..d select b.CID , substring(ivent,1,len(ivent)-3),qu from a , b where a.cid = b.cid and a.dt = '2009-10-15'
go
insert into A select
'001','2009-10-15','023' union select
'002','2009-10-16','198'create table B(CID nvarchar(10),ivent nvarchar(20),qu nvarchar(10))
go
insert into B select
'001','345678000','2' union select
'001','456789240','6' union select
'002','789567124','1' union select
'002','123456564','2' union select
'002','345678976','4'
create table C(ID nvarchar(10),ddata datetime,cuscode nvarchar(10))
go
create table D(ID nvarchar(10),cinv nvarchar(10),quantity nvarchar(10))
go
declare @startdate datetimeselect @startdate='2009-10-15'insert into c
select cid,dt,ENT from a where dt = @startdate insert into d
select cid,left(ivent,len(ivent)-3),qu from b where cid in (select cid from a where dt = @startdate)
select * from c
select * from d/*
ID ddata cuscode
---------- ------------------------------------------------------ ----------
001 2009-10-15 00:00:00.000 023(所影响的行数为 1 行)ID cinv quantity
---------- ---------- ----------
001 345678 2
001 456789 6*/
drop table a,b,c,d
insert into 表C(id,ddate,cuscode) select cid,dt,ent from 表A where DT='2009-10-15'--把表B中表B.CID=表A.cid 并且 日期等于查询日期的数据插入表D
insert into D(id,ddate,cuscode)
select a.cid,substring(b.ivent,1,len(b.ivent)-3),b.qu from A,B where a.cid=b.cid and a.DT='2009-10-15'
insert c(flag,id,ddata,cuscode)select 1,* from #t where dt='2009-10-15'
insert d(id,cinv,quantity)select b.* from #t a ,#t1 b where dt='2009-10-15' and a.cid=b.cid
insert into 表A(col) select 1
into #taoistong
from 表A where DT='2009-10-15'insert into 表C(id,ddate,cuscode,flag,autoid)
select * from #taoistong
也就是说,insert语句里不用写id试一下吧,sqlserver的自增量和ORACLE不同,不用另外写,能自动加的
但是我这里插入的时候 自增列为什么提示我不能插入空值呢????
insert into 表C(ddate,cuscode) select dt,ent from 表A where DT='2009-10-15'
ID不是自增列 autoID是自增列 autoid 我是没写进去啊 但是执行的时候提示 不能再autoID中插入NULL值