Inserting GUID

[Guest]

I am converting data from an Access database into SQL Server. As a part of
the conversion, I need to add a GUID as the primary key in the SQL Server
table. Is there an equivalent function to NewID() that can be used in an
Access append query?

Thanks.

 

[Tom Ellison]

Dear Paco:

Typically, when appending to a table which has an identity column, you can
just omit setting that value and let it assign a new one.

Tom Ellison

 

[Guest]

In this case, the the column isn't an identity column.

 

[Tom Ellison]

Dear Paco:

Well, this has me stumped. The column is a GUID (the ID stands for
identity) and it is the primary key, but it is not an identity column. I'm
not familiar with this usage.

Tom Ellison

 

[Guest]

Hi, Paco.

The easiest way to do this is to ensure that the primary key in the SQL
Server table is a UNIQUEIDENTIFIER data type and that the DEFAULT for this
column is NewID( ). That way, the records inserted into this table will
automatically have a GUID created for them.

However, if the DBA isn't available and you don't have permissions to alter
this table, then you can have Access create the GUID's for you before the
append. To do so, create a temporary table of identical structure to the
table where your Access records are currently stored. Ensure that the
primary key column in this temporary table is an Autonumber, but change the
default Field Size from Long Integer to Replication ID. Run an append query
from the original table into the temporary table to create a GUID for each
record inserted from the original table. When it's finished, use this
temporary table as the source of your append query into the SQL Server table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Guest]

The other program (the one we are appending into) is object oriented. I'm not
sure why (I'm don't know much about OOP), but the tables don't have any
identity columns. The PK columns are text (nvarchar).

 

[Guest]

This sounds like a plan. I'll give it a try. Thanks.