表分割一例

/*

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

 

posted @ 2011-10-05 12:53  qanholas  阅读(298)  评论(0编辑  收藏  举报