Keep Primary Key during import

Databases
1

I’m using SQL Server 2012, and I’m importing a number of MS Access databases.

The default SQL that the import tool runs looks like this:

CREATE TABLE [pf].[methods] (
[method_id] int NOT NULL,
[method_name] nvarchar(255),
[method_desc] nvarchar(255),
[icon] nvarchar(255)
)

The problem with this is that I lose my autonumber column, so I’ve added this:

CREATE TABLE [pf].[methods] (
[method_id] int NOT NULL IDENTITY (1,1),
[method_name] nvarchar(255),
[method_desc] nvarchar(255),
[icon] nvarchar(255)
)

My problem is that I can’t seem to get the syntax right to also add the primary key. I’ve tried this but it crash & burns:

CREATE TABLE [pf].[methods] (
[method_id] int NOT NULL  IDENTITY (1,1),
[method_name] nvarchar(255),
[method_desc] nvarchar(255),
[icon] nvarchar(255), 
PRIMARY KEY [method_id]
)

Any tips?

2

Any tips?

Start using Sequences instead of Identities :smile:

How are you inserting the values? Are you using an insert statement? You can turn Identity Insert off. You’re a CF dev, right?

3

I think you just convinced me to ask my employer for an SQL Server class. :wink:

I’m inserting the values in SQL Server Manager Studio, using the Import function. I just need to tweak the SQL to retain the identity columns, otherwise all my relationships will be hosed.

I think I figured out that this works:

CREATE TABLE [pf].[methods] (
[method_id] int NOT NULL IDENTITY (1,1),
[method_name] nvarchar(255),
[method_desc] nvarchar(255),
[icon] nvarchar(255),
CONSTRAINT methods_pk PRIMARY KEY (method_id)
)
4

I think I might have misunderstood the question. I thought you had a problem inserting into a table where the identity column was already defined and you were trying to override that. Sequences are nice alternatives for identities. Example D. You can also share them between tables, which would help with a temp table.

But, I think I understand now and I don’t think this will fix your problem. Classes are always good though. :smiley:

1 Like
5

it’s not because you named the constraint, it’s because you enclosed the column name in parentheses as per PRIMARY KEY syntax

you could also have coded PRIMARY KEY on the column itself, after IDENTITY

1 Like
6

Thanks mawburn & rudy :slight_smile:

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.