2018-04-21

2018 年 4 月 21 日 by 沧海之树 In 工作

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [ID]
,[Original]
,[Now]
FROM [bh20180420].[dbo].[EmployeeID]

update Employee set PositionID='12',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('2031','2069','2070','2802','2805','2806','2808','2809','2810','8819'))

update Employee set PositionID='10',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1220','1283','3001','3002','3007','3102','3203','3218','3219','3220','3223','3225','3568','3997','3999'))

update Employee set PositionID='61',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1100','1155','1156','1157','1158','1159'))

update Employee set PositionID='22',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('2022','2901'))

update Employee set PositionID='47',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1007','1013','1016','1020','1030','1266','1268','1271','1279','1308','1621','1830','1831','1835'))

update Employee set PositionID='04',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1001','1015','1101','1826','1836','1845','1850','1851','9001'))

update Employee set PositionID='23',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1106','1116','1117','1118','1123','1132','1133','1134','1135','1605'))

update Employee set PositionID='06',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1601','1602','1609','1613','1620','1623','1624','1625','1626','1628'))

update Employee set PositionID='01',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1284','1285','1286','1287'))

update Employee set PositionID='07',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('8003','8010'))

update Employee set PositionID='60',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1503','1619','1622','6603','6604','6606','6607','6608','6609','6610','6611','6612'))

update Employee set PositionID='39',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1265','88001','88002','88005','88010','88011','88015','88019','88021','88023','88028','88029','88030','88032','88033','88037','88040','88068','88069','88070','88071','88072','88073','88074'))

select * from Employee

select * from H_Employee where sDept='001004' and sStau='正常'

select * from H_Employee where sName='金楠楠L'

select * from H_Employee where sDept='001005' and sStau='正常'

select * from H_Employee where sDept='001007' and sStau='正常'

select * from H_Employee where sDept='001014' and sStau='正常'

select * from H_Employee where sDept='001015' and sStau='正常'

select * from H_Employee where sDept='001015001' and sStau='正常'

select * from H_Employee where sDept='001015002' and sStau='正常'

select * from H_Employee where sDept='001015004' and sStau='正常'

select * from H_Employee where sDept='001015005' and sStau='正常'

select * from H_Employee where sDept='001018003' and sStau='正常'

select * from H_Employee where sDept='001020' and sStau='正常'

select * from H_Employee where sDept='001021' and sStau='正常'

 

--insert into [bh20180420].[dbo].[DD_Department] select * from [bh].[dbo].[DD_Department]
--insert into [bh20180420].[dbo].[IncomeCategory] select * from [bh].[dbo].[IncomeCategory]
--insert into [bh20180420].[dbo].[Role] select * from [bh].[dbo].[Role]
--insert into [bh20180420].[dbo].[CustomerUnderstandWay] select * from [bh].[dbo].[CustomerUnderstandWay]
--insert into [bh20180420].[dbo].[PresentationOutsideCategory](Name) select Name from [bh].[dbo].[PresentationOutsideCategory]
--insert into [bh20180420].[dbo].[PresentationOutside](Name,CategoryID,Phone) select Name,1,Phone from [bh].[dbo].[PresentationOutside]
--insert into [bh20180420].[dbo].[NewRankInfo] select * from [bh].[dbo].[NewRankInfo]
--insert into [bh20180420].[dbo].[PayModeInfo] select * from [bh].[dbo].[PayModeInfo]
--insert into [bh20180420].[dbo].[CustomerRegion] select * from [bh].[dbo].[CustomerRegion]
--insert into [bh20180420].[dbo].[GoodsFactoryInfo] select * from [bh].[dbo].[GoodsFactoryInfo]
--insert into [bh20180420].[dbo].[MedicineFactoryInfo] select * from [bh].[dbo].[MedicineFactoryInfo]
--insert into [bh20180420].[dbo].[medicinecategory] select * from [bh].[dbo].[medicinecategory]
--insert into [bh20180420].[dbo].[ItemTypeInfo] select * from [bh].[dbo].[ItemTypeInfo]
--insert into [bh20180420].[dbo].[FinanceTotalIncomeTypeInfo] select * from [bh].[dbo].[FinanceTotalIncomeTypeInfo]
--insert into [bh20180420].[dbo].[ServerStatus] select * from [bh].[dbo].[ServerStatus]
--insert into [bh20180420].[dbo].[SystemParas] select * from [bh].[dbo].[SystemParas]

