> Now the problem with this is that the primary key must be non-null and
> unique so must be given a value in the INSERT statement. But, the
> identity value isn't available (I presume) until after the INSERT
> statement has executed.

That's correct, you don't know the IDENTITY value until after the rows are
inserted. It shouldn't be a problem. Leave the IDENTITY column as a numeric
and just format it with leading zeros in your application, in a view or when
you query the table.

In SQL an IDENTITY column should be used only as a *surrogate* key. You
should ensure that you declare the *natural* key of the table as unique and
non-NULL as well. As I expect you already know, the design considerations in
SQL are quite different to those in Fox and you should review your DB schema
verify that you have the appropriate keys in place and that your tables are
correctly normalized.

--
David Portas
SQL Server MVP
--