SQL语句-20180413

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

delete from ItemCategory;
delete from ItemCategoryID;

DBCC CHECKIDENT ('ItemCategory', RESEED, 1)
DBCC CHECKIDENT ('ItemCategoryID', RESEED, 1)

delete from ItemInfo;
delete from ItemInfoID;

DBCC CHECKIDENT ('ItemInfo', RESEED, 1)
DBCC CHECKIDENT ('ItemInfoID', RESEED, 1)

delete from ItemForConsultingCategory;
delete from ItemForConsultingCategoryID;

DBCC CHECKIDENT ('ItemForConsultingCategory', RESEED, 1)
DBCC CHECKIDENT ('ItemForConsultingCategoryID', RESEED, 1)

 

 

delete from [bh20180411].[dbo].[MemberCardInfo]
delete from [bh20180411].[dbo].[MemberCardHandle]
delete from [bh20180411].[dbo].[CustomerBaseInfo]
delete from [bh20180411].[dbo].[CustomerBaseInfoID]

 

select * into [bh20180411].[dbo].[ItemInfo] from [bh].[dbo].[ItemInfo]

insert into [bh20180411].[dbo].[ItemInfo] select * from [bh].[dbo].[ItemInfo]

insert into [bh20180411].[dbo].[Role] select * from [bh].[dbo].[Role]

insert into [bh20180411].[dbo].[PositionInfo] select * from [bh].[dbo].[PositionInfo]

//现在没数据

delete from EducationInfo;

//无效
DBCC CHECKIDENT ('EducationInfo', RESEED, 1)

//顾客来源地区

insert into [bh20180411].[dbo].[CustomerRegion] select * from [bh].[dbo].[CustomerRegion]

//来院途径

insert into [bh20180411].[dbo].[CustomerUnderstandWay] select * from [bh].[dbo].[CustomerUnderstandWay]

//地面来源路径

insert into [bh20180411].[dbo].[PresentationOutsideCategory](Name) select Name from [bh].[dbo].[PresentationOutsideCategory]

//默认topcategory是1 不影响

insert into [bh20180411].[dbo].[PresentationOutside](Name,CategoryID,Phone) select Name,1,Phone from [bh].[dbo].[PresentationOutside]

//会员级别表

insert into [bh20180411].[dbo].[RankInfo] select * from [bh].[dbo].[RankInfo]

select top(1000) * from h_tmk;

select * from h_tmk
where CardNO='13011409'

 

select * from [bh20180411].[dbo].[MemberCardInfo]
select * from [bh20180411].[dbo].[MemberCardHandle]
select * from [bh20180411].[dbo].[CustomerBaseInfo]
select * from [bh20180411].[dbo].[CustomerBaseInfoID]

delete from [bh20180411].[dbo].[MemberCardInfo]
delete from [bh20180411].[dbo].[MemberCardHandle]
delete from [bh20180411].[dbo].[CustomerBaseInfo]
delete from [bh20180411].[dbo].[CustomerBaseInfoID]

DBCC CHECKIDENT ('MemberCardInfo', RESEED, 1)
DBCC CHECKIDENT ('MemberCardHandle', RESEED, 1)
DBCC CHECKIDENT ('CustomerBaseInfo', RESEED, 1)
DBCC CHECKIDENT ('CustomerBaseInfoID', RESEED, 1)

 

select count(*) from [bh20180411].[dbo].[CustomerBaseInfo]

select count(*) from [bh20180411].[dbo].[AccountsOfCustomer]

select count(*) from CustomerBaseInfo

 

=====================================================

 

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

insert into [bh20180414].[dbo].[DD_Department] select * from [bh].[dbo].[DD_Department]

 

-- select * from [bh20180414].[dbo].[ItemCategoryID]
-- select * from [bh20180414].[dbo].[ItemCategory]

-- insert into [bh20180414].[dbo].[DD_Department] select * from [bh].[dbo].[DD_Department]

--select * from [bh20180414].[dbo].[DD_Department]
--select * from [bh20180414].[dbo].[Department]
-- select * from [bh20180414].[dbo].[Role]
--select * from [bh20180414].[dbo].[Employee]

-- insert into [bh20180414].[dbo].[IncomeCategory] select * from [bh].[dbo].[IncomeCategory]
--insert into [bh20180414].[dbo].[Role] select * from [bh].[dbo].[Role]
--insert into [bh20180414].[dbo].[PositionInfo] select * from [bh].[dbo].[PositionInfo]
--insert into [bh20180414].[dbo].[CustomerUnderstandWay] select * from [bh].[dbo].[CustomerUnderstandWay]
--insert into [bh20180414].[dbo].[PresentationOutsideCategory](Name) select Name from [bh].[dbo].[PresentationOutsideCategory]
--insert into [bh20180414].[dbo].[PresentationOutside](Name,CategoryID,Phone) select Name,1,Phone from [bh].[dbo].[PresentationOutside]
--insert into [bh20180414].[dbo].[NewRankInfo] select * from [bh].[dbo].[NewRankInfo]
--insert into [bh20180414].[dbo].[PayModeInfo] select * from [bh].[dbo].[PayModeInfo]
--[PayModeInfo]
--select * from [bh20180411].[dbo].[EducationInfo]
--select * from [bh20180414].[dbo].[CustomerUnderstandWay]
--select * from [bh20180414].[dbo].[PresentationOutside]
--select * from [bh20180414].[dbo].[PresentationOutsideCategory]
--select * from [bh20180411].[dbo].[EducationInfo]

--insert into [bh20180414].[dbo].[CustomerRegion] select * from [bh].[dbo].[CustomerRegion]

--select * from [bh20180414].[dbo].[NewRankInfo]
--select count(*) from [bh20180414].[dbo].[CustomerBaseInfo]

--select count(*) from [bh20180411].[dbo].[CustomerBaseInfo] where

--select count(*) from [bh20180414].[dbo].[CustomerBaseInfoID]-- where Original='XC170116018'

--select * from [bh20180414].[dbo].[CustomerBaseInfoID]-- where Original='XC170116018'
--49412

---select * from [YUN_HIS2015092016031703].[dbo].[H_PayYS] where sCID='17040105'
--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_TMK] where CardNO<>'' and FID like '%170401%';

--select * from [bh20180411].[dbo].[PayModeInfo]

--select * from [bh].[dbo].[PayModeInfo]

--select * from [YUN_HIS2015092016031703].[dbo].[H_PayYS] where sSort='退费' and sCID like '%1704%'

//顾客做过的项目信息

select b.bAccStau,a.* from H_FrontSK_Project a left join H_FrontSK b on a.FID=b.FID where b.SCID='17040809' order by a.dDT desc

 

=======================

-- select * from [bh20180414].[dbo].[ItemCategoryID]
-- select * from [bh20180414].[dbo].[ItemCategory]

-- insert into [bh20180414].[dbo].[DD_Department] select * from [bh].[dbo].[DD_Department]

--select * from [bh20180414].[dbo].[DD_Department]
--select * from [bh20180414].[dbo].[Department]
-- select * from [bh20180414].[dbo].[Role]
--select * from [bh20180414].[dbo].[Employee]

-- insert into [bh20180414].[dbo].[IncomeCategory] select * from [bh].[dbo].[IncomeCategory]
--insert into [bh20180414].[dbo].[Role] select * from [bh].[dbo].[Role]
--insert into [bh20180414].[dbo].[PositionInfo] select * from [bh].[dbo].[PositionInfo]
--insert into [bh20180414].[dbo].[CustomerUnderstandWay] select * from [bh].[dbo].[CustomerUnderstandWay]
--insert into [bh20180414].[dbo].[PresentationOutsideCategory](Name) select Name from [bh].[dbo].[PresentationOutsideCategory]
--insert into [bh20180414].[dbo].[PresentationOutside](Name,CategoryID,Phone) select Name,1,Phone from [bh].[dbo].[PresentationOutside]
--insert into [bh20180414].[dbo].[NewRankInfo] select * from [bh].[dbo].[NewRankInfo]
--insert into [bh20180414].[dbo].[PayModeInfo] select * from [bh].[dbo].[PayModeInfo]
--[PayModeInfo]
--select * from [bh20180411].[dbo].[EducationInfo]
--select * from [bh20180414].[dbo].[CustomerUnderstandWay]
--select * from [bh20180414].[dbo].[PresentationOutside]
--select * from [bh20180414].[dbo].[PresentationOutsideCategory]
--select * from [bh20180411].[dbo].[EducationInfo]

--insert into [bh20180414].[dbo].[CustomerRegion] select * from [bh].[dbo].[CustomerRegion]

--select * from [bh20180414].[dbo].[NewRankInfo]
--select count(*) from [bh20180414].[dbo].[CustomerBaseInfo]
--98599
--select count(*) from [bh20180411].[dbo].[CustomerBaseInfo] where

--select count(*) from [bh20180414].[dbo].[CustomerBaseInfoID]-- where Original='XC170116018'

--select * from [bh20180414].[dbo].[CustomerBaseInfoID]-- where Original='XC170116018'
--49412
--98612
---select * from [YUN_HIS2015092016031703].[dbo].[H_PayYS] where sCID='17040105'
--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_TMK] where CardNO<>'' and FID like '%170401%';

--select * from [bh20180411].[dbo].[PayModeInfo]

--select * from [bh].[dbo].[PayModeInfo]

--select * from [YUN_HIS2015092016031703].[dbo].[H_PayYS] where sSort='退费' and sCID like '%1704%'

