执行之前，务必所有前台交接班，并且后台断掉进程
--创建新表暂存原始数据
if not exists(select 1 from sysobjects where name='xjl_Details_123' and type='U')
begin
CREATE TABLE [xjl_Details_123](
[FlowNo] [varchar](50) not null,
[TicketNo] [int],
[SubNo] [int] not null,
[TradeTime] [datetime],
[DeviceID] [varchar](10),
[ScaleNo] [varchar] ( 10 ) ,
[LocalStoreID] [varchar](20),
[CWANID] [varchar](10),
[IPPoint] [varchar](20) ,
[PluNo] [varchar](20),
[PluName] [varchar](70),
[ArticleNo] [varchar](20) ,
[Weight] [float],
[Quantity] [float],
[UnitPrice] [float],
[OldPrice] [float] ,
[TotalPrice] [float] ,
[OperatorNo] [varchar] ( 10 ) ,
[SellType] [int],
[Payment] [int],
[PluType] [int],
[StoreType] [int] ,
[SubShopNo] [varchar](30) ,
[PrintTime] [datetime] ,
[Discount] [int] ,
[PriceFlag] [varchar](10),
[TicketFlowNo] [varchar](50),
[ShiftNo] [varchar](20),
[RefluxState] [int] ,
[SpriceFLowno] varchar(50),
[Num1] numeric(16,4),
[Num2] numeric(16,4),
[Num3] numeric(16,4),
[Other1] varchar(20),
[Other2] varchar(50),
[Other3] varchar(50)
)
end
if not exists(select 1 from sysobjects where name='xjl_NoCashDetails_123' and type='U')
begin
CREATE TABLE [xjl_NoCashDetails_123](
[FlowNo] [varchar](50) not null,
[TicketNo] [int],
[SubNo] [int] not null,
[TradeTime] [datetime],
[DeviceID] [varchar](10) ,
[ScaleNo] [varchar] ( 10 ) ,
[LocalStoreID] [varchar](20) ,
[CWANID] [varchar](10) ,
[IPPoint] [varchar](20),
[OperatorNo] [varchar] (10) ,
[SellType] [int] ,
[Payment] [int] ,
[PluType] [int],
[Discount] [int],
[PayTypeNo] [int],
[PayMoney] [float],
[SeqNo] [int],
[CardNo] [varchar](30),
[TicketFlowNo] [varchar](50),
[VoucherNo] [varchar](50),
[ShiftNo] [varchar](20),
[RefluxState] [int],
[DiscountSum] numeric(16,4),
[Num1] numeric(16,4),
[Num2] numeric(16,4),
[Num3] numeric(16,4),
[Other1] varchar(20),
[Other2] varchar(50),
[Other3] varchar(50)
)
END
IF NOT EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'xjl_Total_123' AND type = 'U' ) BEGIN
CREATE TABLE [xjl_Total_123] (
[FlowNo] [varchar] ( 50 ) not null,
[TicketNo] [int],
[SubNo] [int] not null,
[TradeTime] [datetime] ,
[DeviceID] [varchar] ( 10 ),
[ScaleNo] [varchar] ( 20 ) ,
[LocalStoreID] [varchar] ( 20 ),
[CWANID] [varchar] ( 20 ) ,
[IPPoint] [varchar] ( 20 ),
[OperatorNo] [varchar](10),
[SellType] [int] ,
[RealSum] [float] ,
[CardDiscountSum] [float] ,
[Payment] [int] ,
[Discount] [int] ,
[CardNo] [varchar] ( 30 ) ,
[VoucherNo] [varchar] ( 50 ),
[ShiftNo] [varchar] ( 20 ) ,
[simp_order] [varchar] ( 50 ),
[RefluxState] [int] ,
payment_info text null,
[num1] [numeric](16, 4) NULL,
[num2] [numeric](16, 4) NULL,
[num3] [numeric](16, 4) NULL,
[other1] [varchar](20) NULL,
[other2] [varchar](50) NULL,
[other3] [varchar](50) NULL
)
END
--把原始数据转移新表
insert into xjl_Details_123 select * from xjl_Details
insert into xjl_NoCashDetails_123 select * from xjl_NoCashDetails
insert into xjl_Total_123 select * from xjl_Total
--对比原始表和暂存表数据条数是否一致 确认一致暂存表存在数据在进行下一步
select count(*) from xjl_Details
select count(*) from xjl_Details_123
select count(*) from xjl_NoCashDetails
select count(*) from xjl_NoCashDetails_123
select count(*) from xjl_Total
select count(*) from xjl_Total_123
--删除旧表
drop table xjl_Details
drop table xjl_NoCashDetails
drop table xjl_Total
--创建新表
if not exists(select 1 from sysobjects where name='xjl_Details' and type='U')
begin
CREATE TABLE [xjl_Details](
[FlowNo] [varchar](40) not null,
[TicketNo] [int],
[SubNo] [int] not null,
[TradeTime] [datetime],
[DeviceID] [varchar](10),
[ScaleNo] [varchar] ( 10 ) ,
[LocalStoreID] [varchar](20),
[CWANID] [varchar](10),
[IPPoint] [varchar](20) ,
[PluNo] [varchar](20),
[PluName] [varchar](70),
[ArticleNo] [varchar](20) ,
[Weight] [float],
[Quantity] [float],
[UnitPrice] [float],
[OldPrice] [float] ,
[TotalPrice] [float] ,
[OperatorNo] [varchar] ( 10 ) ,
[SellType] [int],
[Payment] [int],
[PluType] [int],
[StoreType] [int] ,
[SubShopNo] [varchar](30) ,
[PrintTime] [datetime] ,
[Discount] [int] ,
[PriceFlag] [varchar](10),
[TicketFlowNo] [varchar](40),
[ShiftNo] [varchar](20),
[RefluxState] [int] ,
[SpriceFLowno] varchar(25),
[Num1] numeric(16,4),
[Num2] numeric(16,4),
[Num3] numeric(16,4),
[Other1] varchar(20),
[Other2] varchar(50),
[Other3] varchar(50)
)
ALTER TABLE xjl_Details WITH NOCHECK ADD
CONSTRAINT [PK_xjl_Details_xz] PRIMARY KEY NONCLUSTERED
(
FlowNO,
SubNo
)
create
clustered
index
xjl_Details_index
on xjl_Details
(TradeTime desc)
create
nonclustered
index
xjl_Details_index2
on xjl_Details
(TicketFlowNo desc)
end
if not exists(select 1 from sysobjects where name='xjl_NoCashDetails' and type='U')
begin
CREATE TABLE [xjl_NoCashDetails](
[FlowNo] [varchar](40) not null,
[TicketNo] [int],
[SubNo] [int] not null,
[TradeTime] [datetime],
[DeviceID] [varchar](10) ,
[ScaleNo] [varchar] ( 10 ) ,
[LocalStoreID] [varchar](20) ,
[CWANID] [varchar](10) ,
[IPPoint] [varchar](20),
[OperatorNo] [varchar] (10) ,
[SellType] [int] ,
[Payment] [int] ,
[PluType] [int],
[Discount] [int],
[PayTypeNo] [int],
[PayMoney] [float],
[SeqNo] [int],
[CardNo] [varchar](30),
[TicketFlowNo] [varchar](40),
[VoucherNo] [varchar](40),
[ShiftNo] [varchar](10),
[RefluxState] [int],
[DiscountSum] numeric(16,4),
[Num1] numeric(16,4),
[Num2] numeric(16,4),
[Num3] numeric(16,4),
[Other1] varchar(20),
[Other2] varchar(50),
[Other3] varchar(50)
)
ALTER TABLE xjl_NoCashDetails WITH NOCHECK ADD
CONSTRAINT [PK_xjl_NoCashDetails_xz] PRIMARY KEY NONCLUSTERED
(
FlowNO,
SubNo
)
create
clustered
index
xjl_NoCashDetails_index
on xjl_NoCashDetails
(TradeTime desc)
END
IF NOT EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'xjl_Total' AND type = 'U' ) BEGIN
CREATE TABLE [xjl_Total] (
[FlowNo] [varchar] ( 40 ) not null,
[TicketNo] [int],
[SubNo] [int] not null,
[TradeTime] [datetime] ,
[DeviceID] [varchar] ( 10 ),
[ScaleNo] [varchar] ( 20 ) ,
[LocalStoreID] [varchar] ( 20 ),
[CWANID] [varchar] ( 20 ) ,
[IPPoint] [varchar] ( 20 ),
[OperatorNo] [varchar](10),
[SellType] [int] ,
[RealSum] [float] ,
[CardDiscountSum] [float] ,
[Payment] [int] ,
[Discount] [int] ,
[CardNo] [varchar] ( 30 ) ,
[VoucherNo] [varchar] ( 40 ),
[ShiftNo] [varchar] ( 10 ) ,
[simp_order] [varchar] ( 40 ),
[RefluxState] [int] ,
payment_info text null,
[num1] [numeric](16, 4) NULL,
[num2] [numeric](16, 4) NULL,
[num3] [numeric](16, 4) NULL,
[other1] [varchar](20) NULL,
[other2] [varchar](50) NULL,
[other3] [varchar](50) NULL
)
ALTER TABLE xjl_Total WITH NOCHECK ADD
CONSTRAINT [PK_xjl_Total_xz] PRIMARY KEY NONCLUSTERED
(
FlowNO,
SubNo
)
create
clustered
index
xjl_Total_index
on xjl_Total
(TradeTime desc)
create
nonclustered
index
xjl_Total_index_VoucherNo
on xjl_Total
(VoucherNo)
END
--创建完成新表开始转移暂存表数据
insert into xjl_Details select * from xjl_Details_123
insert into xjl_NoCashDetails select * from xjl_NoCashDetails_123
insert into xjl_Total select * from xjl_Total_123
--对比原始表和暂存表数据条数是否一致 确认一致暂存表存在数据在进行下一步
select count(*) from xjl_Details
select count(*) from xjl_Details_123
select count(*) from xjl_NoCashDetails
select count(*) from xjl_NoCashDetails_123
select count(*) from xjl_Total
select count(*) from xjl_Total_123
--删除暂存表
drop table xjl_Details_123
drop table xjl_NoCashDetails_123
drop table xjl_Total_123
--必须先把3张表的触发器创建好了再打开服务
--1.可以通过注销重新拉取线上更新
--2.可以换取测试节点更新触发器
--3.直接使用sql创建
--必须创建好触发器才可以开服务，否则会导致流水写不回去原软件
--3张表触发器检测
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tr_xjl_details' AND type = 'TR')
select '已存在tr_xjl_details触发器！'
else
select '未存在tr_xjl_details触发器！'
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tr_xjl_NoCashDetails' AND type = 'TR')
select '已存在tr_xjl_NoCashDetails触发器！'
else
select '未存在tr_xjl_NoCashDetails触发器！'
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tr_xjl_details' AND type = 'TR')
select '已存在tr_xjl_Total触发器！'
else
select '未存在tr_xjl_details触发器！'
--收缩数据库
