April 13, 2012 at 12:16 am
Hi guys,
I have a table Devices and i am copying the structure of the table to temp table for further process,
i want to disable or remove the identity for the column
below query will illustrate my problem
IF NOT Object_ID('tempdb..#TempTest') IS NULL
BEGIN
DROP TABLE #TempTest
END
SELECT Top 0 * INTO #TempTest FROM Devices
TRUNCATE TABLE #TempTest
if @value = 1
BEGIN
INSERT INTO #TempTest SELECT * FROM Devices where condition1......
-- calculation 1
END
ELSE if @value = 2
BEGIN
INSERT INTO #TempTest SELECT * FROM Devices where condition2......
-- calculation 2
END
ELSE
BEGIN
INSERT INTO #TempTest SELECT * FROM Devices where condition3......
-- calculation 3
END
the result for the above query is
(0 row(s) affected)
Server: Msg 8101, Level 16, State 1, Line 12
An explicit value for the identity column in table '#TempTest' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Please suggest some better solution
how do i overcome with this problem...
Thanks
Patel Mohamad
April 13, 2012 at 1:29 am
Drop the identity column and add a simple integer column with the same name.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2012 at 4:04 am
Koen Verbeeck (4/13/2012)
Drop the identity column and add a simple integer column with the same name.
Hi Koen,
Please read my question then suggest some better solution,
before posting My question on Sqlservercenteral.com i have already tried many of the alternate things,
but no luck.
Patel Mohamad
April 13, 2012 at 4:18 am
patelmohamad (4/13/2012)
before posting My question on Sqlservercenteral.com i have already tried many of the alternate things,but no luck.
In that case, please will you post everything you've done so far, with an explanation of why it doesn't work for you. That way we won't waste our time and yours suggesting things you've already tried.
Thanks
John
April 13, 2012 at 4:30 am
patelmohamad (4/13/2012)
Koen Verbeeck (4/13/2012)
Drop the identity column and add a simple integer column with the same name.Hi Koen,
Please read my question then suggest some better solution,
There isn't a 'better suggestion', you cannot drop the identity property from a column, the only way to get rid of it is to drop the column. If you need to preserve the values, then create a new int column, update to set the values same as the identity column, then drop the identity column.
Or you can just not create the column as identity in the first place, use CREATE TABLE, not SELECT ... INTO to create it.
If you just want to insert a couple of explicit values, not completely remove the identity column, then the error message you posted tells you exactly what you need to do.
An explicit value for the identity column in table '#TempTest' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 13, 2012 at 4:44 am
patelmohamad (4/13/2012)
Please read my question then suggest some better solution,
I did read the question. Do you think I randomly type stuff in forum topics?
I did have to guess on which table you wanted the identity removed: the Devices or the temp table.
I guessed the temp table (makes most sense to me since you are trying to put data in it), so my solution works and it is quick. I see no disadvantages, as you create the temp table with SELECT TOP 0 ... INTO, so there is no data loss.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2012 at 5:15 am
Koen Verbeeck (4/13/2012)
patelmohamad (4/13/2012)
Please read my question then suggest some better solution,I did read the question. Do you think I randomly type stuff in forum topics?
I did have to guess on which table you wanted the identity removed: the Devices or the temp table.
I guessed the temp table (makes most sense to me since you are trying to put data in it), so my solution works and it is quick. I see no disadvantages, as you create the temp table with SELECT TOP 0 ... INTO, so there is no data loss.
Agree with you koen but the column indexing will be lost in it and in my next step i have used the same statement but with where condition and i have lot of calculation and then the result.
so your suggestion wont be lucky to me 🙁 so any other better solution.
Thanks
Patel Mohamad
April 13, 2012 at 5:22 am
patelmohamad (4/13/2012)
Agree with you koen but the column indexing will be lost in it and in my next step i have used the same statement but with where condition and i have lot of calculation and then the result.
so your suggestion wont be lucky to me 🙁 so any other better solution.
Thanks
A SELECT ... INTO doesn't transfer/create any indexes, so I'm not sure where you are heading at.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2012 at 5:30 am
Koen Verbeeck (4/13/2012)
patelmohamad (4/13/2012)
Agree with you koen but the column indexing will be lost in it and in my next step i have used the same statement but with where condition and i have lot of calculation and then the result.
so your suggestion wont be lucky to me 🙁 so any other better solution.
Thanks
A SELECT ... INTO doesn't transfer/create any indexes, so I'm not sure where you are heading at.
Hi Koen
IF NOT Object_ID('tempdb..#TempTest') IS NULL
BEGIN
DROP TABLE #TempTest
END
SELECT Top 0 * INTO #TempTest FROM Devices
TRUNCATE TABLE #TempTest
INSERT INTO #TempTest SELECT * FROM Devices where condition1......
What should i do in below condition
INSERT INTO #TempTest SELECT * FROM Devices where condition1......
?
As you suggested me to drop the identity column and then regenerate the column,
but in Select statement i am not specifying the field names , here my column index totally messed...
Thanks
Patel Mohamad
April 13, 2012 at 5:42 am
What do you mean with column index?
You can either specify the columns names, or just add two clauses: ALTER TABLE #TempTest DROP COLUMN ID and ALTER TABLE #TempTest ADD ID INT.
(I assumed the column name ID for the identity column, as I don't know the DDL of the Devices table)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2012 at 5:59 am
Koen Verbeeck (4/13/2012)
What do you mean with column index?You can either specify the columns names, or just add two clauses: ALTER TABLE #TempTest DROP COLUMN ID and ALTER TABLE #TempTest ADD ID INT.
(I assumed the column name ID for the identity column, as I don't know the DDL of the Devices table)
Hi Koen
my table structure is like
DeviceId
DeviceRegistrationId
DeviceName
DeviceMobileNo
DeviceModelId
....
...
...
...
CreationDate
totaly 42 columns
if i drop the column DeviceID and recreate it then the my table structure will be
DeviceRegistrationId
DeviceName
DeviceMobileNo
DeviceModelId
....
...
...
...
CreationDate
DeviceId
and in my procedure i have used SELECT * INTO #TempTable FROM Devices or
INSERT INTO #TempTable SELECT * FROM Devices
so how do i overcome with the issue.?
Patel Mohamad
April 13, 2012 at 6:02 am
I don't see the issue.
How does the order of columns matter? It's just a temp table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2012 at 6:07 am
patelmohamad (4/13/2012)
and in my procedure i have used SELECT * INTO #TempTable FROM Devices orINSERT INTO #TempTable SELECT * FROM Devices
so how do i overcome with the issue.?
By enumerating the columns in your SELECT statement. This is one of the reasons why SELECT * is considered bad practice. If you insist on using it, you'll have to use a CREATE TABLE statement to create the table with the columns in the correct order.
John
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply