Follow our news:

Follow canonburysvcs on Twitter Follow Canonbury Services on Facebook Follow Canonbury Services' news by RSS Follow Canonbury Services' news by Atom Follow Canonbury Services' news by email


Add a unique reference to an import file

17/02/2012 – in SQL queries

When importing into GoldMine it’s often useful to have a unique reference against every line in the import file.

If the source data is held in a SQL table then you can run the following script to generate this. First you need to create two new columns: impref (VARCHAR) and impcount (INT).

The process will populate the impref field in each record with the date and time plus an ascending number (eg ‘2012-02-16 10:36 1’).

The script is expecting the import table to be called ‘import’ but you can obviously change this to be whatever you want.

declare @count int
set @count = 0

update import
set impref=CONVERT( CHAR(16), GetDate(), 20)
update import
set @count = impcount = @count + 1
update import
set impref=impref+' '+cast(impcount as varchar)