Insert into table with no columns

Hello veruthandai,

insert into YourTable DEFAULT VALUES
select SCOPE_IDENTITY()

Regards,

Aneesh

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.

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();

Select allOpen in new window

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;

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#.

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

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;

Select allOpen in new window

[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.

veruthandai,

check my last post, that wont keep the table growing

Aneesh

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)

Ultimately, you can also set the primary key to be cascaded to the other tables OR be a unique foreign key.

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

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());

Select allOpen in new window

@mwvisa1
I'm sorry, I think that's a little beyond the scope of my experience level. I'm not certain what you mean.

:)

Looks like you got it.  The visual helped.

Good luck and happy coding.

Regards,
Kevin

Am I taking the right approach, or is this just way too far out there?