SQL Server 2000 - DTS

How to insert NEW RECORDS ONLY

[Argblat]

Hi all,

I'm wondering how to setup a DTS Package in SQL Server 2000 that only pulls in records that aren't already in the table. I do have a primary key to compare against, since I'm sure someone will ask, and the data source I'm pulling from is MYSQL.

The gist of the idea (in SQL pseudocode) is:
Select * from MYSQL_Table where MYSQL.ID IS NOT IN SQL_Server_2000.ID

If there are any other details that would be useful to anyone who can help me, please ask ... I'll be more than happy to go into specifics and gory details...but I figure for now, I'll keep it simple and generic, and see where it goes from here

Any help is greatly appreciate,
Mike

[Argblat]

I found this while searching for a solution

Insert into dbo_SQL_Table (field1, field2,....)
Select Access_Table.field1, Access_Table.field2) from
Access_Table
where Access_table.sm_timestamp not in (select
sm_timestamp from SQL_Table)

If this is a valid solution, my question becomes what is the proper formatting for Database.Table.Column in order to DTS to know which one I'm talking about for each reference, becuase it seems based on what I've tried so far, that the DTS ONLY looks at the MYSQL and it gives errors when you try to select statements back into the SQL within the DTS

-Mike

[aspdaddy]

You can also use [IP Address] instead of Server

INSERT INTO
Server.Database.dbo.Table (Column)
SELECT Column
FROM Server.Database.dbo.Table

[Argblat]

Let me create an example for the purposes of making this easier.

Let's say that I have a MySQL Table:

Server: 'MySQL_Server'
Database: EMPLOYEE_INFO
Table: MySQL_EMPLOYEE
---------------------
EmployeeID - int(4) PK
EmployeeName - varchar(50)

I then have an Identical Table called SqlServer_EMPLOYEE on the 'SQL_Server'

I then go into my Data Transformation Services (DTS) and setup the following query against the MySQL DataSource using MySQL ODBC 3.51 Driver and a User/System DSN I setup prior:

INSERT INTO 'The SQL SERVER Data'
select * from 'The MySQL SERVER Data'
where MySQL.EmployeeID NOT IN (select SqlServer.EmployeeID from SqlServer)

It give me a MySQL error, becuase the query is being run against MySQL, and MySQL doesn't know anything about the SQL Server Data....

So I still dont' know what to do

-Mike

[Argblat]

bump...

Someone please help me with this

-Mike

[aspdaddy]

There are 2 different issues here:

1) Connecting to MySQL from DTS and retrieving data
2) Writing the correct SQL to get new records and insert them

Lets solve 1) first, can you select and display a record from DTS using the mySQL data source? If not what error do you get?

[Argblat]

aspdaddy,

What I can do is setup a DTS package to 'copy' rows from my MySQL datasource into a new or pre-existing SQL Server 2000 table.

What I can't do, and what I would like to be able to do (and what the wording of your question, "can you select and display a record from DTS using the mySQL data source" makes me think that I can do) is to be able to query the mysql datasource using something like Query Analyzer and return on-the-fly records.

If this is possible, setting up DTS as a middle-man translator of sorts, I would be very interested to learn how to do this, and it would certainly solve my problem.

-Mike