I have a problem similar to this SO question.
I have three tables that are related with an identity column as primary key. And I need to clone them into a new database with new identity values.
These are the tables where I'm going to copy the data, but the relationship is the same in the source database.
CREATE TABLE [dbo].[Code] ( [CodeId] INT IDENTITY (1, 1) NOT NULL, [Serial] NVARCHAR (20) NOT NULL, [...] ) CREATE TABLE [dbo].[Aggregation] ( [AggregationId] INT NOT NULL, [Created] NVARCHAR (50) NOT NULL, CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC), CONSTRAINT [FK_Aggregation_Code] FOREIGN KEY ([AggregationId]) REFERENCES [dbo].[Code] ([CodeId]) ) CREATE TABLE [dbo].[AggregationChildren] ( [AggregationChildrenId] INT NOT NULL, [AggregationId] INT NOT NULL, [Position] INT NOT NULL, CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC), CONSTRAINT [FK_AggregationChildren_Code] FOREIGN KEY ([AggregationChildrenId]) REFERENCES [dbo].[Code] ([CodeId]), CONSTRAINT [FK_AggregationChildren_Aggregation] FOREIGN KEY ([AggregationId]) REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE )
There is a master table, Code, and the other tables related to the master by its primary key
I'm going to add an auxiliary column, with the value of the primary key in the source database, to make the conversion in tables
That process work in SQL Server Management Studio, but when I try to use it in a procedure stored it complains about that the auxiliary column doesn't exist.
The stored procedure is like this pseudo code:
// Alter table to add the auxiliary column // Insert into with select and inserting into the auxiliary column // Alter table to drop the auxiliary column
The stored procedure has an error because I'm inserting in a column that doesn't exist.
Do you know if there is another solution to this problem using an auxiliary column?