SP won''t let me insert into table
-
I have an SP that is supposed to select a record from the Projects table then Insert it "n" times back into the table creating "clones" of the original record. Here's the code:
CREATE PROCEDURE [dbo].[Clone_Project]
(
@id int,
@count int
)
AS
BEGIN
DECLARE @i int
DECLARE @rc int
DECLARE @title varchar(2000)
BEGIN TRANSACTION
SELECT * INTO #temp FROM Projects WHERE rojectID=@id">ProjectID=@id
SELECT @title = Project_Name FROM Projects WHERE rojectID=@id">ProjectID=@id
ALTER TABLE #temp DROP COLUMN ProjectID
SET @i=0
WHILE @i<=@count BEGIN
UPDATE #temp SET roject_Name=@title+'">Project_Name=@title+' ('+cast(@i as varchar(3))+')'
INSERT INTO Projects
SELECT * FROM #temp
IF @@rowcount<>1 OR @@error<>0 BEGIN
SET @rc = @@error
ROLLBACK TRANSACTION
RETURN @rc
END
SET @i=@i+1
END
COMMIT TRANSACTION
END
GO
So theoretically "Clone_Project 469, 5" would give me 5 copies of project 469 with the Project_Names having an incremental counter added (i.e. "Test Project" --> "Test Project (1)", "Test Project (2)", etc).
The problem is I'm getting an error on the Insert Into that says I can't insert into a table with an Identity column. I already dropped the identity column from the #temp table. ("ALTER TABLE DROP COLUMN") so I assumed I could simply use INSERT...SELECT * FROM #TEMP. I'd rather not code all the column names since then I'll have to maintain it every time the Projects table changes.
Is there a way to do what I want without coding all the column names (except the Identity column)?
-
It's your "Projects" table that seems to have the identity column...you'll have to explicitly..
SET IDENTITY_INSERT Projects ON
...insert data...........
SET IDENTITY_INSERT Projects OFF
You will also have to explicitly specify the column names in your Projects table...
Having said all that, what is the purpose of this identity column if it's not going to be unique ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
The intent of the SP is to copy all the columns in the source row except for the Project_ID (identity column) and insert them into "n" new rows. Each new row would have a new Project_ID. That's why I'm confused, unless SQL Server just doesn't understand how the use of DDL in the SP affects the Insert.
- I copy the complete row to #temp
- I Drop the Identity column (Project_ID).
- I then Insert Into Projects Select * From #temp as many times as I need new Projects.
Theoretically since I've Dropped the Project_ID column from #temp I should be able to do the Insert...Select the way I've coded it, right? The columns in #temp match the columns in Projects except for Project_ID.
Have I run into one of those places where SQL Server is just plain dumb?
-
The following worked:
CREATE PROCEDURE [dbo].[Clone_Project]
(
@id int,
@count int
)
AS
BEGIN
DECLARE @rc int
DECLARE @i int
DECLARE @title varchar(2000)
BEGIN TRANSACTION
SELECT Project_Name, Project_Description, Project_Start_Date, Project_Due_Date, Project_Key_Contact, Project_Owner,
Project_Estimated_Days, Project_Est_Self_Study, Project_Est_Classroom, Project_Business_Strategy, Project_Multiple_Sponsor,
Project_Program_ID, Project_Indicator_ID, Project_Level_ID, Project_LifeCycle_ID, Project_Phase_ID, Project_Priority_ID,
Project_Type_ID, Project_Workload_ID, Project_Active, Project_Training_Event, Project_Assoc_Workshop
INTO #temp FROM Projects WHERE <A href="mailto
rojectID=@id">ProjectID=@idSELECT @title = Project_Name FROM Projects WHERE <A href="mailto
rojectID=@id">ProjectID=@idselect * from #temp
SET @i=1
WHILE @i<=@count BEGIN
UPDATE #temp SET <A href="mailto
roject_Name=@title+'">Project_Name=@title+' ('+cast(@i as varchar(3))+')'INSERT INTO Projects
SELECT * FROM #temp
IF @@rowcount<>1 OR @@error<>0 BEGIN
SET @rc = @@error
ROLLBACK TRANSACTION
RETURN
END
SET @i=@i+1
END
COMMIT TRANSACTION
drop table #temp
END
Apparently SQL Server was convinced there was still a Project_ID column in the #temp table, even though I'd dropped it. It was throwing an error based on syntax alone, it never even executed one line of code. Once I put all the column names (minus the Project_ID) into the initial Select everything else worked fine.
-
Ya - I was going to ask if you've tried explicitly specifying all column names (in your insert statement)!!! - glad you got it working!
**ASCII stupid question, get a stupid ANSI !!!** -
Try putting a "GO" just after the ALTER table statement. It should work ok after that.
ALTER TABLE #temp DROP COLUMN ProjectID
GO <-----
SET @i=0
ron
-
Maybe in QA, but not within a Stored Procedure...
-
Sorry, yes you are correct. It seems that until the "GO" sql server doesn't stop to make the ddl change so you are stuck with naming the columns.
ron
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply