January 25, 2015 at 7:29 pm
hi,
i have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now i have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
thanks
January 26, 2015 at 12:03 am
CREATE TABLE #Table
(
ID INT IDENTITY
,x INT
);
SET IDENTITY_INSERT #Table ON;
INSERT INTO #Table (ID, x)
SELECT 0, 0 UNION ALL SELECT 0, 0;
SELECT *
FROM #Table;
-- You can't do this
WITH a AS
(
SELECT ID, n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM #Table
WHERE ID = 0
)
UPDATE a
SET ID = n;
GO
-- But you can try this
DBCC CHECKIDENT (#Table, RESEED, 0);
SET IDENTITY_INSERT #Table OFF;
INSERT INTO #Table (x)
SELECT x
FROM #Table;
DELETE FROM #Table
WHERE ID = 0;
SELECT *
FROM #Table;
SET IDENTITY_INSERT #Table OFF;
GO
DROP TABLE #Table;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 28, 2015 at 3:19 am
SET IDENTITY_INSERT tablename ON
insert into tablename
(
columns
)
select
columns
from tablename
SET IDENTITY_INSERT tablename OFF
you can try this
January 28, 2015 at 7:13 am
If the table only contains the 50 rows you inserted or you want to reset all the ID values
ALTER TABLE
DROP COLUMN ID
GO
ALTER TABLE
ADD ID int IDENTITY(1,1) NOT NULL
GO
Far away is close at hand in the images of elsewhere.
Anon.
January 28, 2015 at 4:39 pm
David Burrows (1/28/2015)
If the table only contains the 50 rows you inserted or you want to reset all the ID values
ALTER TABLE
DROP COLUMN ID
GO
ALTER TABLE
ADD ID int IDENTITY(1,1) NOT NULL
GO
David - that's a very clever alternative!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 29, 2015 at 2:00 am
dwain.c (1/28/2015)
David - that's a very clever alternative!
Thank you :blush:
It is very annoying that you can use ALTER COLUMN to do everything except add or remove IDENTITY 🙁
Far away is close at hand in the images of elsewhere.
Anon.
January 29, 2015 at 1:03 pm
David Burrows (1/29/2015)
dwain.c (1/28/2015)
David - that's a very clever alternative!Thank you :blush:
It is very annoying that you can use ALTER COLUMN to do everything except add or remove IDENTITY 🙁
True, and that you can't update it. But you have to remember what an IDENTITY value is -- it's a physical insert count. Thus, it's not consistent with that data to allow it to be updated. It's technically not consistent to allow it to be inserted either, but I'm glad MS lets us slide by on that one :-).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply