Loop to insert data into a table

Question

Loop to insert data into a table

sennat

Grasshopper

Points: 15
More actions
April 5, 2010 at 2:42 pm

#220039

I am migrating a VB6 to SSIS and I need help in how to resolve this looping to insert rows into a table using Store Procedure...
'Add ACCT MONTHS for new year
sSQL = "SELECT *"
sSQL = sSQL & " FROM dbo.DIM_ACCT_DATE"
'Every two chars denoted Days in each month starting from Jan to Dec
sDays = "252020251920242019251919"
'Every character denotes number of weeks in each month starting from Jan to Dec
sWeeks = "544544544544"
sMonths = "01JAN02FEB03MAR04APR05MAY06JUN07JUL08AUG09SEP10OCT11NOV12DEC"
sYear = "2012"
For Ix = 1 To 12
mRSLocal.AddNew
mRSLocal.Fields("ACCT_DT_LVL_1").Value = "ACCT MONTHS"
mRSLocal.Fields("ACCT_DT_LVL_2").Value = Mid(sMonths, (((Ix - 1) * 5) + 3), 3) & " " & sYear
mRSLocal.Fields("ACCT_DT_LVL_3").Value = Null
mRSLocal.Fields("ACCT_DT_CD").Value = "temp" & Ix
mRSLocal.Fields("ACCT_MO_YR").Value = Str(Ix) + "/01/" & sYear
mRSLocal.Fields("ACCT_DT_ORDER").Value = sYear & Mid(sMonths, (((Ix - 1) * 5) + 1), 2)
mRSLocal.Fields("ACCT_DT_OPERATOR").Value = "~"
mRSLocal.Fields("ACCT_DT_CALC").Value = Null
mRSLocal.Fields("ACCT_DT_CMO").Value = Null
mRSLocal.Fields("ACCT_DAYS_IN_MO").Value = Mid(sDays, (((Ix - 1) * 2) + 2), 2)
mRSLocal.Fields("ACCT_WKS_IN_MO").Value = Mid(sWeeks, (((Ix - 1) * 1) + 2), 1)
mRSLocal.Fields("UPDT_TMSTMP").Value = Null
mRSLocal.Update
Next Ix

Viewing 8 posts - 1 through 8 (of 8 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I can't tell you how to do that in VB in SSIS, but it would be amazingly easy to turn those into input parameters for a stored procedure and then use that to insert the whole thing all at once, without a loop, if you want to do that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Having been a VB6 programmer for lol too many years, let me tell you ..

take GSquared's offer.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

sennat Grasshopper Points: 15 More actions · Answer 3

Thanks for your reply... But can u tell me how it should be done without a loop thru store proc...

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Follow the first link in my signature block to post the table's definitions, including indexes (if any), column constraints (if any). Without knowing the details about the table it is difficult or almost impossible to give you a tested answer.

I believe the sample data you have already provided is adequate

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

bitbucket-25253 (4/5/2010)
Follow the first link in my signature block to post the table's definitions, including indexes (if any), column constraints (if any). Without knowing the details about the table it is difficult or almost impossible to give you a tested answer.
I believe the sample data you have already provided is adequate

Agreed with Ron on this. Any script that would be provided would be more guesswork than necessary.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sennat Grasshopper Points: 15 More actions · Answer 6

Here are the table definitions, index and constraints...

CREATE TABLE [dbo].[DIM_ACCT_DATE](

[ACCT_DT_LVL_1] [char](25) NOT NULL,

[ACCT_DT_LVL_2] [char](25) NOT NULL,

[ACCT_DT_LVL_3] [char](25) NULL,

[ACCT_DT_CD] [char](25) NOT NULL,

[ACCT_MO_YR] [smalldatetime] NULL,

[ACCT_DT_ORDER] [int] NOT NULL,

[ACCT_DT_OPERATOR] [char](1) NOT NULL,

[ACCT_DT_CALC] [varchar](500) NULL,

[ACCT_DT_CMO] [varchar](255) NULL,

[ACCT_DAYS_IN_MO] [smallint] NULL,

[ACCT_WKS_IN_MO] [smallint] NULL,

[UPDT_TMSTMP] [datetime] NULL,

CONSTRAINT [PK_DIM_PARC_ACCT_DATE] PRIMARY KEY CLUSTERED

(

[ACCT_DT_CD] ASC

)) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_DIM_PARC_ACCT_DATE] ON [dbo].[DIM_ACCT_DATE]

(

[ACCT_MO_YR] ASC

)ON [PRIMARY]

GO

GSquared SSC Guru Points: 260824 More actions · Answer 7

This will break up the days/month string:

DECLARE @Days CHAR(24);

SELECT @Days = '252020251920242019251919';

;WITH Numbers (Number) AS

(SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY OBJECT_ID) * 2

FROM sys.columns AS C)

SELECT SUBSTRING(@Days, Number-1, 2)

FROM Numbers;

You just do the same sort of thing with each string you want to parse, join those together, and write your Insert Select statement off of that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon