Board index MS SQL Server

Date conversion?

Date conversion?

Post by Lionel » Sat, 11 Jan 2003 00:36:33



Hi,

I'm doing the data conversion, and have more than 50000 records need to be
processed. And it took about 4 hours to finish.  I want to speed it up.

The logic like this:
    Set rs = db.OpenRecordset("SELECT * FROM Table1;", dbOpenForwardOnly)
    Do While Not rs.EOF
      .
      Rs1(ADO).update
      .
      Rs2(ADO).update
      .
      Rs3(ADO).update
      FuncCall( RS4(ADO) could update other table as well)
      .
      Rs.movenext
    Loop

How can I control committing 100(or 1000) top-level records each time?

DTS is the best way to do that?  What will happen when the process in middle
failed (the program now won't handle it, need to do from beginning)

Thanks.

 
 
 
Top

Date conversion?

Post by Allan Mitchel » Sat, 11 Jan 2003 02:57:58


DTS Will handle this.  You can set a datapump to be "All or nothing" or you
can commit in batches.  DTS will definitley be quicker.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> Hi,

> I'm doing the data conversion, and have more than 50000 records need to be
> processed. And it took about 4 hours to finish.  I want to speed it up.

> The logic like this:
>     Set rs = db.OpenRecordset("SELECT * FROM Table1;", dbOpenForwardOnly)
>     Do While Not rs.EOF
>       .
>       Rs1(ADO).update
>       .
>       Rs2(ADO).update
>       .
>       Rs3(ADO).update
>       FuncCall( RS4(ADO) could update other table as well)
>       .
>       Rs.movenext
>     Loop

> How can I control committing 100(or 1000) top-level records each time?

> DTS is the best way to do that?  What will happen when the process in
middle
> failed (the program now won't handle it, need to do from beginning)

> Thanks.


 
 
 
Top

1. Eternity date/date conversion

Thank you very much Joe, Doo, Nick !

Actually I am transforming data from an old database
to a newly designed database.

Basically in one table the from_date is in a format of
20000326, while the to_date SOMETIMES is 00000000 which
means eternity. Actually all the columns in this table
were defined as CHAR.

Now I want to select these dates from this table and
insert them into another table whose date columns are
defined as DATE. So I have to convert those invalid
date format before I insert them into the new table.
I just don't know the syntax for doing this, can you
help me ?  Thanks so much

I use

INSERT INTO new_table

(EventNo, Description, FromDate, ToDate)

SELECT CONVERT(INT, poll64.col001), CONVERT(VARCHAR(30), poll64.col002), substring
(poll64.col003, 1, 4)+'/'+substring(poll64.col003, 5, 2)+'/'+substring
poll64.col003, 7, 2), substring(poll64.col004, 1, 4)+'/'+substring(poll64.col004,
5, 2)+'/'+substring poll64.col004, 7, 2)
FROM old_table

But how about in the case of there is 00000000 in the old_table's column 004?

Yuelin

2. ADO, SQLServer, Chinese and VB

3. Julian Date Conversion to SQL Date:

4. ROLAP with 0 agregations or Realtime cubes return nothing...

5. Date Conversion, String to java.util.Date from Access DB

6. ADO with IDS SPL

7. Date to Julian date conversion

8. (DB)ComboBox + MultiSelect + DataSource

9. week(int) to date(date) conversion in SQL

10. Julian/Serial Date conversion

11. Date conversion

12. A Useful Date Conversion Function -- Problem Initializing Default Function Value to Getdate()


All times are UTC
Board index