?
Solved

Insert into table with no columns

Posted on 2009-05-01
18
Medium Priority
?
789 Views
Last Modified: 2012-08-14
I've got a simple table with one column, which is only an identity column that ties other tables together.

Collections
- CollectionId

I simply want to insert a row and get the key it generates. Is there any way to do this? I cannot specify the key because it is an identity column... and I'm not sure how to retrieve the key.

I am using MSSQL 2005.
0
Comment
Question by:veruthandai
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 24280165
You can try:

INSERT INTO Collections
DEFAULT VALUES;

SELECT SCOPE_IDENTITY();
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24280194
Hello veruthandai,

insert into YourTable DEFAULT VALUES
select SCOPE_IDENTITY()

Regards,

Aneesh
0
 

Author Comment

by:veruthandai
ID: 24280207
That actually works to insert a row, but the scope_identity part gives me errors.
I'm very curious if you would be willing to elaborate more on "SCOPE_IDENTITY()". What does it return? How would I be able to use that?
Is there any method of using this kind of command in C# code? Ideally what I aim to do is generate IDs so that I can bind other things to them and use the Collection table as the binding point.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24280234
It returns the ID of the row that was just inserted.  That will work if the CollectionID column is setup as an autogenerated identity field.

Here is a test.
DECLARE @Collections table (CollectionID INT IDENTITY(1,1) PRIMARY KEY)
 
INSERT INTO @Collections
DEFAULT VALUES;
 
SELECT SCOPE_IDENTITY();
 
INSERT INTO @Collections
DEFAULT VALUES;
 
SELECT SCOPE_IDENTITY();

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24280235
Try this one out:

set identity_insert schema_name.table_name on;

insert into table(colname) values();

set identity_insert schema_name.table_name off;
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24280292
Since you only have one column, I am not sure this extra table does you any good OR at least doesn't give you any added benefit over having one table use its own primary key and another reference a foreign key on first table.

If you are looking to associate the tables without having a foreign key in either, then you would want a table with structure like this:

Collections:
CollectionID INT IDENTITY(1,1)
TableA_ID INT
TableB_ID INT

The CollectionID can be omitted since you have other two that uniquely identify each row OR leave it so you can refer to the grouping by CollectionID and find the associated records.  Just up to what you are trying to do at the application level in C#.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24280304
I would rather recommend having a table with a single entry

create table GenTable ( i int )
insert into GenTable select '1'
GO
Create proc dbo.RetNextVal
as
declare @nextVal int
Update GenTable
SET   @NextVal = I + 1 ,
      i        = @nextVal

SELECT @nextVal

go

EXEC dbo.RetNextVal
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24280348
Here is more information on SCOPE_IDENTITY from Microsoft:
http://msdn.microsoft.com/en-us/library/ms190315.aspx

A second suggestion of mine was going to be like rrjegan17; however, note that it is not optimal since you can end up with collisions based on multiple instances of application layer unless you do locking to prevent duplicate key insertion/usage.


DECLARE @newID INT
 
SELECT @newID = ISNULL(MAX(CollectionID), 0) + 1
FROM Collections;
 
SET IDENTITY_INSERT Collections ON;
 
INSERT INTO Collections VALUES(@newID);
 
SET IDENTITY_INSERT Collections ON;
 
-- return id inserted to application as scalar result
SELECT @newID AS id;

Open in new window

0
 

Author Comment

by:veruthandai
ID: 24280350
[quote]
The CollectionID can be omitted since you have other two that uniquely identify each row OR leave it so you can refer to the grouping by CollectionID and find the associated records.  Just up to what you are trying to do at the application level in C#.
[/quote]
Well, that's hard to explain. I am tyring to create a model that is bound by a single table instead of a column in each row.
Units
- CollectionId
Tokens
- CollectionId
Tags
- CollectionId
Keys
- CollectionId
 
Each of them will reference a single ID and this will bind them all together.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24280618
veruthandai,

check my last post, that wont keep the table growing

Aneesh
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24280689
Again, if the value doesn't need to be maintained in the original table, then what I was saying before is why not just make the Units table (for example since that is first on list) have primary key of CollectionID.

DECLARE @CollectionID INT

INSERT INTO Units
DEFAULT VALUES;

SELECT @CollectionID = SCOPE_IDENTITY();

INSERT INTO Tokens(CollectionID)
VALUES(@CollectionID)

INSERT INTO Tags(CollectionID)
VALUES(@CollectionID)

INSERT INTO Keys(CollectionID)
VALUES(@CollectionID)

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24280701
Ultimately, you can also set the primary key to be cascaded to the other tables OR be a unique foreign key.
0
 

Author Comment

by:veruthandai
ID: 24280733
It is working that way - the problem isn't the design, it's merely my understanding of SQL code.
Here, have a look. It might make more sense like this. :)
 
I appreciate all the input by the way. This is all very helpful information.

BasicSchema.jpg
0
 

Author Comment

by:veruthandai
ID: 24280757
So then my idea is that something like this...
 
will let me construct things with relationships and properties assigned by keys and tags.
Though I'm still trying to figure out the code.  I'm pretty new at this, this is mostly a design concept to try and dive in further.

			// Create a Context of the data models.
			ModelsDataContext Database = new ModelsDataContext();
 
			Unit newUnit = new Unit();
			Key newKey = new Key();
 
			var elementid = from i in Database.Elements
							   where i.Name == "Name"
							   select i.ElementId;
 
			newKey.ElementId = Int32.Parse(elementid.ToString());

Open in new window

0
 

Author Comment

by:veruthandai
ID: 24280770
@mwvisa1
I'm sorry, I think that's a little beyond the scope of my experience level. I'm not certain what you mean.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24281535
:)

Looks like you got it.  The visual helped.

Good luck and happy coding.

Regards,
Kevin
0
 

Author Comment

by:veruthandai
ID: 24281646
Am I taking the right approach, or is this just way too far out there?
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question