数据库有个表Customer,其数据是来自0.28的表UserListinsert into Customer (CustomerId,Account,FullName,Tel,Contactor)
select CustomerId,Account,FullName,Tel,Contactor from [192.168.0.28].[OID2009].[dbo].[UserList]
where deleted=0 and len(Account)>8 and CustomerId not in (select CustomerId from Customer)
现在想把表UserList中的一列数据Address也增加到Customer表中,不知道SQL语句怎么写
select CustomerId,Account,FullName,Tel,Contactor from [192.168.0.28].[OID2009].[dbo].[UserList]
where deleted=0 and len(Account)>8 and CustomerId not in (select CustomerId from Customer)
现在想把表UserList中的一列数据Address也增加到Customer表中,不知道SQL语句怎么写
解决方案 »
- 应收应付问题,怎么把一列数字分为两列显示
- 初学者一个很幼稚简单的问题
- 小游戏比赛数据库的设计
- 大家帮忙思考一个SQL逻辑,连续工作日的发生交易问题
- 在触发器中我使用了一个动态执行的SQL语句,但提示INSERTED对像不存在?这条语句是什么原因?请教!!!
- 请问 如何把select count(cl1) from table1 where cl1=@strcl1 的结果赋值给@intcount?
- 求一个储存过程
- 有没有好的办法将数据权限与业务分开?!
- 请问如何写这样的SQL语句
- SQL Server请大侠指教!
- 执行存储过程报错,附上了数据和数据库以及出错的报告,求人解决。
- 这个触发器怎么建?
go
insert into Customer (CustomerId,Account,FullName,Tel,Contactor,address)
select CustomerId,Account,FullName,Tel,Contactor,address from [192.168.0.28].[OID2009].[dbo].[UserList]
where deleted=0 and len(Account)>8 and CustomerId not in (select CustomerId from Customer)
select CustomerId,Account,FullName,Tel,Contactor,Address from [192.168.0.28].[OID2009].[dbo].[UserList]
where deleted=0 and len(Account)>8 and CustomerId not in (select CustomerId from Customer)
说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
单纯增加一列语法
alter table Customer with no check add address varchar(200);
go
--2更改 Customer已有数据行
update Customer set address
from [192.168.0.28].[OID2009].[dbo].[UserList] a
inner join Customer c on a.CustomerId=c.CustomerId
--3.增加 Customer中没有的行
insert into Customer (CustomerId,Account,FullName,Tel,Contactor)
select CustomerId,Account,FullName,Tel,Contactor from [192.168.0.28].[OID2009].[dbo].[UserList]
where deleted=0 and len(Account)>8 and CustomerId not in (select CustomerId from Customer)
alter table Customer with no check add address varchar(200);
go
--2更改 Customer已有数据行
update Customer set address= a.address
from [192.168.0.28].[OID2009].[dbo].[UserList] a
inner join Customer c on a.CustomerId=c.CustomerId
--3.增加 Customer中没有的行
insert into Customer (CustomerId,Account,FullName,Tel,Contactor)
select CustomerId,Account,FullName,Tel,Contactor from [192.168.0.28].[OID2009].[dbo].[UserList]
where deleted=0 and len(Account)>8 and CustomerId not in (select CustomerId from Customer)
alter table Customer with nocheck add address varchar(200);
go
update Customer set address= ul.address
from [192.168.0.28].[OID2009].[dbo].[UserList] ul
inner join Customer c on ul.CustomerId=c.CustomerId
alter table Customer with nocheck add address varchar(200);
go
update Customer set address= ul.address
from [192.168.0.28].[OID2009].[dbo].[UserList] ul
inner join Customer c on ul.CustomerId=c.CustomerId