use [db]
--²éѯ;
select * from dindan where id like '2016%'
select * from dindanjd where id like '2016%'
select * from dindansp where id like '2016%'
select * from dindansp2 where id like '2016%'
select * from dindansp3 where id like '2016%'
select * from dindanbukuan where id like '2016%'
select * from dindanbukuan2 where id like '2016%'
select * from mywork where id like '2016%'
select * from burncdreg where id like '2016%'
select * from photoprint where id like '2016%'
select * from digitalwork where id like '2016%'
select * from client where id like '2016%'

--¸´ÖÆ;
select * into dindan_2015 from dindan where id like '2016%'
select * into dindanjd_2015 from dindanjd where id like '2016%'
select * into dindansp_2015 from dindansp where id like '2016%'
select * into dindansp2_2015 from dindansp2 where id like '2016%'
select * into dindansp3_2015 from dindansp3 where id like '2016%'
select * into dindanbukuan_2015 from dindanbukuan where id like '2016%'
select * into dindanbukuan2_2015 from dindanbukuan2 where id like '2016%'
select * into mywork_2015 from mywork where id like '2016%'
select * into burncdreg_2015 from burncdreg where id like '2016%'
select * into photoprint_2015 from photoprint where id like '2016%'
select * into digitalwork_2015 from digitalwork where id like '2016%'
select * into client_2015 from client where id like '2016%'

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dindan_2015]') AND name = N'id')
CREATE UNIQUE NONCLUSTERED INDEX [id] ON [dbo].[dindan_2015] 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dindanjd_2015] ADD CONSTRAINT PK_dindanjd PRIMARY KEY CLUSTERED 
(
	id,
	name
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dindansp_2015]') AND name = N'autoid')
CREATE UNIQUE NONCLUSTERED INDEX [autoid] ON [dbo].[dindansp_2015] 
(
	[autoid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dindansp_2015]') AND name = N'spid')
CREATE NONCLUSTERED INDEX [spid] ON [dbo].[dindansp_2015] 
(
	[spid] ASC,
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO


--ÐÞ¸Ä;
update dindan_2015 set id = replace(id, '2016', '2015'),time1 = replace(time1, '2016', '2015'),time2 = replace(time2, '2016', '2015'),time4 = replace(time4, '2016', '2015'),time5 = replace(time5, '2016', '2015'),time6 = replace(time6, '2016', '2015')
update dindanjd_2015 set id = replace(id, '2016', '2015'),date = replace(date, '2016', '2015'),bookingdate = replace(bookingdate, '2016', '2015')
update dindansp_2015 set id = replace(id, '2016', '2015'),date3 = replace(date3, '2016', '2015'),date4 = replace(date4, '2016', '2015')
update dindansp2_2015 set id = replace(id, '2016', '2015'),date = replace(date, '2016', '2015')
update dindansp3_2015 set id = replace(id, '2016', '2015')
update dindanbukuan_2015 set id = replace(id, '2016', '2015'),date = replace(date, '2016', '2015')
update dindanbukuan2_2015 set id = replace(id, '2016', '2015'),date = replace(date, '2016', '2015')
update client_2015 set id = replace(id, '2016', '2015')

--»Ø²å;
insert into dindan_2015 select * from dindan
insert into dindanjd_2015 select * from dindanjd
insert into dindansp_2015 select * from dindansp
insert into dindansp2_2015 select * from dindansp2
insert into dindansp3_2015 select * from dindansp3


update dindan set id = replace(id, '2016', '2015'),time1 = replace(time1, '2016', '2015'),time2 = replace(time2, '2016', '2015'),time4 = replace(time4, '2016', '2015'),time5 = replace(time5, '2016', '2015'),time6 = replace(time6, '2016', '2015')
update dindanjd set id = replace(id, '2016', '2015'),date = replace(date, '2016', '2015'),bookingdate = replace(bookingdate, '2016', '2015')
update dindansp set id = replace(id, '2016', '2015'),date3 = replace(date3, '2016', '2015'),date4 = replace(date4, '2016', '2015')
update dindansp2 set id = replace(id, '2016', '2015'),date = replace(date, '2016', '2015')
update dindansp3 set id = replace(id, '2016', '2015')
update dindanbukuan set id = replace(id, '2016', '2015'),date = replace(date, '2016', '2015')
update dindanbukuan2 set id = replace(id, '2016', '2015'),date = replace(date, '2016', '2015')
update client set id = replace(id, '2016', '2015')