1 Reply by GaryP

  • GaryP
  • Veteran
  • Offline
  • Registered: 12-07-2009
  • Posts: 65

Re: Creating temp tables to upload and compare data

Hello,

I would like to compare two sets of extracted data to check for differences. I was thinking that I could create a couple of temp tables and upload the data then run an sql to compare differences.
To create the temp tables and insert data statements, is that something I can find in the report manual? I seem to remember the sql to load data was something like 'Insert into xxx From filename'? And a temp table is something like Create Temp xxxx?

Any thoughts or help that would point in the right direction appreciated!

Thanks

Gary

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Creating temp tables to upload and compare data

There is a fairly good explanation in the XML Reports help file. The latest version is at 

http://forums.nbn.org.uk/uploads.php?file=R6XMLHelp.zip

Search for temporary tables.

Mike Weideli

MikeWeideli's Website

3 Reply by GaryP

  • GaryP
  • Veteran
  • Offline
  • Registered: 12-07-2009
  • Posts: 65

Re: Creating temp tables to upload and compare data

Cheers Mike, I will have a look at that and sure I will be able to go forward from there.

Gary

4 Reply by GaryP

  • GaryP
  • Veteran
  • Offline
  • Registered: 12-07-2009
  • Posts: 65

Re: Creating temp tables to upload and compare data

This code worked ok to load data in from a .csv file.

BULK
INSERT #LepCompareOne
FROM 'C:\Program Files\Recorder 6\User Files\Reports\Output\LepidopteraFullList.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Just to take this one step further. Is it possible in SQL to read one of the created tables then for every row found check the other table and update if a value is different?

Cheers for now

Gary

5 Reply by GaryP

  • GaryP
  • Veteran
  • Offline
  • Registered: 12-07-2009
  • Posts: 65

Re: Creating temp tables to upload and compare data

This code seemed to work ok for the update part...

UPDATE #table1
SET #table1.value = #table2.value
FROM #table1
JOIN #table2 on #table1.value = #table2.value
and #table1.value <> #table2.value

This did the update in the temp table with the new values and worked ok.

Cheers for now

Gary