--insert into [bh20180420].[dbo].[CheckUpdater] select * from [bh].[dbo].[CheckUpdater]

--insert into [bh20180420].[dbo].[PowerTemplateHistory](Template,Date) select Template,Date from [bh].[dbo].[PowerTemplateHistory]
--insert into [bh20180420].[dbo].[CarBrandInfo](Brand) select Brand from [bh].[dbo].[CarBrandInfo]
--insert into [bh20180420].[dbo].[CarModelInfo](BrandID,Model) select BrandID,Model from [bh].[dbo].[CarModelInfo]
--insert into [bh20180420].[dbo].[CustomerInterest] select * from [bh].[dbo].[CustomerInterest]
--insert into [bh20180420].[dbo].[CustomerProfession] select * from [bh].[dbo].[CustomerProfession]
--insert into [bh20180420].[dbo].[Device] select * from [bh].[dbo].[Device]
--insert into [bh20180420].[dbo].[HandselItemMode] select * from [bh].[dbo].[HandselItemMode]
--insert into [bh20180420].[dbo].[InjectionPlaceInfo] select * from [bh].[dbo].[InjectionPlaceInfo]
--insert into [bh20180420].[dbo].[InModeInfo](InModeName,Remark) select InModeName,Remark from [bh].[dbo].[InModeInfo]
--insert into [bh20180420].[dbo].[KindofSickness] select * from [bh].[dbo].[KindofSickness]

--insert into [bh20180420].[dbo].[NetChannel] select * from [bh].[dbo].[NetChannel]
--insert into [bh20180420].[dbo].[NewNetChannel] select * from [bh].[dbo].[NewNetChannel]
--insert into [bh20180420].[dbo].[OperationInfo] select * from [bh].[dbo].[OperationInfo]
--insert into [bh20180420].[dbo].[OutModeInfo] select * from [bh].[dbo].[OutModeInfo]
--insert into [bh20180420].[dbo].[PackageMediDetails](PackageID,MedicineCode,Quantity,Unit) select PackageID,MedicineCode,Quantity,Unit from [bh].[dbo].[PackageMediDetails]

--insert into [bh20180420].[dbo].[PositionInfo] select * from [bh].[dbo].[PositionInfo]
--insert into [bh20180420].[dbo].[ValidationUser] select * from [bh].[dbo].[ValidationUser]
--insert into [bh20180420].[dbo].[CountSettings] select * from [bh].[dbo].[CountSettings]
--insert into [bh20180420].[dbo].[Role] select * from [bh].[dbo].[Role]
--insert into [bh20180420].[dbo].[MemberScontoForDeptInfo](NewRankID,CountSettingsID,Sconto,Remark) select NewRankID,CountSettingsID,Sconto,Remark from [bh].[dbo].[MemberScontoForDeptInfo]

select Name,count(*)
from Employee
where workstatus='在职' group by Name

select Name,count(*)
from Employee
group by Name

select * from Employee where Name='廉锋'

select * from PositionInfo

select * from Employee

select * from bh20180420.dbo.CustomerBaseInfo where OtherInfo like '%150318%' and OtherInfo not in (select FID from H_TMK where FID like '%150318%') ;

select * from bh20180420.dbo.CustomerBaseInfo where OtherInfo like '%150318%' and OtherInfo not in (select FID from H_TMK where FID like '%150318%') ;

select FID from H_TMK where FID like '%150318%' and FID not in (select OtherInfo from bh20180420.dbo.CustomerBaseInfo)

select OtherInfo from bh20180420.dbo.CustomerBaseInfo where OtherInfo like '%150318%'

delete from bh20180420.dbo.CustomerBaseInfo where OtherInfo like '%150318%'

select * from H_TMK where FID like '%150318%' order by DT
select * from bh20180420.dbo.CustomerBaseInfoID where Original='150318';
GX5BAJ5QKV

ZZZB5DVLEV

select count(*) from bh20180420.dbo.CustomerBaseInfoID

select count(*) from bh20180420.dbo.CustomerBaseInfo

select count(*) from H_TMK;
56595
110614

54002
56612

65073
45541

 

