/* 建议:同一表字段之间应该是更新而不是插入。
唯一要担心的就是数字太长,转 int 会溢出。 int大约10位左右,9位安全*/
--要求:itemCode_C列 TB2014-58 去掉 TB 和 - 转换为int类型 存储在 sysSort列
UPDATE ProjectInformation
set sysSort = Convert(int,Replace(SubString(itemCode_C,3,48),'-',''))
from ProjectInformation
--SubString ( expression, start, length )
--参数:expression 字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。
--start 整数或可以隐式转换为 int 的表达式,指定子字符串的开始位置。
--length 整数或可以隐式转换为 int 的表达式,指定子字符串的长度。
--
/*
* sql,两个表关联,根据B表更新A表
* update A set id=B.id from A,B where A.name=B.name
*/
UPDATE crm_cb_cust
SET CustDist = dddd$.CustDist
FROM crm_cb_cust,dddd$
WHERE crm_cb_cust.CustCode = dddd$.CustCode
--插入新增 Contacts_T1 不存在的卡号
insert [CRMSERVER].[DeLiXiTenderSystem2].dbo.[Contacts_T1](card_C)
select CustCode from CRM_CB_Sale i
where not exists( select * from [CRMSERVER].[DeLiXiTenderSystem2].dbo.[Contacts_T1] where card_C=cast(i.CustCode as varchar(50)))
--联合结果集
--有时候我们需要组合俩个完全不同的查询结果集,而这俩个查询结果之间没有必的联系,只是我们需要将他们显示在
--一个结果集中而已.在SQL中可以使用 union 运算符来将俩个或者多个查询结果集 联合为一个结果集中.
--需要列数相同 和 相同位置的列字段类型相容 (技巧可以通过常量字段补足就好了.select '1',xxx from 表)
SELECT fnumber,fname,Fage FROM dbo.T_Employee
union
SELECT FidCarNumber,FName,Fage FROM dbo.T_TempEmployee
--默认情况下,union合并了俩个查询结果集,其中完全重复的数据行被合并为一条.如果需要在联合结果集中返回所有的
--记录而不管他们是否唯一,则需要在union后使用all 比如下面
SELECT fnumber,fname,Fage FROM dbo.T_Employee
UNION ALL
SELECT FidCarNumber,FName,Fage FROM dbo.T_TempEmployee
---------------------------------------------------
/*
* 用于不同服务器数据库之间的关联
*/
--1.1 创建一个链接名
EXEC sp_addlinkedserver 'LinkOa','','SQLOLEDB','192.168.17.88' --有自定义实例名还要加上"/实例名"
EXEC sp_addlinkedsrvlogin 'LinkOa','false',NULL,'oa_npp','sql+SERVER'
--范例
EXEC sp_addlinkedserver
@server='DBVIP',--被访问的服务器别名(任意的名称)
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='MYSQLServer' --要访问的服务器(SQL SERVER实例名)
EXEC sp_addlinkedsrvlogin
'DBVIP', --被访问的服务器别名
'false',
NULL,
'sa', --登陆链接服务器的帐号
'1q2w3e4R' --登陆链接服务器的密码
--查看已注册的链接服务器
--exec sp_linkedservers
--把本地的表数据插入到链接服务器上的表中
SELECT * INTO DBVIP.database_name.dbo.table_name FROM local_table_name
--DBVIP.database_name.dbo.table_name 远程服务器完整表名(必须用4部分表示)
--local_table_name 本地表名
--用完后可删除
--Exec sp_droplinkedsrvlogin DBVIP,NULL --删除链接服务器的登陆帐户
--Exec sp_dropserver DBVIP --删除链接服务器
---------------------------------------------------
--要求:将OA数据库中HrmDepartment表的数据 特定字段 更新到本地数据库Down_Xqbm表中.
insert [IT-CHENXIAOPENG].[Npp_Pro].dbo.[Down_Xqbm](keyName_C,keyValue_C,keyId)
SELECT departmentname,departmentmark,id FROM [LinkOa].[oa].dbo.[HrmDepartment]
--事务范例 事务基本语法
--在正常情况下数据库的处理均是事务
--我们执行的一个插入,删除等等一系列操作,每一句话都是一个事务(SQL Server 默认)
--我们需要执行多条语句为一个事务的时候,就需要声明了.
begin TRANSACTION
declare @myError int;
set @myError = 0;
update bank set balance=balance - 1000 where cid='0001';
set @myError = @myError + @@ERROR;
update bank set balance=balance + 1000 where cid='0002';
set @myError = @myError + @@ERROR;
if(@myError > 0)
BEGIN
ROLLBACK
END
ELSE
BEGIN
commit
END
GO
--查看sql server 企业管理器版本
use master
select @@VERSION
-------------------选中表名 配置为Ctrl+3 即可select * 表
GO
/****** 对象: StoredProcedure [dbo].[SP_Select] 脚本日期: 05/28/2015 21:46:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[SP_Select]
@OName varchar( 100)
As
Declare @Str Varchar (1000),
@dbname varchar( 40)
set @dbname= db_name()
Set @Str= 'Select * from '+@dbname +'.dbo.'+ @OName
Exec ( @Str)
-------------------选中表名 配置为Ctrl+8 即可查此表的所有列名
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_syscolumns] --Exec sp_syscolumns 'eemployee'
@Object NVARCHAR( 1000)
As
/*
Function:取得一个对象中的所有列的项目 (主要针对表)
Remark: Create By Deam L 2013/4/7
*/
Begin
Set nocount on
Declare @Name NVARCHAR (1000)
Select @Name= Isnull(@Name +',', '')+name From syscolumns Where id=object_id( @Object)
Print @Name
Set nocount off
END
--sql语句块 引用
with cte as
(
select
t1.itemCode_C, t2.P_itemCode_C,
sys_tbxh_C, sys_tbxhgg_C
from ProjectInformation as t1
inner join Project_PMana_New as t2
on t1.itemCode_C = t2.P_itemCode_C
group by t1.itemCode_C, t2.P_itemCode_C,
sys_tbxh_C,sys_tbxhgg_C
)
update ProjectInformation
set sys_tbxh_C = cxp2.sys_tbxhgg_C
from ProjectInformation,
(SELECT
itemCode_C, P_itemCode_C,
sys_tbxh_C, sys_tbxhgg_C = stuff((SELECT ',' + sys_tbxhgg_C FROM cte AS t
WHERE t.itemCode_C = cte.itemCode_C FOR xml path('')), 1, 1, '')
FROM cte
GROUP BY itemCode_C, P_itemCode_C, sys_tbxh_C) as cxp2
where ProjectInformation.itemCode_C = cxp2.itemCode_C
---------------根据某列分组后 类加其他列值
create table tb(id int, value varchar(10))
insert into tb values(1,'aa')
insert into tb values(1,'bb')
insert into tb values(2,'aaa')
insert into tb values(2,'bbb')
insert into tb values(2,'ccc')
GO
SELECT * FROM tb
SELECT id, value = stuff((SELECT ',' + value FROM tb AS t
WHERE t .id = tb.id FOR xml path('')), 1, 1, '')
FROM tb
GROUP BY id