locked
Inserting data into sql table RRS feed

  • Question

  • Every mth, I need to copy a set of 30 tbls into new tables, with the previous month name .

    For If I have a tableA, In June, I need to copy it into copy data into new table, but the newtblname shud be The previousmth ie

    MAY_2007_TableA...

    eVERYMTH, I NEED TO do this ie In July, I need to do a select into JUNE_2007_TABLEA....

    How can I automate this, so that the tblname everymonth, is named as previousmthyear+ tblname>?

     

    Friday, June 1, 2007 3:45 PM

Answers

  •  

    I'm not sure why you want the monthly data in individually named tables.

     

    Sounds like maybe a design needs to be reviewed.

     

    But to accomplish what you're looking for:

     

     

    Code Snippet

    create table dbo.tablea( id int identity(1,1), data nvarchar(20))

    insert into dbo.tablea values( N'item 1')

    insert into dbo.tablea values( N'item 2')

    insert into dbo.tablea values( N'item 3')

    insert into dbo.tablea values( N'item 4')

     

    DECLARE @pmth datetime,

    @sql nvarchar(200)

     

    SET @pmth = dateadd(m, -1, getdate())

    SET @sql = 'SELECT * INTO dbo.' + datename(month, @pmth) + '_' + convert(char(4), year(@pmth)) +

    '_TABLEA FROM TABLEA'

     

    EXEC sp_executesql @sql

     

     

     

    SELECT * FROM dbo.may_2007_tablea

    DROP TABLE dbo.tablea

    DROP TABLE dbo.may_2007_tablea

     

     

     


     

    Friday, June 1, 2007 4:22 PM

All replies

  • I am not sure if this is what you are looking for, but you can resolve your problem and much more using SMO(SQL Server Management objects) using c# or vb.net.



    Let me know if you are interested and I will post some code that I use myself for archiving old tables.

    Friday, June 1, 2007 4:11 PM
  • Tarana,

     

    This is a very unwieldy design, and only gets harder to work with over time.

     

    Sounds like a candidate for table partitioning.

    Friday, June 1, 2007 4:19 PM
  •  

    I'm not sure why you want the monthly data in individually named tables.

     

    Sounds like maybe a design needs to be reviewed.

     

    But to accomplish what you're looking for:

     

     

    Code Snippet

    create table dbo.tablea( id int identity(1,1), data nvarchar(20))

    insert into dbo.tablea values( N'item 1')

    insert into dbo.tablea values( N'item 2')

    insert into dbo.tablea values( N'item 3')

    insert into dbo.tablea values( N'item 4')

     

    DECLARE @pmth datetime,

    @sql nvarchar(200)

     

    SET @pmth = dateadd(m, -1, getdate())

    SET @sql = 'SELECT * INTO dbo.' + datename(month, @pmth) + '_' + convert(char(4), year(@pmth)) +

    '_TABLEA FROM TABLEA'

     

    EXEC sp_executesql @sql

     

     

     

    SELECT * FROM dbo.may_2007_tablea

    DROP TABLE dbo.tablea

    DROP TABLE dbo.may_2007_tablea

     

     

     


     

    Friday, June 1, 2007 4:22 PM
  • I am not sure why you have this approach.

    Its really bad design to keep the data (basically redundant data) in single database. I recommend to use the Indexed Views here. Its basically same approach as you create a separate table. The advantage is there is no latency between your Source table & De-normalized (or grouped) result.

    To automate this approach you might need to have a scheduled job  (2000 – DTS, 2005 – SSIS), on Begin of the Month.

     

    Here the sample script which may help you..

    Code Snippet

    Create Table bigdatatable (

                [SomeUniqueId] int Primary Key,

                [ActionDateTime] DateTime ,

                [SomeOtherData] Varchar(10)

    );

    go

    Set NOCOUNT ON;

    Insert Into bigdatatable Values(1,'1/1/2007','Action1');

    Insert Into bigdatatable Values(2,'1/10/2007','Action2');

    Insert Into bigdatatable Values(3,'1/21/2007','Action3');

    Insert Into bigdatatable Values(4,'2/1/2007','Action10');

    Insert Into bigdatatable Values(5,'2/10/2007','Action11');

    Insert Into bigdatatable Values(6,'2/20/2007','Action12');

    Insert Into bigdatatable Values(7,'3/1/2007','Action20');

    Insert Into bigdatatable Values(8,'3/10/2007','Action21');

    Insert Into bigdatatable Values(9,'3/20/2007','Action22');

    Go

     

    --use this batch on the Scheduled JOB

    SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT ON; SET QUOTED_IDENTIFIER, ANSI_NULLS ON;

    Declare @ExecutionDate as Datetime

    Declare @ObjectName as nvarchar(100)

    Declare @ColumnList as nvarchar(4000)

    Declare @Query as nvarchar(4000)

    Declare @Name as nvarchar(100)

     

    Set @ExecutionDate = DateAdd(MM,-1,'2/1/2007') --Getdate() :: expected on first day of the month

    Set @ObjectName = 'BigDataTable'

    Set @ColumnList = ''

    Select @ColumnList = @ColumnList + ',' + Name from Syscolumns Where id = (Select Id from Sysobjects where name=@ObjectName and type='u')

    Set @ColumnList = Substring(@ColumnList, 2, len(@ColumnList))

     

    Set @Name = @ObjectName

                + '_'

                + Substring(DateName(MM,@ExecutionDate),1,3)

                + '_' + Cast(Year(@ExecutionDate) as varchar) ;

     

    Set @Query= 'Create View '

                + @Name

                + ' WITH  SCHEMABINDING  as '

                + 'Select '

                + @ColumnList

                + ' from dbo.'

                + @ObjectName

                + ' Where Month(ActionDateTime)= '

                + Cast(Month(@ExecutionDate) as varchar)

                + ' And Year(ActionDateTime)  = '

                + Cast(Year(@ExecutionDate) as varchar)

     

    Exec (@Query)   

    Exec ('Create Unique Clustered Index Index_' + @Name + ' On ' + @Name + '(SomeUniqueId)')

     

     

     

    Friday, June 1, 2007 4:23 PM
  • This code worked very well for me. THanks
    Friday, June 1, 2007 4:47 PM
  •  

    You're welcome.

     

    But give some serious thought to a new design.

     

    This is a major disaster in progress...

     

    Friday, June 1, 2007 5:47 PM