INSERTING RECORDS FROM A SELECT
-
So, i'm being really dumb here, and this is a real quick fix.
I am doing data migration and have 2 tables (catalog and products) in 2 differrent database. The fields are similar in the 2 tables. I'm trying to select all the rows in the old table (products) and insert them into the new table (catalog).
Both these tables have an identity on the catalogID field.
=========================================================
SET IDENTITY_INSERT Catalog OFF
INSERT INTO Catalog
SELECT
catalogID AS [CatalogID],
cSubCategory AS [ManID],
cCode AS [Code],
cName AS [Name],
cDescription AS [Description],
cImageURL AS [Image],
cSpecials AS [Special],
cNew AS [New],
cWeight AS [Weight],
VAT AS [VAT],
RRP AS [RRP],
0 AS [StackID],
0 AS [IsHidden],
1 AS IsEnabled
FROM
old_db..products
SET IDENTITY_INSERT Catalog ON
=======================================================
I'm getting this error: An explicit value for the identity column in table 'Catalog' can only be specified when a column list is used and IDENTITY_INSERT is ON.
-
I think that the error message is spot on, just a slight change required:
INSERT INTO Catalog (fieldname1, fieldname2, ... , fieldnamenn)
SELECT
catalogID AS [CatalogID],
cSubCategory AS [ManID],
etc
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Also, haven't you got your
SET IDENTITY_INSERT Catalog OFF
and
SET IDENTITY_INSERT Catalog ON
the wrong way round?
You want to set it ON before doing the insert and then set it OFF...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply