1 Topic by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Topic: Moving records to new Survey

Hi All,

I am trying to reorganise some of my records. I want to move records for certain sites to  a new Survey. Is there  a Batch Update that will do this (or one that can be adapted easily) or can this be achieved another way?? I could cut and paste Event by Event but it would take forever. Any suggestions anyone?

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website

2 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Moving records to new Survey

Looks Straightforward enough. For a single site you would need to use (but I haven't tested it)

UPDATE SURVEY_EVENT SET SURVEY_EVENT.SURVEY_KEY = 'yyyyyyyyyyyyyyyy'
WHERE (((SURVEY_EVENT.LOCATION_KEY)='xxxxxxxxxxxxxxxxx'))

Where xxxxxx etc is the location key of the site for which data is to be moved and yyyyy is the key of the survey to which they are to go.

For a small number of sites you could use:

UPDATE SURVEY_EVENT SET SURVEY_EVENT.SURVEY_KEY = 'yyyyyyyyyyyyyyyy'
WHERE (((SURVEY_EVENT.LOCATION_KEY) in ('xxxxxxxxxxxxxxxxx', 'zzzzzzzzzzzzzzzz'))

Where zzzz is also the location key of one of the sites. Not sure how many sites you can do in one go like this, but it will be limited by the total length of the command string.

You might also want to adapt it to set the changed date and changed by fields and add a comment, but the principle should be the same.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

3 Reply by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Re: Moving records to new Survey

Thanks Rob - I'll try to see if I can give that a go....

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website

4 Reply by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Re: Moving records to new Survey

HI Rob - how would I actually use this - within SQL Server Management Studio?

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website

5 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Moving records to new Survey

Yes, in Management Studio (back up the database first!)

Click the New Query button, then in the window which appears type
USE NBNdata
GO
Then paste in the text I gave you & click the execute button (red exclamation mark).

If it works you should get a completed successfully message.

Or in the linked Access db, create a new query, make it an Update query, switch to SQL view and paste the text in before clicking the run button (again red exclamation mark.)

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website