T-SQL INSERT Help

What I am trying to accomplish: Marketing uses a sql server 2000 database named Purchase Orders. This is to document employee purchases within two tables going back only 1 year. Marketing wants to have the incremental key of the purchase orders to start again at 1 for the new year. As far as my knowledge goes, I'm assuming this is not possible to just delete the records to have the incremental key start again at one (table needs to be recreated).
How I tried to accomplish this:
1. I used the Generate SQL Script "Create To" option on the original table to CREATE a historical table so that we may go back for last years records
2. I tried to INSERT the values from the original table into the new table
3. I tried to DROP the original table (so that I may recreate it again with the correct indexes, keys, ect. )
4. I tried to again CREATE the original table so that marketing could again start with an incremental key of 1
Problem: I tried doing this all in one query, and didn't get any further than the first step (creating the two new tables). I didn't realize this until I tried to open the new tables and they faild to populate. Also, the tables I wanted droped were still there and populated. The query completed successfully, however it stated (0 row(s) affected)
Code:


CREATE TABLE [dbo].[tblLYPurchaseOrderProductsTST] (
      [PONum] [int] NOT NULL ,
      [ProductID] [int] IDENTITY (1, 1) NOT NULL ,
      [ProductDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [ProductQuantity] [int] NOT NULL ,
      [ProductPrice] [decimal](18, 2) NOT NULL ,
      [Materials] [decimal](18, 2) NOT NULL ,
      [Labor] [decimal](18, 2) NOT NULL ,
      [OrderDate] [smalldatetime] NULL ,
      [EmployeeNum] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblLYPurchaseOrdersTST] (
      [PONum] [int] IDENTITY (1, 1) NOT NULL ,
      [SupplierNum] [int] NOT NULL ,
      [WarehouseNum] [int] NOT NULL ,
      [FreightCharge] [decimal](18, 2) NOT NULL ,
      [MiscSurchargePercent] [decimal](18, 2) NOT NULL ,
      [MiscSurchargeCurrency] [decimal](18, 2) NOT NULL ,
      [DiscountPercent] [decimal](18, 2) NOT NULL ,
      [DiscountCurrency] [decimal](18, 2) NOT NULL ,
      [FrequencyType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Comments] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [EntryDate] [datetime] NULL ,
      [EmployeeNum] [int] NOT NULL ,
      [ArrivalDate] [datetime] NULL ,
      [DeptID] [int] NULL ,
      [SalesTax] [decimal](18, 2) NOT NULL ,
      [ShipToName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShipToAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShipToAddress2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShipToCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShipToState] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShipToZip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLYPurchaseOrderProductsTST] WITH NOCHECK ADD
      CONSTRAINT [PK_tblLYPurchaseOrderProductsTST] PRIMARY KEY  CLUSTERED
      (
            [ProductID]
      ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLYPurchaseOrdersTST] WITH NOCHECK ADD
      CONSTRAINT [PK_tblLYPurchaseOrdersTST] PRIMARY KEY  CLUSTERED
      (
            [PONum]
      ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLYPurchaseOrderProductsTST] ADD
      CONSTRAINT [DF_tblLYPurchaseOrderProductsTST_ProdQuantity] DEFAULT (0) FOR [ProductQuantity],
      CONSTRAINT [DF_tblLYPurchaseOrderProductsTST_ProdPrice] DEFAULT (0) FOR [ProductPrice],
      CONSTRAINT [DF_tblLYPurchaseOrderProductsTST_Materials] DEFAULT (0) FOR [Materials],
      CONSTRAINT [DF_tblLYPurchaseOrderProductsTST_Labor] DEFAULT (0) FOR [Labor],
      CONSTRAINT [DF_tblLYPurchaseOrderProductsTST_OrderDate] DEFAULT (getdate()) FOR [OrderDate],
      CONSTRAINT [DF_tblLYPurchaseOrderProductsTST_EmployeeNum] DEFAULT (0) FOR [EmployeeNum]
GO

 CREATE  INDEX [IX_tblLYPurchaseOrderProductsTST] ON [dbo].[tblLYPurchaseOrderProductsTST]([PONum], [ProductID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLYPurchaseOrdersTST] ADD
      CONSTRAINT [DF_tblLYPurchaseOrdersTST_SupNum] DEFAULT (0) FOR [SupplierNum],
      CONSTRAINT [DF_tblLYPurchaseOrdersTST_WarehouseNum] DEFAULT (0) FOR [WarehouseNum],
      CONSTRAINT [DF_tblLYPurchaseOrdersTST_FreightCharge] DEFAULT (0) FOR [FreightCharge],
      CONSTRAINT [DF_tblLYPurchaseOrdersTST_FuelSurcharge] DEFAULT (0) FOR [MiscSurchargePercent],
      CONSTRAINT [DF_tblLYPurchaseOrdersTST_MiscSurchargeCurrently] DEFAULT (0) FOR [MiscSurchargeCurrency],
      CONSTRAINT [DF_tblLYPurchaseOrdersTST_SalesTax] DEFAULT (0) FOR [DiscountPercent],
      CONSTRAINT [DF_tblLYPurchaseOrdersTST_DiscountCurrency] DEFAULT (0) FOR [DiscountCurrency],
      CONSTRAINT [DF_tblLYPurchaseOrdersTST_EntryDate] DEFAULT (getdate()) FOR [EntryDate],
      CONSTRAINT [DF_tblLYPurchaseOrdersTST_EmployeeNum] DEFAULT (0) FOR [EmployeeNum],
      CONSTRAINT [DF_tblLYPurchaseOrdersTST_SalesTax_1] DEFAULT (0) FOR [SalesTax]
GO

ALTER TABLE [dbo].[tblLYPurchaseOrderProductsTST] ADD
      CONSTRAINT [FK_tblLYPurchaseOrderProductsTST_tblLYPurchaseOrdersTST] FOREIGN KEY
      (
            [PONum]
      ) REFERENCES [dbo].[tblLYPurchaseOrdersTST] (
            [PONum]
      ) ON DELETE CASCADE  ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tblLYPurchaseOrdersTST] ADD
      CONSTRAINT [FK_tblLYPurchaseOrdersTST_lkpFrequency] FOREIGN KEY
      (
            [FrequencyType]
      ) REFERENCES [dbo].[lkpFrequency] (
            [FrequencyType]
      ),
      CONSTRAINT [FK_tblLYPurchaseOrdersTST_tblSuppliers] FOREIGN KEY
      (
            [SupplierNum]
      ) REFERENCES [dbo].[tblSuppliers] (
            [SupplierNum]
      )
GO

SET IDENTITY_INSERT [tblLYPurchaseOrderProductsTST] ON
INSERT INTO tblLYPurchaseOrderProductsTST ([PONum] ,
      [ProductID] ,
      [ProductDesc] ,
      [ProductQuantity] ,
      [ProductPrice] ,
      [Materials] ,
      [Labor] ,
      [OrderDate] ,
      [EmployeeNum])

(SELECT * FROM tblPurchaseOrderProducts)

GO

SET IDENTITY_INSERT [tblLYPurchaseOrderProductsTST] OFF

GO

SET IDENTITY_INSERT [tblLYPurchaseOrdersTST] ON
INSERT INTO tblLYPurchaseOrdersTST ([PONum] ,
      [SupplierNum] ,
      [WarehouseNum] ,
      [FreightCharge] ,
      [MiscSurchargePercent] ,
      [MiscSurchargeCurrency] ,
      [DiscountPercent] ,
      [DiscountCurrency] ,
      [FrequencyType] ,
      [Comments] ,
      [EntryDate] ,
      [EmployeeNum] ,
      [ArrivalDate] ,
      [DeptID] ,
      [SalesTax] ,
      [ShipToName] ,
      [ShipToAddress] ,
      [ShipToAddress2] ,
      [ShipToCity] ,
      [ShipToState] ,
      [ShipToZip])

(SELECT * FROM tblPurchaseOrders)

GO

SET IDENTITY_INSERT [tblLYPurchaseOrdersTST] OFF

GO

DROP TABLE tblPurchaseOrderProducts

GO

DROP TABLE tblPurchaseOrders

GO

CREATE TABLE [dbo].[tblPurchaseOrderProducts] (
      [PONum] [int] NOT NULL ,
      [ProductID] [int] IDENTITY (1, 1) NOT NULL ,
      [ProductDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [ProductQuantity] [int] NOT NULL ,
      [ProductPrice] [decimal](18, 2) NOT NULL ,
      [Materials] [decimal](18, 2) NOT NULL ,
      [Labor] [decimal](18, 2) NOT NULL ,
      [OrderDate] [smalldatetime] NULL ,
      [EmployeeNum] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPurchaseOrders] (
      [PONum] [int] IDENTITY (1, 1) NOT NULL ,
      [SupplierNum] [int] NOT NULL ,
      [WarehouseNum] [int] NOT NULL ,
      [FreightCharge] [decimal](18, 2) NOT NULL ,
      [MiscSurchargePercent] [decimal](18, 2) NOT NULL ,
      [MiscSurchargeCurrency] [decimal](18, 2) NOT NULL ,
      [DiscountPercent] [decimal](18, 2) NOT NULL ,
      [DiscountCurrency] [decimal](18, 2) NOT NULL ,
      [FrequencyType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Comments] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [EntryDate] [datetime] NULL ,
      [EmployeeNum] [int] NOT NULL ,
      [ArrivalDate] [datetime] NULL ,
      [DeptID] [int] NULL ,
      [SalesTax] [decimal](18, 2) NOT NULL ,
      [ShipToName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShipToAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShipToAddress2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShipToCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShipToState] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ShipToZip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPurchaseOrderProducts] WITH NOCHECK ADD
      CONSTRAINT [PK_tblPurchaseOrderProducts] PRIMARY KEY  CLUSTERED
      (
            [ProductID]
      ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPurchaseOrders] WITH NOCHECK ADD
      CONSTRAINT [PK_tblPurchaseOrders] PRIMARY KEY  CLUSTERED
      (
            [PONum]
      ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPurchaseOrderProducts] ADD
      CONSTRAINT [DF_tblPurchaseOrderProducts_ProdQuantity] DEFAULT (0) FOR [ProductQuantity],
      CONSTRAINT [DF_tblPurchaseOrderProducts_ProdPrice] DEFAULT (0) FOR [ProductPrice],
      CONSTRAINT [DF_tblPurchaseOrderProducts_Materials] DEFAULT (0) FOR [Materials],
      CONSTRAINT [DF_tblPurchaseOrderProducts_Labor] DEFAULT (0) FOR [Labor],
      CONSTRAINT [DF_tblPurchaseOrderProducts_OrderDate] DEFAULT (getdate()) FOR [OrderDate],
      CONSTRAINT [DF_tblPurchaseOrderProducts_EmployeeNum] DEFAULT (0) FOR [EmployeeNum]
GO

 CREATE  INDEX [IX_tblPurchaseOrderProducts] ON [dbo].[tblPurchaseOrderProducts]([PONum], [ProductID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPurchaseOrders] ADD
      CONSTRAINT [DF_tblPurchaseOrders_SupNum] DEFAULT (0) FOR [SupplierNum],
      CONSTRAINT [DF_tblPurchaseOrders_WarehouseNum] DEFAULT (0) FOR [WarehouseNum],
      CONSTRAINT [DF_tblPurchaseOrders_FreightCharge] DEFAULT (0) FOR [FreightCharge],
      CONSTRAINT [DF_tblPurchaseOrders_FuelSurcharge] DEFAULT (0) FOR [MiscSurchargePercent],
      CONSTRAINT [DF_tblPurchaseOrders_MiscSurchargeCurrently] DEFAULT (0) FOR [MiscSurchargeCurrency],
      CONSTRAINT [DF_tblPurchaseOrders_SalesTax] DEFAULT (0) FOR [DiscountPercent],
      CONSTRAINT [DF_tblPurchaseOrders_DiscountCurrency] DEFAULT (0) FOR [DiscountCurrency],
      CONSTRAINT [DF_tblPurchaseOrders_EntryDate] DEFAULT (getdate()) FOR [EntryDate],
      CONSTRAINT [DF_tblPurchaseOrders_EmployeeNum] DEFAULT (0) FOR [EmployeeNum],
      CONSTRAINT [DF_tblPurchaseOrders_SalesTax_1] DEFAULT (0) FOR [SalesTax]
GO

ALTER TABLE [dbo].[tblPurchaseOrderProducts] ADD
      CONSTRAINT [FK_tblPurchaseOrderProducts_tblPurchaseOrders] FOREIGN KEY
      (
            [PONum]
      ) REFERENCES [dbo].[tblPurchaseOrders] (
            [PONum]
      ) ON DELETE CASCADE  ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tblPurchaseOrders] ADD
      CONSTRAINT [FK_tblPurchaseOrders_lkpFrequency] FOREIGN KEY
      (
            [FrequencyType]
      ) REFERENCES [dbo].[lkpFrequency] (
            [FrequencyType]
      ),
      CONSTRAINT [FK_tblPurchaseOrders_tblSuppliers] FOREIGN KEY
      (
            [SupplierNum]
      ) REFERENCES [dbo].[tblSuppliers] (
            [SupplierNum]
      )
GO

Any help would be appreciated. I realize it might be something pretty simple, however I'm still learning.
Thanks!
Traci

Start Free Trial