Query regarding SQL Insert in SQL Server?

Question

I am using SQL Server 2008 and developing a project which is in maintenance phase.

I want to insert record in a table whose primary key is an Integer but not an identity. e.g. table name is tblFiles and fields are ID, FileName, FileContent.

Actually that table is in use so I don’t want to make any schema change in it. And I want the key after row insertion because I have to put that in another table. Existing values in the Id column are different integer, means not in sequence.

So I want the query that also returns me the Id value. So I want to insert only FileName and FileContent and some sort of sql to whom I can embed in my insert query which insert a unique Id and also send me that id

Answer 1

Well, if it's not an IDENTITY field - don't you already have to specify the "ID" in your insert for it to succeed ? If so - you already have the ID! Or what am I missing? Is the ID determined by a trigger or something??

If so, try this query:

INSERT INTO dbo.tblFiles(FileName, FileContent)
OUTPUT inserted.ID
VALUES ('yourfile.name', 'your contents')

This should return the newly inserted ID from the INSERT query.

Marc

Answer 2

You could create a unique integer, not so elegantly, using

SELECT MAX(ID) + 1 FROM tblFiles

And simply return this from your query or sproc as the case maybe. Otherwise follow as marc_s says if it is known already.

UPDATE: have to say, rather than this fudge as requested, I would strongly recommend pushing back hard and getting table changed so this is an identity column, as this is what is. all answers so far are simply fudges, mine especially.

Answer 3

Change the Columns Identity Specification > Is Identity to Yes.

The after inserting into the table you can

Select SCOPE_IDENTITY()

to get the integer that was just added and return this in your SP.

If you really can't edit the database schema maybe you could add another table to the database that has two columns called ID and CurrentDate. Make the ID column an Identity. In your code insert into this table first select SCOPE_IDENTITY() and then use the integer returned to insert as the ID in your tblFles table.

P.S. Stop prefixing your table with tbl that's so 1999. :)

Answer 4

so my final query look like...

Insert into dbo.tblData (Id, FName, LName) 
output inserted.Id
values ((SELECT MAX(ID) + 1 FROM dbo.tblData), 'xyz', 'abc')