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

1

I have altered existing table to add new Identity column. Then I did a self join to get LocationID that has specific DataSourceID of 62. I need to insert all rows with DataSourceID of 62 into new Identity Column starting with the 6000 number series. There are over 3000 records.

ALTER TABLE dbo.tbl_Location_DataSource
ADD MergeID INT IDENTITY (6000,1)

--Self join table to get the DatasourceID 62
SELECT a.LocationID, b.DataSourceID
From tbl_Location_DataSource a, tbl_Location_DataSource b
Where b.DataSourceID = '62'

I need help to transfer/insert all the locationIDs where DataSourceID=62 into new MergeID column and set the first record of LocationID 6 = MergeID 6000. Additional: any remaining LocationIDs that do not have datasourceID of 62, can stay in the LocationID column. Auto-increment the rest. I can't figure out how. Please and Thank you.

2

I don't think you can do that, the way you have described.

I think you would have to:

Make a new table, with the IDENTITY column

Turn ON the IDENTITY_INSERT for that table:

SET IDENTITY_INSERT MyNewTable ON

INSERT rows into NewTable with the calculated IDENTITY column value WHERE DataSourceID = 62

Turn OFF the IDENTITY_INSERT for that table:

SET IDENTITY_INSERT MyNewTable OFF

Then reset the SEED value of the table to the ID you want to start for all the rest of the rows - e.g.

SELECT MAX(MyIdentityColumn)+1 FROM MyNewTable

DBCC CHECKIDENT (MyNewTable, RESEED, 12345)

Then insert rows into NewTable with WHERE DataSourceID <> 62 OR DataSourceID IS NULL

Then DROP the original table

Then RENAME the NewTable to original table's name.

1 Like
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!

4

:slight_smile: We all had to start somewhere - for me it was back when Dinosaurs roamed the earth!!

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')

My thought was that you will "pull" the data from your existing table at this point e.g.

INSERT INTO Tbl_MyTest_InsertTable
(
       LocationID, DataSourceID, MergeID
)
SELECT LocationID, DataSourceID,
       LocationID + 6000         -- Have I understood correctly this is the value you want for MergeID?
From   tbl_Location_DataSource
Where  DataSourceID = '62'

If you want something different to LocationID + 6000 then please explain what you need. For example, if LocationID has gaps, or doesn't start at "1", and you just want sequence, contiguous, numbers 6000, 6001, ... then SQL can give you a "Row_Number" which you could use instead

SET IDENTITY_INSERT Tbl_MyTest_InsertTable OFF

DECLARE @MaxMergeID int
SELECT @MaxMergeID = MAX( MergeID)+1 FROM Tbl_MyTest_InsertTable

DBCC CHECKIDENT (Tbl_MyTest_InsertTable, RESEED, @MaxMergeID)

next you can insert all the rows that are NOT DataSourceID = '62' e.g.

INSERT INTO Tbl_MyTest_InsertTable
(
       LocationID, DataSourceID     --, MergeID
)
SELECT LocationID, DataSourceID
From   tbl_Location_DataSource
Where  DataSourceID <> '62'
       OR DataSourceID IS NULL

and these will be inserted "after" the DataSourceID = '62' records - i.e. with "higher" MergeID values)

Just in case what you want to do is:

Do you want to import DataSourceID = '62' with a MergeID starting at 6000 but WITHOUT it needing to be equivalent to the LocationID?; and then import the DataSourceID <> '62' with higher MergeID number (but, again, without any association of MergeID value with LocationID value?)

If so then you could do that without having to mess about with setting IDENTITY_INSERT to ON / OFF. Create your new target-table as before:

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

and then insert your data in one batch with the DataSourceID = '62' rows sorted first, so they will get MergeID 6000, 6001, etc. and then sort the remainder (i.e. DataSourceID <> '62' ) rows second. Within those two groups you could sub-sort by, say, LocationID so that the MergeID numbers are allocaed in ascending LocationID order (or any other column(s) that you choose as a Tie Break in the ORDER BY).

INSERT INTO Tbl_MyTest_InsertTable
(
       LocationID, DataSourceID
)
SELECT LocationID, DataSourceID
From   tbl_Location_DataSource
ORDER BY
       CASE WHEN DataSourceID = '62'
            THEN 1 --"First"
            ELSE 2 --"Subsequent"
            END,
      LocationID   -- List any "tie break" sequencing columns, in precedence order
5

Excellent Kristen. Your query produce better results than I could have. The only thing isthe MergeID started at 10255 and not 6000. Everything else worked perfectly. I followed the 1st 4 steps of your code and it started my mergeID col with 10255. I like the mergeIDs at top as there will not be much left to enter. You are wonderful. Thank you again.