Click here to Skip to main content
15,918,976 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Dear Friends,

Can anyone guide me for this following query.

In my table i am having one column named ID(non Primary Key) which contains the values like 1,2,3,4,5 etc. If i am trying to insert 4 again that column means then new 4 should insert and old 4 should replace by 5, old 5 sholud be replaced by 6 like this orderly.

Can any one please provide the sql server query.

I need immediately.
Posted
Updated 4-Jun-13 23:46pm
v2
Comments
gvprabu 5-Jun-13 5:47am    
give your table structure, U need to do this in single statement or u want to use SP.
If you will keep the newly inserted value (4) in one variable then ite easy.

Hi ShanmugaPriya,

Try this one.

First, check if there exists any records which have ID equal or greater than 4. If so, then increment their ID values by 1. Then insert the record where its ID = 4.

SQL
BEGIN TRANSACTION TRASN_Insert
BEGIN TRY
	IF EXISTS (SELECT ID FROM TableName WHERE ID >= 4)
	BEGIN
		UPDATE TableName
		SET ID = ID + 1
		WHERE ID >= 4
	END

	INSERT INTO TableName
	(ID)
	VALUES
	(4)

	COMMIT TRANSACTION TRASN_Insert
	
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION TRASN_Insert
END CATCH



Its protected with a transaction and Try Catch for failure cases also.

Thank you,
RelicV.
 
Share this answer
 
Comments
gvprabu 5-Jun-13 6:06am    
Nice Solution, so my +5 :-)
RelicV 5-Jun-13 7:07am    
Thank you gvprabu. Have a nice day.!
SQL
update temp1
set id = id + 1
where id > 3 

SQL
insert into TEMP1 (ID)  values (4);


Note : Values 3 and 4 are to be replaced programmatically
 
Share this answer
 
Hi,

you need to perform an update before you do the insert.

SQL along the following line would acheive this.

SQL
DECLARE @NewId INT
SET @NewId = 4

UPDATE dbo.MyTable
SET
  Id = Id + 1
WHERE
  Id >= @NewId

INSERT INTO dbo.MyTable (Id) VALUES (@NewId)
 
Share this answer
 
Comments
gvprabu 5-Jun-13 6:05am    
hi friend,
we both are have same solution :-)
Hi ,

Try like this....
SQL
DECLARE @NewID INT 
SELECT @NewID = 4

UPDATE ID=ID+1
FROM table_Name
WHERE ID >= @NewID 

-- Insert New Record
Insert table_Name(ColumnList) values(Details) -- ID(4)

Regards,
GVPrabu
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900