| Author |
Topic |
|
chwanhun
Starting Member
22 Posts |
 Posted - 2008-09-11 : 12:40:44
|
| I was able to use the IDENTITY function once on an empty table that already existed but I get a message saying the object (table) already exists when I try and do it again. I had to remove the data from my tables and reimport more datafiles and thought I would be able to do it the same way. I need to use IDENTITY to populate a Key column sequentially. Has anyone else ran into this issue? How did you work around it?Any help is much appreciated.Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chwanhun
Starting Member
22 Posts |
Posted - 2008-09-11 : 14:03:43
|
| Error:Msg 2714, Level 16, State 6, Line 3There is already an object named 'Districts' in the database.Code:SELECT IDENTITY(INT,1,1) AS D_Key, C_Key, D_Num, D_Name, D_AddressINTO D_Table2The code worked the first time it was executed. I understand the error, but am unsure of why it worked the first time. I would also like to know an alternate way of generating sequential numbers.Thanks!FROM D_Table1 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-11 : 14:10:42
|
| You haven't shown the full code as Districts isn't anywhere in your posted code.What are you trying to solve? If you just want an identity column on a table, then modify the table. Then on inserts, don't specify the column in the column list and SQL Server will manage it for you.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
chwanhun
Starting Member
22 Posts |
Posted - 2008-09-11 : 14:20:35
|
| Error:Msg 2714, Level 16, State 6, Line 3There is already an object named 'Table2' in the database.Code:SELECT IDENTITY(INT,1,1) AS D_Key,C_Key,D_Num,D_Name,D_AddressINTO D_Table2FROM D_Table1I need to populate a table based on results from a query. If I do: INSERT INTO table2SELECT IDENTITY(INT,1,1) AS D_Key,C_Key,D_Num,D_Name,D_AddressFROM D_Table1It gives me the error: The IDENTITY function can only be used when the SELECT statement has an INTO clause. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-11 : 14:35:43
|
| Run a DROP command before the SELECT/INTO/FROM. Or you could get fancier with IF EXISTS to run the SELECT/INTO/FROM if it doesn't exist and then INSERT/SELECT if it does exist.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
chwanhun
Starting Member
22 Posts |
Posted - 2008-09-11 : 14:46:08
|
| I really didn't want to drop my table because it contains constraints and I'll have to create the table structure again. Not to mention all of the other tables it references. So, there is no way I can use IDENTITY function to INSERT rows in a pre-existing table with a SELECT INTO statement? If I can't use IDENTITY is there any other way to generate sequential numbers as it's inserting into a table? Also, does anyone know why the IDENTITY function worked once, but after I deleted info from the table, it didn't the second time? The table was always there...Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-11 : 14:57:37
|
| You can only use SELECT INTO if the table doesn't exist. But just don't use SELECT INTO! Run a regular INSERT command and SQL Server will manage the autonumber for you. If you are going to supply the autonumber value from another table, then you must use the IDENTITY_INSERT option. If you want SQL Server to manage it, then just exclude the column from your column list.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
chwanhun
Starting Member
22 Posts |
Posted - 2008-09-11 : 15:06:20
|
| Duh! :) It worked this time.Thanks Tara. |
|
|
|
|
INSERT INTO Identity