--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_TMK] where DT>'2011-04-01 00:00:00' and DT<'2018-04-01 00:00:00'-
--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_TMK] where CARDNO<>''
--select count(*) from [bh20180411].[dbo].[CustomerBaseInfo] where RegionID='00015'

--update [bh20180411].[dbo].[CustomerBaseInfo] set RegionID='00029' where RegionID='00015'
--漏转化
--select top 100 * from [YUN_HIS2015092016031703].[dbo].[H_TMK] where FID not in (select Original from [bh20180411].[dbo].[CustomerBaseInfoID])

--select top 100 * from [YUN_HIS2015092016031703].[dbo].[H_TMK] where FID<>''

--select TMKEmployee from [bh20180414].[dbo].[CustomerBaseInfo]
--select * from [bh20180414].[dbo].[CustomerBaseInfoID] where Original='JZ170401001'
--select * from [bh20180414].[dbo].[CustomerBaseInfo] where ID in ('BMUX98CBSQ','3JOZ7GY9IJ')
--select count(*) from [bh20180414].[dbo].[CustomerBaseInfo]

--select TMKEmployee from [bh20180414].[dbo].[CustomerBaseInfo]

--delete from [bh20180414].[dbo].[MemberCardInfo]
--delete from [bh20180414].[dbo].[MemberCardHandle]
--delete from [bh20180414].[dbo].[CustomerBaseInfo]
--delete from [bh20180414].[dbo].[CustomerBaseInfoID]

--select * from [bh20180414].[dbo].[ReturnMoneyForPrePaymentInfo];

--delete from [bh20180414].[dbo].[ReturnMoneyForPrePaymentInfo]

--delete from [bh20180414].[dbo].[ReturnMoneyForPrePaymentDetails];
--delete from [bh20180414].[dbo].[PrePaymentRecharge]
--delete from [bh20180414].[dbo].[PayModeForPrePayment]

--truncate table [bh20180414].[dbo].[CustomerBaseInfo]

--alter table [bh20180414].[dbo].[ReturnMoneyForPrePaymentInfo] drop CONSTRAINT FK_ReturnMoneyForPrePaymentInfo_CustomerBaseInfo

--select * from [bh20180414].[dbo].[CustomerBaseInfoID]

--select * from [bh20180414].[dbo].[PositionInfo]
--select * from [bh20180414].[dbo].[Employee]

--delete from [bh20180414].[dbo].[Employee] where ID >'00001'
--delete from [bh20180414].[dbo].[EmployeeID]
--select * from [bh20180414].[dbo].[Employee]

--select FirstPhone from [bh20180414].[dbo].[CustomerBaseInfo] where FirstPhone like '%000%'
--select OtherInfo from [bh20180414].[dbo].[CustomerBaseInfo] where OtherInfo='JZ170411004'

--update [bh20180414].[dbo].[CustomerBaseInfo] set TMKEmployee=null
--select count(*) from [bh20180414].[dbo].[MemberCardHandle]
--select count(*) from [bh20180414].[dbo].[MemberCardInfo]
--select * from [bh20180414].[dbo].[MemberCardHandle] where MemberCardNum='17040111'

--select * from [bh20180414].[dbo].[PayModeForPrePayment]

--select RegisterDate from [bh20180414].[dbo].[CustomerBaseInfo] where RegisterDate>='2017-04-01 0:00:00' and RegisterDate<='2017-04-11 0:00:00'

--select * from [bh20180414].[dbo].[CustomerComeLog]

--select top 100 * from [YUN_HIS2015092016031703].[dbo].[H_TMK]

--select * from [bh20180414].[dbo].[CountSettingsForSubCategory]

select * from [bh20180414].[dbo].[ConsultingProjects]
select * from [bh20180414].[dbo].[ConsultingInfo]

select * from [bh].[dbo].[ConsultingProjects]
select * from [bh].[dbo].[ConsultingInfo]

exec sp_executesql N'INSERT INTO [dbo].[ConsultingInfo]([CustomerID], [ExcutionNumber], [Remark])
VALUES (@p0, @p1, @p2)', N'@p0 varchar(8000),@p1 varchar(8000),@p2 nvarchar(4000)', @p0 = 'BRKWONRJ4A', @p1 = 'HS2YUDW0VXK5', @p2 = NULL

exec sp_executesql N'INSERT INTO [dbo].[ConsultingProjects]([Booker], [ChatID], [ConsultingBy], [ConsultingContent], [ConsultingDate], [ConsumptionItention], [CustomerID], [ExcutionNumber], [ItemCategoryID], [Keyword], [Status], [Webaddress])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11)