--insert into [bh20180420].[dbo].[DD_Department] select * from [bh].[dbo].[DD_Department]
--insert into [bh20180420].[dbo].[IncomeCategory] select * from [bh].[dbo].[IncomeCategory]
--insert into [bh20180420].[dbo].[Role] select * from [bh].[dbo].[Role]
--insert into [bh20180420].[dbo].[PositionInfo] select * from [bh].[dbo].[PositionInfo]
--insert into [bh20180420].[dbo].[CustomerUnderstandWay] select * from [bh].[dbo].[CustomerUnderstandWay]
--insert into [bh20180420].[dbo].[PresentationOutsideCategory](Name) select Name from [bh].[dbo].[PresentationOutsideCategory]
--insert into [bh20180420].[dbo].[PresentationOutside](Name,CategoryID,Phone) select Name,1,Phone from [bh].[dbo].[PresentationOutside]
--insert into [bh20180420].[dbo].[NewRankInfo] select * from [bh].[dbo].[NewRankInfo]
--insert into [bh20180420].[dbo].[PayModeInfo] select * from [bh].[dbo].[PayModeInfo]
--insert into [bh20180420].[dbo].[CustomerRegion] select * from [bh].[dbo].[CustomerRegion]
--insert into [bh20180420].[dbo].[GoodsFactoryInfo] select * from [bh].[dbo].[GoodsFactoryInfo]
--insert into [bh20180420].[dbo].[MedicineFactoryInfo] select * from [bh].[dbo].[MedicineFactoryInfo]
--insert into [bh20180420].[dbo].[medicinecategory] select * from [bh].[dbo].[medicinecategory]
--insert into [bh20180420].[dbo].[ItemTypeInfo] select * from [bh].[dbo].[ItemTypeInfo]
--insert into [bh20180420].[dbo].[FinanceTotalIncomeTypeInfo] select * from [bh].[dbo].[FinanceTotalIncomeTypeInfo]
--insert into [bh20180420].[dbo].[ServerStatus] select * from [bh].[dbo].[ServerStatus]
--insert into [bh20180420].[dbo].[SystemParas] select * from [bh].[dbo].[SystemParas]
--insert into [bh20180420].[dbo].[ValidationUser] select * from [bh].[dbo].[ValidationUser]
--insert into [bh20180420].[dbo].[CheckUpdater] select * from [bh].[dbo].[CheckUpdater]
--insert into [bh20180420].[dbo].[CountSettings] select * from [bh].[dbo].[CountSettings]
--insert into [bh20180420].[dbo].[Role] select * from [bh].[dbo].[Role]
--insert into [bh20180420].[dbo].[PowerTemplateHistory](Template,Date) select Template,Date from [bh].[dbo].[PowerTemplateHistory]
--insert into [bh20180420].[dbo].[CarBrandInfo](Brand) select Brand from [bh].[dbo].[CarBrandInfo]
--insert into [bh20180420].[dbo].[CarModelInfo](BrandID,Model) select BrandID,Model from [bh].[dbo].[CarModelInfo]
--insert into [bh20180420].[dbo].[CustomerInterest] select * from [bh].[dbo].[CustomerInterest]
--insert into [bh20180420].[dbo].[CustomerProfession] select * from [bh].[dbo].[CustomerProfession]
--insert into [bh20180420].[dbo].[Device] select * from [bh].[dbo].[Device]
--insert into [bh20180420].[dbo].[HandselItemMode] select * from [bh].[dbo].[HandselItemMode]
--insert into [bh20180420].[dbo].[InjectionPlaceInfo] select * from [bh].[dbo].[InjectionPlaceInfo]
--insert into [bh20180420].[dbo].[InModeInfo](InModeName,Remark) select InModeName,Remark from [bh].[dbo].[InModeInfo]
--insert into [bh20180420].[dbo].[KindofSickness] select * from [bh].[dbo].[KindofSickness]
--insert into [bh20180420].[dbo].[MemberScontoForDeptInfo](NewRankID,CountSettingsID,Sconto,Remark) select NewRankID,CountSettingsID,Sconto,Remark from [bh].[dbo].[MemberScontoForDeptInfo]
--insert into [bh20180420].[dbo].[NetChannel] select * from [bh].[dbo].[NetChannel]
--insert into [bh20180420].[dbo].[NewNetChannel] select * from [bh].[dbo].[NewNetChannel]
--insert into [bh20180420].[dbo].[OperationInfo] select * from [bh].[dbo].[OperationInfo]
--insert into [bh20180420].[dbo].[OutModeInfo] select * from [bh].[dbo].[OutModeInfo]
--insert into [bh20180420].[dbo].[PackageMediDetails](PackageID,MedicineCode,Quantity,Unit) select PackageID,MedicineCode,Quantity,Unit from [bh].[dbo].[PackageMediDetails]
--insert into [bh20180420].[dbo].[GoodsFactoryInfo] select * from [bh].[dbo].[GoodsFactoryInfo]
--insert into [bh20180420].[dbo].[MedicineFactoryInfo] select * from [bh].[dbo].[MedicineFactoryInfo]
--insert into [bh20180420].[dbo].[Department] select * from [bh].[dbo].[Department]
--insert into [bh20180420].[dbo].[EducationInfo] select * from [bh].[dbo].[EducationInfo]

