MS SQL Server Communication

[Izzy123]

Hi,

I am writing an application where I would do the following:

1) import an access file to MS SQL server.

2) Compare the records in that file/table to a master table.

3) If there are new records I would add them to the master table.

My questions are:

1) How can I import a table/file into the server?

2) How can I execute the script that shows if there are new records between both tables.

I would like to automate the process. I would like to ask the user about the location of the file, then, I would take over and get the job done using  LabWindows CVI. Your help is really appreciated.

Ismail

[Jonathan_C]

Hi Ismail,

This can all be accomplished using the LabWindows/CVI SQL toolkit.  The easiest way that I can think of to do this is as follows. I have broken this up into three sections:

Import Access Data

1. Create an ODBC DSN to the MS SQL server
2. Programmatically connect to the Access file
1. Refer to the KnowledgeBase article: Programmatically Specifying an Arbitrary File as the Data Source with the CVI-SQL Toolkit
2. This KnowledgeBase article uses Excel as an example, however; the theory should work exactly the same for Access.  You can use an Acess Database File DSN as a reference.
   
3. Open the MS SQL and the Access connection in LabWindows/CVI
4. Create a table on the MS SQL server to store the imported Access data
5. Copy the Access data into the newly created table

Compare Records

1. If you are using MS SQL 2005, to return all rows in table1 that do not match exactly the rows in table2, we can just use the EXCEPT command like this:
• select * from table1 except select * from table2
2. If you are not using MS SQL 2005, you can use the UNION command.  To return all rows in either table that do not completely match all columns in the other as well as all rows in either table that do not exist in the other table, use the query below.  This query handles nulls as well, since GROUP BY normally consolidates NULL  values together in the same group.  If both tables match completely, no rows are returned at all.
• SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
  FROM
  (
    SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
    FROM A
    UNION ALL
    SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
    FROM B
  ) tmp
  GROUP BY ID, COL1, COL2, COL3 ...
  HAVING COUNT(*) = 1
  ORDER BY ID

Add New Records to Master Table

1. Once you have compared the records, you can loop through each result that is returned
2. Take each result and insert it into the Master Table
   

[Izzy123]

Hi Jonathan,

Thank you very much for your quick response and the clear and complete reply. I will write the code in the next 2 days and let you know if I have any issues. Thanks again.

Ismail