I have a question here.
I have a Audit tables database. I want to archive the Audit Tables data to another new database in the same server and remove the data in the Actual database but continues with the table seed where it's the primary ID.
Example:
I have a AuditProducts Table with AuditID as BIGINT and auto increment value.
contains10,000 records in AuditTable Database.
I want to archive the tables to a new database: Audit200707
After archived, the AuditProducts Table in AuditTable database will remove all the records and continues with 10001 in the next record.
How can I done the whole process using T-SQL so that it can schedule and run for every month? BTW I am using SQL Server 2005 SP2.
Thanks and regards,
The following query might help you,
Code Snippet
Declare @.tablename as varchar(100);
Set @.tablename = 'AuditProduct' + Cast(Year(getdate()) as Varchar) + Substring(cast(100 + Month(getdate())-1 as varchar),2,2)
If NOT EXISTS(Select 1 From Sysobjects Where Name=@.tablename and type='U')
Begin
Exec('Select * into ' + @.tablename + ' From AuditProduct')
--If You want to rest the identity to 1
--Truncate Table AuditProduct
--If you want to keep the identity sequence
Delete From AuditProduct
End
|||I want to create a new database and the same table. this process I need to put it into script as well.
How do I Generate the Selected Table scripts using T-SQL?
thanks for the help once again.
|||OK the code mentioned by Manivannan.D.Sekaran works to create the table and record. But this doesn't copy the column information such as Default Value Or Binding and Description.
How to make it copy the column information as well?
Thanks a lot for your help.
regards,
|||I think you're going to want to create an SSIS package, where you can copy database objects and then execute the T-SQL to delete the contents from your table(s).|||
I managed to wrote out a T-SQL according to Manivannan.D.Sekaran suggectiion:
Code Snippet
USE DB1
DECLARE @.DBName AS VARCHAR(50)
DECLARE @.TableName AS VARCHAR(100)
DECLARE @.NextSeedNo BIGINT
SET @.DBName = 'AuditDB1' + CAST(YEAR(GETDATE()) AS VARCHAR) + SUBSTRING(CAST(100 + MONTH(GETDATE()) AS VARCHAR),2,2)
EXEC('CREATE DATABASE ' + @.DBName)
DECLARE csrTable CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT TABLE_NAME
FROM information_schema.tables (NOLOCK)
WHERE TABLE_TYPE <> 'VIEW'
AND TABLE_CATALOG = 'DB1'
AND (TABLE_NAME LIKE 'Audit%')
ORDER BY TABLE_NAME
OPEN csrTable
FETCH NEXT FROM csrTable INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC('SELECT * INTO [' + @.DBName + '].[dbo].[' + @.TableName + '] FROM '+ @.TableName + ' GO ')
SET @.NextSeedNo = IDENT_CURRENT(@.TableName) + 1
EXEC('TRUNCATE TABLE ' + @.TableName + ' GO ')
EXEC('dbcc checkident (' + @.TableName + ', reseed, ' + CAST(@.NextSeedNo AS VARCHAR(10))+ ') GO')
FETCH NEXT FROM csrTable into @.TableName
END
CLOSE csrTable
DEALLOCATE csrTable
This was successfully meet what I want but the new database tables doesn't include the column information such as Default Value and Description. How can I get this done as well?
Thanks for your help.
regards
