SSIS 2008 - Update/insert into the database based on a condition

Question

Post reply

SSIS 2008 - Update/insert into the database based on a condition

0409SQL

SSChasing Mays

Points: 650
More actions
November 20, 2013 at 4:38 am

#300675

Hello everyone !
I have a query. I am updating/inserting a database table below(currently with no data in it)
I have finance data in excel, which is given quarterly. Its a forecast and has monthly data in it. In quarter 1, the data given is for Jan -Dec. Again quarter 2, data is given for Jan- Dec. I do not want to overwrite the data but store it in different columns (see below). So if its Quarter 1, the new rows are inserted. If its q2,q3,q4 then the null values are update for each month.
ID Timekey Q1 Data Q2 Data Q3 Data Q4 data
--- -------- ------- ------ ------- --------
1 1-Jan-2013 111 NULL NULL NULL
2 1-feb-2013 231 NULL NULL NULL
3 1-Mar-2013 123 NULL NULL NULL
4 1-Apr-2013 344 NULL NULL NULL
How do we achieve this in SSIS? I have looked around for long but with no luck. Please help :crying:

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ryan George SSC Veteran Points: 268 More actions · Answer 1

Hi,

could you be more specific?

actually I didnt get your query clearly.

Veteran Jones SSC Eights! Points: 937 More actions · Answer 2

Are you trying to insert all the data into a table and then pivoting the information in a specific way or are you wanting to keep appending columns to an existing table?

0409SQL SSChasing Mays Points: 650 More actions · Answer 3

HI

No, I am not. Here is the existing table structure:

CREATE TABLE [dbo].[ContributionForecast](

[Id] [int] IDENTITY(1,1) NOT NULL,

[TimeId] [int] NOT NULL,

[Q1Forecast] [decimal](18, 2) NULL,

[Q2Forecast] [decimal](18, 2) NULL,

[Q3Forecast] [decimal](18, 2) NULL,

[Q4Forecast] [decimal](18, 2) NULL,

CONSTRAINT [PK_ContributionForecast] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

I dont need to keep appending the columns but i need to add as new rows if there is no data for that year. Else update the table based on Q1, Q2, Q3 or Q4.

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 4

Please also provide some sample data (as insert statements) and the expected outcome of your query.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

You could consider using the MERGE combined with this kind of update.

SELECT CASE WHEN DATEPART(qq, GETDATE()) = 1 THEN 'VALUE'

END

, CASE WHEN DATEPART(qq, GETDATE()) = 2 THEN 'VALUE'

END

, CASE WHEN DATEPART(qq, GETDATE()) = 3 THEN 'VALUE'

END

, CASE WHEN DATEPART(qq, GETDATE()) = 4 THEN 'VALUE'

END