SELECT CONVERT(BigInt,SCOPE_IDENTITY()) AS [value]'
, N'@p0 varchar(8000),@p1 varchar(8000),@p2 varchar(8000),@p3 text,@p4 datetime,@p5 nvarchar(4000),@p6 varchar(8000),@p7 varchar(8000),@p8 varchar(8000),@p9 nvarchar(4000),@p10 tinyint,@p11 nvarchar(4000)'
, @p0 = '00123',
@p1 = NULL,
@p2 = '00123',
@p3 = '眼部整形-15366651899',
@p4 = '2018-04-15 15:33:45.640',
@p5 = N'重点',
@p6 = 'BRKWONRJ4A',
@p7 = 'HS2YUDW0VXK5',
@p8 = '00002',
@p9 = NULL,
@p10 = 0,
@p11 = NULL

--select * from [bh20180414].[dbo].[GoodsCategory]
--select * from [bh20180414].[dbo].[GoodsInfo]

--select * from [bh20180414].[dbo].[GoodsFactoryInfo]
--select * from [bh].[dbo].[GoodsFactoryInfo]

--insert into [bh20180414].[dbo].[GoodsFactoryInfo] select * from [bh].[dbo].[GoodsFactoryInfo]
--insert into [bh20180414].[dbo].[MedicineFactoryInfo] select * from [bh].[dbo].[MedicineFactoryInfo]
--select * from [YUN_HIS2015092016031703].[dbo].[H_Drugs_index_Data]
--bZSJE,mHY,sCode,sStock,sJL
--select * from [bh].[dbo].[medicinecategory]
--select * from [bh].[dbo].[category]

--insert into [bh20180414].[dbo].[medicinecategory] select * from [bh].[dbo].[medicinecategory]

--select * from [bh20180414].[dbo].[medicineinfo];
--select * from [bh20180414].[dbo].[categoryid]

--select * from [bh20180414].[dbo].[ItemTypeInfo]
--select * from [bh].[dbo].[ItemTypeInfo]

--insert into [bh20180414].[dbo].[ItemTypeInfo] select * from [bh].[dbo].[ItemTypeInfo]

--select * from [bh20180414].[dbo].[ChargeForCertainDocInfo];

--select * from [bh].[dbo].[ReturnMoneyForCertainDoc];

--select * from [YUN_HIS2015092016031703].[dbo].[H_FrontSK_Project]
--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_QKACCOUNT]
--select * from [YUN_HIS2015092016031703].[dbo].[H_FrontSK_Paylst]

--select * from [YUN_HIS2015092016031703].[dbo].[H_QKACCOUNT]
--select * from [YUN_HIS2015092016031703].[dbo].[H_QKACCOUNT_Paylst] where sRemark='后期处理数据添加的'

--select * from [YUN_HIS2015092016031703].[dbo].[H_FrontSK_Paylst] where sRemark='后期处理数据添加的'

--select count(*) from [YUN_HIS2015092016031703].[dbo].[H_FrontSK_Project] where mJF>0

--select * from [bh].[dbo].[CardInfo]
--select * from [bh20180414].[dbo].[CardInfo]

-- select * from [bh20180414].[dbo].[ChargeForCertainDocDetails]
--select * from [bh].[dbo].[CardInfo]

--select * from CustomerBaseInfo

--select * from MemberCardHandle;

--alter table [bh20180414].[dbo].[ChargeForCertainDocDetails] drop CONSTRAINT FK_ChargeForCertainDocDetails_CardInfo

--alter table [bh20180414].[dbo].[ReturnMoneyForCertainDocDetails] drop CONSTRAINT FK_ReturnMoneyForCertainDocDetails_CardInfo

--select * from [bh].[dbo].[FinanceTotalIncomeTypeInfo]

--insert into [bh20180414].[dbo].[FinanceTotalIncomeTypeInfo] select * from [bh].[dbo].[FinanceTotalIncomeTypeInfo]

--insert into [bh20180414].[dbo].[ServerStatus] select * from [bh].[dbo].[ServerStatus]

--insert into [bh20180414].[dbo].[SystemParas] select * from [bh].[dbo].[SystemParas]
--insert into [bh20180414].[dbo].[ValidationUser] select * from [bh].[dbo].[ValidationUser]
--insert into [bh20180414].[dbo].[CheckUpdater] select * from [bh].[dbo].[CheckUpdater]
--insert into [bh20180414].[dbo].[CountSettings] select * from [bh].[dbo].[CountSettings]
--insert into [bh20180414].[dbo].[Role] select * from [bh].[dbo].[Role]
--insert into [bh20180414].[dbo].[PowerTemplateHistory](Template,Date) select Template,Date from [bh].[dbo].[PowerTemplateHistory]
select * from Employee

select * from [bh20180414].[dbo].[Employee]

select * from [bh].[dbo].[PowerTemplateHistory]
select * from [bh20180414].[dbo].[PowerTemplateHistory]

select * from PositionInfo

select * from ConsultingProjects