Help with importing data.

[ChrisH2]

Before I go down the a long path of writing a script for 250 tables, has anyone got a better idea.

I am using MS SQL 2000 and I need to import incremental data changes from a unix system.
There are 250 tables on the unix system. When a record in the unix table is inserted/changed a line is written to an ascii or xml file.
I need to run some code on SQL server to read these files and import the data. It needs to do this every 10-15mins and it has to handle inserts and updates.

The only way I can think of doing this, is to create a script to update the table, then insert into the table. I am going to have to specify every single column in the table for both the update & insert. Then repeat this for all 250 tables.

Is dynamic SQL an option here. Could I read sysobjects & syscolumns and generate the code or is this a bad idea?

 

[AlexCuse]

sysobjects and syscolumns could help you out here. It's kind of a tall order, but I think you could do it in these steps:

1. Insert all table names you will want to handle into temp table 1 (from sysobjects). Make sure temp table also has an identity field.

2. Insert all table names and column names into temp table 2 (from sysobjects and syscolumns).

3. Construct your dynamic SQL statements using a while loop. The counter for this while loop should be based off of the ID field on temp table 1. You should then be able to return a table name from the ID, and then a list of columns based on the table name from temp table 2.

3b. You will need to concatenate the list of columns into one long, comma delimited string (so that it can be placed easily into your query). Good FAQ's on that can be found here faq183-2146 and here faq183-6466.

Just for laughs, have a look at this query. It will help you with populating temp table two (it returns all user tables and columns).

select a.name, b.name from dbo.syscolumns a inner join
(
select * from dbo.sysobjects where xtype = 'U'
) b
on a.ID = b.ID

Whether this takes less time than scripting all of your statements, I don't know, but this should be enough to get you started.

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".