DTS:copy data to SQL Server by checking

• AliPolat

  SSC Veteran

  Points: 231

  More actions

  October 1, 2001 at 7:56 am

  #104626

  Hello,

  I connect from SQL Server on Windows 2000 to Progress

  Database on UNIX.

  The database name of SQL Server is cstarsql and the

  name is cstarint on UNIX.

  I would like to schedule to copy data from

  cstarint(Progress) to cstarsql(SQLServer). I did for

  one time, but I want to control if the data has

  already copied or not. If not, it will copy.

  In DTS Query Builder to copy from Progress to SQL

  Server,

  SELECT * from calls WHERE call_date = TODAY

  The name of table of calls is the same for both

  database.The above calls is cstarint(Progress

  Database)

  This command is enough for one time. But I need to

  control if the record has already copied.

  In SQL Server, I control if the record is available or

  not with @@FETCH_STATUS .

  Now I would like to mix two queries, but I couldn't.

  Can anybody do this?

  DECLARE calls_cursor SCROLL CURSOR

  FOR SELECT * FROM calls

  WHERE call_date = TODAY

  OPEN calls_cursor

  -- Perform the first fetch.

  FETCH NEXT FROM calls_cursor

  -- Check @@FETCH_STATUS to see if there are any more

  rows to fetch.

  WHILE @@FETCH_STATUS = 0

  BEGIN

  -- This is executed as long as the previous fetch

  succeeds.

  FETCH NEXT FROM calls_cursor

  END

  CLOSE calls_cursor

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  October 1, 2001 at 8:53 am

  #418586

  I don't think you need a cursor for this - an outer join should work well. Something like this:

  insert into table a (column names here)

  select (all your column names from the insert) from remotetable A left join localtable B on A.Primarykey=B.Primarykey where B.PrimaryKey is null

  If you have a lot of data, it's probably faster to move all the data to one server or the other first, but you'll have to test to be sure.

  Andy

  Andy
  SQLAndy - My Blog!
  Connect with me on LinkedIn
  Follow me on Twitter