Hej håller på att ändra i en db så att jag får Primary Key i ArtId

SELECT ArtNr, GruppId, Benamning, Farg, Storlek, Lager, Inpris, Utpris From Lagersaldo

CREATE TABLE Lager (
    ArtId  int IDENTITY(1,1)PRIMARY KEY,
    ArtNr nvarchar(15) NOT NULL,
	GruppId nchar(10),
	Benamning nvarchar(20), 
	Farg nvarchar(20),
	Storlek nvarchar(20),
	Lager int, 
	Inpris int, 
	Utpris int)

INSERT Lager
VALUES(@ArtNr, @GruppId, @Benamning, @Farg, @Storlek, @Lager, @Inpris, @Utpris)

Om du vill behålla värdet i PM från din andra databas:



SET IDENTITY_INSERT
Allows explicit values to be inserted into the identity column of a table.

Syntax
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

Arguments
database

Is the name of the database in which the specified table resides.

owner

Is the name of the table owner.

table

Is the name of a table with an identity column.

Remarks
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

Permissions
Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner.

Examples
This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products
WHERE product = 'saw'
GO

SELECT *
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO

SELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO





jag hade nog gjort så här:

<code>
CREATE TABLE Lager (
ArtId int IDENTITY(1,1)PRIMARY KEY,
ArtNr nvarchar(15) NOT NULL,
GruppId nchar(10),
Benamning nvarchar(20),
Farg nvarchar(20),
Storlek nvarchar(20),
Lager int,
Inpris int,
Utpris int)

SET IDENTITY_INSERT Lager ON

insert into Lager
SELECT ArtNr, GruppId, Benamning, Farg, Storlek, Lager, Inpris, Utpris From Lagersaldo

SET IDENTITY_INSERT Lager OFF
</code>

Fick följande Error;

Msg 545, Level 16, State 1, Line 14
Explicit value must be specified for identity column in table 'Lager' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Hittade felet,

Som så många gånger förr så glömer man vissa små detaljer.
Att som i detta fall inte skriva till vilka fält som man vill lägga till poster är ju ganksa klantigt.


Tack
Gert