add new records to a table weekly--how to?

Question

add new records to a table weekly--how to?

John N

SSC Veteran

Points: 245
More actions
January 25, 2007 at 8:22 am

#175694

I am trying to figure out the best way to add new records to some tables on a weekly basis. The original data will all be transfered from an AS/400 db2 system. Once all the current data has been transfered over I will need to add new records from the AS/400 to my sql 200 tables. Can any one suggest a good way of doing this?
Thanks

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John

Set up your AS/400 server as a linked server and then create a job to copy data to your table weekly. Or create a DTS package to do the same thing. I've never used AS/400 so I don't know whether the driver that you need to connect to it comes with SQL Server or whether you need to download and install it.

John

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

Is the question about how to physically do the INSERT ?

Or is the question around how to schedule this ?

Clive Strong SSChampion Points: 11222 More actions · Answer 3

I installed the AS/400 DB2 ODBC driver in a previous job.

As John mentioned, Linked Server or DTS work just as well as each other. We used a lot of DTS becuase of the our needs to manipulate the data.

John N SSC Veteran Points: 245 More actions · Answer 4

my question, is it better to just delete the whole table ever week or insert the new records? if insert is the best way, would assigning a flag (read/unread) be the best method?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

John

Best just to insert the new records. Is your data date-stamped? If so, you can write your insert query based on the date. If not, you will indeed need some kind of flag system.

John

Chris Foster-356495 SSC Veteran Points: 224 More actions · Answer 6

I'd say it depends on how many records there are in the source database, and what the purpose of the sql server system is. If there aren't many records, and you're just taking a copy of the data (say, for datawarehousing, or some read-only purpose), then you could just truncate and reload the whole lot every week. I've done it that way before now and it's better than creating something that's more hassle than it's worth.

Chris