inserting into SQL table with just a primary key column?
-
Hello, is there a way on how to insert a new row into an MS SQL table that only contains an autogenerated primary key column? Thanks, Michal
What are you inserting into the row? You might want to reconsider the design of only having one column and it being auto.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
-
Hello, is there a way on how to insert a new row into an MS SQL table that only contains an autogenerated primary key column? Thanks, Michal
hi Please Explain in Detail on what you want to achieve. Thanks
Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za
-
What are you inserting into the row? You might want to reconsider the design of only having one column and it being auto.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
Paul, thanks for the reply. It's exactly what my design is. There's just one column and it is being autogenerated. Let's say this table is named Table_A. The sole auto int PK column in Table_A is named Table_A_Id. Let's say there's another table, Table_B. This Table_B contains, among other things, a column named Table_A_Id. There is a foreign key relationship between Table_A.Table_A_Id and Table_B.Table_A_Id. By way of this FK relationship, the unique Table_A.Table_A_Id rows are being linked to many Table_B.Table_A.Id rows. Each time I insert a new row into Table_A, I get the last autogenerated PK by issuing SELECT @@IDENTITY. I take this last PK from Table_A and insert multiple rows to Table_B that link to this Table_A.Table_A_Id. The problem is that if Table_A only contains one autogenerated PK column, there's actually nothing to insert (no columns, no values) into Table_A. My current workaround is that I've added a text column named Comment to Table_A. I'm generating a new row in Table_A by issuing INSERT INTO Table_A (Comment) VALUES ('') then. But I'd like to find out how to generate a new row in Table_A with no workarounds. Thanks much, Michal
-
hi Please Explain in Detail on what you want to achieve. Thanks
Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za
Hi, Vuyiswa Maseko, thanks for your reply. I described the coveted structure in my reply to Paul. Any help will be greatly appreciated. Thanks much, Michal
-
Paul, thanks for the reply. It's exactly what my design is. There's just one column and it is being autogenerated. Let's say this table is named Table_A. The sole auto int PK column in Table_A is named Table_A_Id. Let's say there's another table, Table_B. This Table_B contains, among other things, a column named Table_A_Id. There is a foreign key relationship between Table_A.Table_A_Id and Table_B.Table_A_Id. By way of this FK relationship, the unique Table_A.Table_A_Id rows are being linked to many Table_B.Table_A.Id rows. Each time I insert a new row into Table_A, I get the last autogenerated PK by issuing SELECT @@IDENTITY. I take this last PK from Table_A and insert multiple rows to Table_B that link to this Table_A.Table_A_Id. The problem is that if Table_A only contains one autogenerated PK column, there's actually nothing to insert (no columns, no values) into Table_A. My current workaround is that I've added a text column named Comment to Table_A. I'm generating a new row in Table_A by issuing INSERT INTO Table_A (Comment) VALUES ('') then. But I'd like to find out how to generate a new row in Table_A with no workarounds. Thanks much, Michal
Why on earth do you want a table with only an autogenerated column on it? That's just begging for somebody to point out that you've failed the database normalisation test here. Plus, don't use @@IDENTITY to get the identity of a column. Use SCOPE_IDENTITY() instead.
Deja View - the feeling that you've seen this post before.
-
Paul, thanks for the reply. It's exactly what my design is. There's just one column and it is being autogenerated. Let's say this table is named Table_A. The sole auto int PK column in Table_A is named Table_A_Id. Let's say there's another table, Table_B. This Table_B contains, among other things, a column named Table_A_Id. There is a foreign key relationship between Table_A.Table_A_Id and Table_B.Table_A_Id. By way of this FK relationship, the unique Table_A.Table_A_Id rows are being linked to many Table_B.Table_A.Id rows. Each time I insert a new row into Table_A, I get the last autogenerated PK by issuing SELECT @@IDENTITY. I take this last PK from Table_A and insert multiple rows to Table_B that link to this Table_A.Table_A_Id. The problem is that if Table_A only contains one autogenerated PK column, there's actually nothing to insert (no columns, no values) into Table_A. My current workaround is that I've added a text column named Comment to Table_A. I'm generating a new row in Table_A by issuing INSERT INTO Table_A (Comment) VALUES ('') then. But I'd like to find out how to generate a new row in Table_A with no workarounds. Thanks much, Michal
Is there also a Table_C which has as a column, Table_C.Table_A_Id? I've come across this a few times and other than adding an extra column as you suggest, you can also go without using Auto. But instead you write a trigger that peforms the logic that an Auto would do you for you normally.
Chris Meech I am Canadian. [heard in a local bar] Donate to help Conquer Cancer[^]
-
Why on earth do you want a table with only an autogenerated column on it? That's just begging for somebody to point out that you've failed the database normalisation test here. Plus, don't use @@IDENTITY to get the identity of a column. Use SCOPE_IDENTITY() instead.
Deja View - the feeling that you've seen this post before.
Pete O'Hanlon wrote:
Why on earth do you want a table with only an autogenerated column on it?
He explained it to me in his reply to my post. But he still makes no sense and yes, he really blew the database normalization here.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
-
Paul, thanks for the reply. It's exactly what my design is. There's just one column and it is being autogenerated. Let's say this table is named Table_A. The sole auto int PK column in Table_A is named Table_A_Id. Let's say there's another table, Table_B. This Table_B contains, among other things, a column named Table_A_Id. There is a foreign key relationship between Table_A.Table_A_Id and Table_B.Table_A_Id. By way of this FK relationship, the unique Table_A.Table_A_Id rows are being linked to many Table_B.Table_A.Id rows. Each time I insert a new row into Table_A, I get the last autogenerated PK by issuing SELECT @@IDENTITY. I take this last PK from Table_A and insert multiple rows to Table_B that link to this Table_A.Table_A_Id. The problem is that if Table_A only contains one autogenerated PK column, there's actually nothing to insert (no columns, no values) into Table_A. My current workaround is that I've added a text column named Comment to Table_A. I'm generating a new row in Table_A by issuing INSERT INTO Table_A (Comment) VALUES ('') then. But I'd like to find out how to generate a new row in Table_A with no workarounds. Thanks much, Michal
You really should redesign your tables, because like Pete said, this is a failure at database normalization.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
-
Paul, thanks for the reply. It's exactly what my design is. There's just one column and it is being autogenerated. Let's say this table is named Table_A. The sole auto int PK column in Table_A is named Table_A_Id. Let's say there's another table, Table_B. This Table_B contains, among other things, a column named Table_A_Id. There is a foreign key relationship between Table_A.Table_A_Id and Table_B.Table_A_Id. By way of this FK relationship, the unique Table_A.Table_A_Id rows are being linked to many Table_B.Table_A.Id rows. Each time I insert a new row into Table_A, I get the last autogenerated PK by issuing SELECT @@IDENTITY. I take this last PK from Table_A and insert multiple rows to Table_B that link to this Table_A.Table_A_Id. The problem is that if Table_A only contains one autogenerated PK column, there's actually nothing to insert (no columns, no values) into Table_A. My current workaround is that I've added a text column named Comment to Table_A. I'm generating a new row in Table_A by issuing INSERT INTO Table_A (Comment) VALUES ('') then. But I'd like to find out how to generate a new row in Table_A with no workarounds. Thanks much, Michal
Table_A contains no data. All it contains is a surrogate key, a key to look up nothing. If you really need the list of values that you expect in Table_A, then a select distinct(Table_A_Id) from Table_B will serve its purpose. If you want to constrain the column in Table_B to a set of values, then use a constaint.
Mark Churchill Director Dunn & Churchill Diamond Binding: Zero to Data Layer in 3 mins
-
Hi, Vuyiswa Maseko, thanks for your reply. I described the coveted structure in my reply to Paul. Any help will be greatly appreciated. Thanks much, Michal