表分割一例
/*
Script created by SQL Prompt version 5.1.8.2 from Red Gate Software Ltd at 2011-10-5 12:26:13
Run this script on ycmis to split the table.
Please back up your database before running this script.
*/
-- Summary for the table split:
-- Action:
-- Create table [dbo].[费用表]
-- Create primary key [pk_费用表] on [dbo].[费用表]
-- Alter table [dbo].[费用表]
-- Add foreign key to [dbo].[费用表]
-- Add foreign key to [dbo].[费用表]
--
-- No warnings
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[费用表]'
GO
CREATE TABLE [dbo].[费用表]
(
[费用ID] [decimal] (12, 0) NOT NULL,
[金额] [decimal] (13, 3) NULL,
[滞纳金日期] [datetime] NULL,
[费用类型] [char] (2) COLLATE Chinese_PRC_CI_AS NULL,
[生成工号] [int] NULL,
[滞纳金] [decimal] (10, 2) NULL,
[销帐日期] [datetime] NULL,
[销帐工号] [int] NULL,
[实收方式] [char] (2) COLLATE Chinese_PRC_CI_AS NULL,
[户名] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL,
[滞纳金结算日] [datetime] NULL,
[销帐时间] [datetime] NULL,
[备注] [varchar] (254) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[费用表]([费用ID], [金额], [滞纳金日期], [费用类型], [生成工号], [滞纳金], [销帐日期], [销帐工号], [实收方式], [户名], [滞纳金结算日], [销帐时间], [备注])
SELECT DISTINCT [费用ID], [金额], [滞纳金日期], [费用类型], [生成工号], [滞纳金], [销帐日期], [销帐工号], [实收方式], [户名], [滞纳金结算日], [销帐时间], [备注]
FROM [dbo].[费用表]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [pk_费用表] on [dbo].[费用表]'
GO
ALTER TABLE [dbo].[费用表] ADD CONSTRAINT [pk_费用表] PRIMARY KEY CLUSTERED ([费用ID]) ON [PRIMARY]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[费用表]'
GO
ALTER TABLE [dbo].[费用表] DROP
COLUMN [费用类型],
COLUMN [金额],
COLUMN [生成工号],
COLUMN [滞纳金日期],
COLUMN [滞纳金],
COLUMN [销帐工号],
COLUMN [销帐日期],
COLUMN [实收方式],
COLUMN [户名],
COLUMN [备注],
COLUMN [滞纳金结算日],
COLUMN [销帐时间]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[费用表]'
GO
ALTER TABLE [dbo].[费用表] ADD
CONSTRAINT [fk_费用表_费用表] FOREIGN KEY ([费用ID]) REFERENCES [dbo].[费用表] ([费用ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[费用表]'
GO
ALTER TABLE [dbo].[费用表] ADD
CONSTRAINT [fk_费用表_费用表_费用表] FOREIGN KEY ([费用ID]) REFERENCES [dbo].[费用表] ([费用ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
SELECT TOP 100 a.* FROM [费用表] a JOIN [费用表] b ON a.费用id=b.费用ID
ORDER BY a.费用id DESC
SELECT TOP 100 b.* FROM [费用表] a JOIN [费用表] b ON a.费用id=b.费用ID
ORDER BY a.费用id DESC
原表
CREATE TABLE [dbo].[费用表]
(
[费用ID] [decimal] (12, 0) NOT NULL,
[费用类型] [char] (2) COLLATE Chinese_PRC_CI_AS NULL,
[户号] [int] NULL,
[金额] [decimal] (13, 3) NULL,
[生成工号] [int] NULL,
[生成日期] [datetime] NULL,
[滞纳金日期] [datetime] NULL,
[滞纳金] [decimal] (10, 2) NULL,
[销帐工号] [int] NULL,
[销帐日期] [datetime] NULL,
[实收方式] [char] (2) COLLATE Chinese_PRC_CI_AS NULL,
[供水公司] [char] (3) COLLATE Chinese_PRC_CI_AS NULL,
[户名] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL,
[地址] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL,
[表位号] [char] (13) COLLATE Chinese_PRC_CI_AS NULL,
[表身号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[口径] [int] NULL,
[收费方式] [char] (2) COLLATE Chinese_PRC_CI_AS NULL,
[帐户编号] [int] NULL,
[税号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL,
[打印发票] [bit] NOT NULL,
[标志] [char] (1) COLLATE Chinese_PRC_CI_AS NULL,
[备注] [varchar] (254) COLLATE Chinese_PRC_CI_AS NULL,
[滞纳金结算日] [datetime] NULL,
[处理类型] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[处理费用ID] [decimal] (12, 0) NULL,
[处理标志] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL,
[销帐时间] [datetime] NULL
) ON [PRIMARY]
GO