select * from [bh].[dbo].[ItemCategory]

select * from [bh20180420].[dbo].[ItemCategory]
select * from [bh20180414].[dbo].[ItemCategory]

select * from [bh].[dbo].[ItemInfo]
select * from [bh20180414].[dbo].[ItemInfo]
select * from [bh].[dbo].[ItemInfo]
select * from [bh20180420].[dbo].[ItemInfo]
select count(*) from [YUN_HIS20150421].[dbo].[H_Project_Data]

select * from [bh20180420].[dbo].[ItemInfoID]

select * from [bh].[dbo].[Department]
select * from [bh20180414].[dbo].[Department]
select * from [bh20180420].[dbo].[Department]

select * from [bh].[dbo].[ItemForConsultingCategory]
select * from [bh20180414].[dbo].[ItemForConsultingCategory]
select * from [bh20180414].[dbo].[ItemForConsultingCategoryID]
select * from [bh20180414].[dbo].[ItemForConsultingCategory]
select * from [bh20180420].[dbo].[ItemForConsultingCategory]

select * from [bh].[dbo].[Employee]
select * from [bh20180414].[dbo].[Employee]
select * from [bh20180414].[dbo].[EmployeeID]
select * from [bh].[dbo].[Employee]
select * from [bh20180420].[dbo].[Employee]
select * from [bh20180420].[dbo].[EmployeeID]

select * from [bh].[dbo].[PositionInfo]
select * from [bh20180414].[dbo].[PositionInfo]
select * from [bh20180414].[dbo].[PositionInfo]
select * from [bh20180420].[dbo].[PositionInfo]
select * from [bh].[dbo].[PositionInfo]

--delete from [bh20180420].[dbo].[Employee]
--delete from [bh20180420].[dbo].[EmployeeID]

--DBCC CHECKIDENT ('EmployeeID', RESEED, 1)
--DBCC CHECKIDENT ('Employee', RESEED, 1)

select * from H_Employee

update [bh20180420].[dbo].[Employee] set PositionID = (select [Employee].PositionID
from [Employee]
where [bh20180420].[dbo].[Employee].ID = [Employee].ID
)

---update [bh20180420].[dbo].[PositionInfo] set DepartmentID = (select [PositionInfo].DepartmentID
-- from [PositionInfo]
--- where [bh20180420].[dbo].[PositionInfo].ID = [PositionInfo].ID
--)

select * from [bh].[dbo].[CustomerRegion]
select * from [bh20180420].[dbo].[CustomerRegion]

select * from [bh].[dbo].[CustomerUnderstandWay]
select * from [bh20180420].[dbo].[CustomerUnderstandWay]

select * from [bh].[dbo].[PresentationOutsideCategory]
select * from [bh20180420].[dbo].[PresentationOutsideCategory]

select * from [bh].[dbo].[PresentationOutside]
select * from [bh20180420].[dbo].[PresentationOutside]

select count(*) from [bh].[dbo].[CustomerBaseInfo]
select count(*) from [bh20180420].[dbo].[CustomerBaseInfo]

select count(*) from [bh].[dbo].[CustomerBaseInfo]
select count(*) from [bh20180420].[dbo].[CustomerBaseInfoID]

select * from [bh].[dbo].[CustomerBaseInfo]
select * from [bh20180420].[dbo].[CustomerBaseInfo]

select* from [bh].[dbo].RankInfo

select* from [bh].[dbo].NewRankInfo

 

 

select * from Employee
select * from Department

select * from EmployeeID

Delete from Employee
DBCC CHECKIDENT (‘Employee’, RESEED, 1)

Delete from Department
select * from PrePaymentRecharge

--delete from PrePaymentRecharge

select * from role

update Employee set Password='12345' where ID<>'00001'

select * from CustomerConsumptionDetails