SQL 2008 select column to insert into new Identity column in same table

3

Thank you Kristen. This is what I did. This started identity with 6001.

CREATE TABLE Tbl_MyTest_InsertTable
(
	[LocationID] [int] Not Null,
	[DataSourceID] [int],
	[MergeID] [int] IDENTITY(6000,1) Not Null,
)
Go
SET IDENTITY_INSERT Tbl_MyTest_InsertTable ON


INSERT INTO Tbl_MyTest_InsertTable (LocationID, DataSourceID, MergeID)
Values ('6', '6', '6000')

SELECT MAX( MergeID)+1 FROM Tbl_MyTest_InsertTable

SET IDENTITY_INSERT Tbl_MyTest_InsertTable OFF
DBCC CHECKIDENT (Tbl_MyTest_InsertTable)

How do I split the datasourceIDs where only ID=62 to insert into MergeID. Would like to get all the rows from locationID that has datasourceID=62, and insert into the MergeID column. I can only do single insert values.

I did this wrong: "INSERT rows into NewTable with the calculated IDENTITY column value WHERE DataSourceID = 62". I not very great with sql, so your help is much appreciated!