October 13, 2016 at 10:08 am
trying to insert data from one table to multiple tables row by row with derived columns. Somehow it's end up with infinite loop. and also throwing the message of
(5727 row(s) affected)
Msg 545, Level 16, State 1, Line 40
Explicit value must be specified for identity column in table 'tbl_yacht_names' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
--DROP TABLE #temp
SET IDENTITY_INSERT dbo.tbl_yacht_names OFF
SELECT manual_yacht_id,yacht_name,yacht_type, yacht_hull_mat
Into #Temp
From [dbo].[tbl_yacht_manual_emp] WHERE yacht_name IS NOT NULL OR yacht_name=''
--SELECT * FROM #temp
Declare @Id INT,
@yacht_id INT,
@yacht_name_id int
While (Select Count(*) From #Temp) > 0
Begin
Select Top 1 @Id = manual_yacht_id From #Temp
SELECT @yacht_name_id = MAX(yacht_name_id) FROM [dbo].[tbl_yacht_names];
SELECT @yacht_id = MAX(yacht_id) FROM [dbo].[tbl_yacht_names];
INSERT INTO [luxyachts].[dbo].[tbl_yachts] ([date_entered],[hull_number],[yacht_type],[hull_material],[superstructure_material],[hull_style],[keel_laid_on],[launch_date],[sales_date]
,[delivery_date],[yacht_valid],[yacht_use],[user_id]) SELECT GETDATE(),NULL,[yacht_type],NULL,NULL,NULL,NULL,GETDATE(),NULL,NULL,0,1,NULL FROM #temp
SELECT @Yacht_id = scope_identity();
INSERT INTO [dbo].[tbl_yacht_names] ([yacht_id],[yacht_name],[effective_from],[effective_through],[current_name]) SELECT TOP 1 (SELECT MAX(yacht_id) FROM [luxyachts].[dbo].[tbl_yachts]),[yacht_name],NULL,NULL,1 FROM #Temp ;
SELECT @Yacht_id = scope_identity();
Delete #Temp Where manual_yacht_id = @Id
End
October 13, 2016 at 10:25 am
First and foremost, you do NOT need a loop here. All the loop is doing is causing what could be a pretty fast process to go super slow.
I am pretty sure that what you are trying to do is insert rows in tbl_yachts and tbl_yacht_names and you need to get the identity values from the first table. The way to handle this is by using the OUTPUT statement instead of looping like this. There are numerous challenges with the code you posted and switching this to using OUTPUT will make most of it go away.
https://msdn.microsoft.com/en-us/library/ms177564.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 13, 2016 at 11:41 am
I know why do you get an infinite loop. I won't post the solution, because you would keep the loop and ignore Sean's advice.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply