Multiple Inserts

[StevieMars]

Hello,

I want to do two sql INSERTS into the same table (table holds pk and fk rows) but I need the second insert to use the new Identity from the first INSERT. Something like:

INSERT INTO [table]
SELECT [Cols] FROM [Table] Where [blah];SELECT NewUDValue AS @@IDENTITY;
UNION ALL
SELECT [colName = NewUDValue, more cols] FROM [Tablle] Where [Blah]

However this doesn't work! Is this even possible or will I need to split this into 2 seperate calls completly?

Thanks,
Steve

[NeoPa]

I have no idea what concept you're trying to explain with your example SQL (which just appears in so many ways) so I suggest you actually just ask the question in clear English. Always a good idea anyway to be fair.

[StevieMars]

Basically I want to insert two rows into the same table using one SQL call.

The second row to be inserted needs to use the newly created ID (auto generated primary key) from the first row. So I know that I can get the newly created ID from the first call using @@IDENTITY but can I use this in the second Insert statement? If I do 2 completly seperate calls to the database then it's fine but I want to do just one call.

Hope that makes sense...

[NeoPa]

I don't think this can make sense as you describe it Steve.

I'm sort of guessing much as there is still much that has not been properly described or explained, but if you are appending a single pair (WHERE [blah] refers to a single source record) then it may work. If you are expecting WHERE [blah] to cover multiple records then this cannot possibly succeed as @@IDENTITY (or either of its equivalents) can only remember the last one used. By definition all previous ones are lost and then you get a whole bunch of records added with the same ID. Frankly, there is so little info to work from here I cannot even imagine what you're about, selecting a single record from a table then adding back two records to the same table with different, but undetermined values in whatever fields you may know about but haven't shared.

[Oralloy]

What it looks like is that StevieMars wants to insert two rows into his target table, the second of which refers to the auto-generated key of the first.

However, it's really difficult to be sure what problem he's really trying to solve.

Still, the solution that results likely won't be portable SQL. And there is value staying within the standard.

Stevie, can you please tell us what your basic problem is, and not ask about optimized code? Perhaps we can suggest an appropriate solution.

[ck9663]

Stevie,

I'm not sure it's possible for the identity value be available in a single atomic statement. The insert has to be completed first with no (syntax, constraint, etc) error before it becomes available. The reason is, SQL Server internally keeps track of the value of what you just inserted. That's why even if you rollback DML command, you will not be able to reuse the identity that's already been used. Because, internally, it's being monitored.

You're going to have to use a two statements T-SQL. If you need to rollback for any error, use transaction processing and reseed your identity as necessary.

Good Luck!!!

~~ CK

[Jerry Winston]

@Steve

I'm hesitant to post this solution because the details of your requirement are so scant. Although SQL will allow you to create PK/FK constraints on the same table, be careful because you can get into some hairy situations if you're not cautious.

The simplest solution is to create an AFTER INSERT trigger. Of course you can't allow recursive triggers, but you can insert into the same table in the AFTER INSERT trigger with the values from the special inserted object.

CREATE TABLE [dbo].[tbl_SelfRef](
	[PK] [int] IDENTITY(1,1) NOT NULL,
	[RowType] [char](2) NULL,
	[FK] [int] NULL)
--First entry must reference itself
INSERT INTO [dbo].[tbl_SelfRef]
VALUES ('PK',1)

CREATE TRIGGER [dbo].[AutoSetFK] 
   ON  [dbo].[tbl_SelfRef] 
   AFTER INSERT
AS 
BEGIN

	SET NOCOUNT ON;
	INSERT INTO dbo.tbl_SelfRef
	SELECT
	'FK',PK
        FROM inserted

END

INSERT INTO [dbo].[tbl_SelfRef]
VALUES ('PK',1)
INSERT INTO [dbo].[tbl_SelfRef]
VALUES ('PK',2)
INSERT INTO [dbo].[tbl_SelfRef]
VALUES ('PK',2)
INSERT INTO [dbo].[tbl_SelfRef]
VALUES ('PK',4)

So what happens? For every value you insert (a PK value) a correlated "FK" row is added to the table with a FK equal to the PK of the "PK" row you just inserted.

[StevieMars]

Sorry if my explanation was a bit vague.

@jerry Winston you are correct that rows can have a correlated "FK" row with the FK equal to that of the "PK" row inserted before. The scenario isn't applicable to all rows. Basically each row represents a module in my application and I need there to be a relationship between some modules (parent module and child module)

@Ck this was what I was looking for (whether I can get the @@IDENTITY in the same call as the next insert).

I'll try and explain better in future posts - thanks for your help

Steve