Sql-server – Adding Last 12 Months to a row in a table (SQL Server)

sql server

I want to add the last 12 months to a SQL Server table, something like the following:

FixedText Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15

Please can someone help with with this?

Thanks

EDIT: Table schema is as follows:

Department   Month12   Month11   Month10   Month9   Month8   Month7   Month6   Month5   Month4   Month3   Month2   Month1
----------- --------- --------- --------- -------- -------- -------- -------- -------- -------- -------- -------- -------

Best Answer

Perhaps you want something like this?

INSERT INTO dbo.Test (
    Department
    , Month12
    , Month11
    , Month10
    , Month9
    , Month8
    , Month7
    , Month6
    , Month5
    , Month4
    , Month3
    , Month2
    , Month1
    )
VALUES ('Test'
    , DATEADD(MONTH, -12, GETDATE())
    , DATEADD(MONTH, -11, GETDATE())
    , DATEADD(MONTH, -10, GETDATE())
    , DATEADD(MONTH, -9, GETDATE())
    , DATEADD(MONTH, -8, GETDATE())
    , DATEADD(MONTH, -7, GETDATE())
    , DATEADD(MONTH, -6, GETDATE())
    , DATEADD(MONTH, -5, GETDATE())
    , DATEADD(MONTH, -4, GETDATE())
    , DATEADD(MONTH, -3, GETDATE())
    , DATEADD(MONTH, -2, GETDATE())
    , DATEADD(MONTH, -1, GETDATE())
);

This assumes several things.

You have DATE (or perhaps DATETIME, etc) as the data type for the 12 columns for each month.
You want each month to reflect the current day. For instance, since it is currently Jan 2nd, this code would insert 2014-01-02, 2014-02-02, ... 2014-12-02 into the table.

If you are using SQL Server 2012 and above, you can modify this:

DATEADD(MONTH, -1, GETDATE())

to this:

FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MMM-yyyy')

To get the date as Jan-2014. There are also a lot of other ways that are probably more efficient.

Related Solutions

Sql-server – Dynamic Creation of Table using Stored Procedure Without a Use of OpenRowSet in SQL Server

If you cannot use OPENROWSET, you could create a loopback linked server and use OPENQUERY or EXECUTE AT.

DECLARE @srv nvarchar(4000);
SET @srv = @@SERVERNAME; -- gather this server name

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
@server     = N'LOOPBACK',
@srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
@provider   = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000
@datasrc    = @srv;


-- Set the authentication to "current security context"
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname  = N'LOOPBACK',
@useself     = N'True',
@locallogin  = NULL,
@rmtuser     = NULL,
@rmtpassword = NULL;

I blogged about it some years ago.

Another option is using some kind of CLR trickery to interpret the result set definition and create a target table based on the type and length of the columns.

If you're not allowed to use OPENROWSET, I highly doubt you will be allowed to use CLR, though. In this case, a third option could be using sp_describe_first_result_set and code your table creation script in T-SQL. It requires SQL Server 2012 or newer.

Sql-server – Modifying a non managed table partition

The best way to do this is:

Let us call your original table Original.

Create two new tables Jan and Feb) matching the schema of Original. You might want to use the partition management tool for this: https://sqlpartitionmgmt.codeplex.com/. Place these new tables directly on the filegroup you want to end up in. Do NOT place them on a partition scheme.
Add a check constraint to Jan limiting the data to only the data in January 2015
Do the same for Feb, but limiting data to February
Script and drop index on Jan and Feb
Use INSERT Jan WITH (TABLOCK)... SELECT Original WHERE Month = 'January' to populate Jan. This is bulk logged, and hence, should run at around 40-80MB/sec with minimal impact on the transaction log
As above, for February.
Add indexes back on Jan and Feb
Switch out the old January/February partition from Original to an empty table. Original now has no 2015 records (validate this)
SPLIT the January partition function of Original on the February/January boundary (and any other months you want to prepare for)
ALTER TABLE Jan SWITCH TO Original PARTITION x where x is the partition number of January
ALTER TABLE Feb SWITCH TO Original PARTITION y, where y is the partition number of February
Profit

This is the fastest way to achieve the result with minimal impact on the disk system and transaction log.

It is rather complicated, but learning Partition Fu tricks like these are key to getting the best out of SQL Server partitioning.

Best Answer

Related Solutions

Sql-server – Dynamic Creation of Table using Stored Procedure Without a Use of OpenRowSet in SQL Server

Sql-server – Modifying a non managed table partition

Related Question