Hi everyone,
I am somewhat of a newbie to DTS, but was wondering if there is a way
to have DTS insert new data into a SQLServer table:

CREATE TABLE SecMonthTbl (
[SecMonth] [datetime] NOT NULL
)

A record for every second between two dates. Most likely be a month.
Don't ask why I need this!

I tried a stored procedure to do a bunch a loop of an insert statement,
and increment the date to insert everytime by a second but that was
taking too long.

This problem isn't really moving any records from one source to
another, it would just be adding new data.
Is it possible?
I am guessing some sort of ActiveX task but not sure.

Thanks in advance for your help.

I'm not really sure how using an ActiveX script task would
speed things up. You will still be inserting about 2.5
million records or so (for a month) and you still have to
dynamically set the dates so I don't know that something
like predefined files with the data and bulk insert would do
you any good. Maybe but it's hard to say without knowing
more specifics on the date requirements and the rules around
what time periods you need and when. You'd may want to look
further at ways to speed up your stored procedure for the
insert. Without seeing what you did, it's hard to say. One
thing is to make sure you have set nocount on.
Other than that, you could post your table definition and
any other DDL as well as the stored procedure in the
programming newsgroup. They will help you figure out how to
speed it up.

-Sue

On 23 Feb 2005 09:50:04 -0800, plau011 (AT) hotmail (DOT) com wrote:

Hi Sue,

Thanks for your reply. I didn't try the NOCount, but here is the
storedprocedure with the NOCOunt. Without the NoCount it took about 21
minutes

CREATE PROCEDURE SecMonthADd AS

DECLARE @CurDatetime datetime

set NoCount ON
delete from secmonth
set @CurDatetime = '5/1/04'
While @CurDatetime < '6/1/04'
BEGIN
insert into Secmonth values(@CurDatetime)
set @CurDatetime = dateadd(s,1,@CurDatetime)
END
set NoCount OFF


I am planning to have the dates passed to the SP, but this is for just
testing purposes.

I did write a little VB program to add these records to a Access db and
it took about 2 minutes to complete. I then ran a DTS from the Access
to SQL Server and it took about the same time maybe a little longer.
This stored procedure took about 21 minutes.

If you want to know the why I am doing this. Look at this Google Group
entry and see Celko's response:

http://groups-beta.google.com/group/...5a65ef81b0cdfd


....Then load it with one row for each seocnd in a year (approximately
60
second * 60 minutes * 24 hours * 365.2455 days) = 31,535,635
rows).......

He was talking about a year, I only really need for a month.The query
is suggested was very fast, just the creating of this table is a bit
time consuming.

Thanks again






Sue Hoegemeier wrote:

Okay...so you basically need a calendar table by seconds.
And yes that will perform pretty fast for the type of query
required in that post.
Is there some reason that you really need to build this all
on the fly all the time? Couldn't you just have a static
table or even static tables with different time periods if
that serves your reporting, querying needs better? In
addition, on a server this would likely run faster. I just
ran it on a relatively slower laptop to see how long it
would take while doing other things and it populated a month
in 20 minutes.
But I'd still wonder why you couldn't just maintain static
tables of the data. That's typically how you'd use a
calendar table anyway if it's going to be used for something
more than a one off type of reporting scenario.
In terms of why you can insert into Access quicker, I'm not
sure how you went about it but a big difference would be
that access doesn't log transactions.
In terms of importing the data from Access and into SQL
Server, if you just set up a basic import the default
options would have been set to fast load so it will invoke a
bulk copy process using IRowsetFastLoad which is
considerably faster. You can use that with other data
sources into SQL Server...which is why I had eluded to that
a bit originally.

-Sue

On 23 Feb 2005 14:02:35 -0800, plau011 (AT) hotmail (DOT) com wrote:

T-SQL is definately your best option to accomplish this. DTS is not,
especially not using an ActiveX script which is uncompiled code and
therefore very slow.

Is this a one-time operation or do you need to do it regularly?

Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamiethom...tegory/71.aspx


<plau011 (AT) hotmail (DOT) com> wrote