Hi ,

i have these two tables

create table [dbo].[test1](
	[test1_id] [int] identity(1,1) primary key,
	[test2_id] [int] not null
)

create table [dbo].[test2](
	[test2_id] [int] identity(1,1) primary key,
	[test1_id] [int] not null
	)
	

alter table [dbo].[test1] 
add  constraint [fk_test1_test2_id] foreign key([test2_id])
references [dbo].[test2] ([test2_id])

alter table [dbo].[test2] add  constraint [fk_test2_test2_id] foreign key([test1_id])
references [dbo].[test1] ([test1_id])

I want to insert the data into two tables in one insert statement. How can i do this using T-SQL ?

Thanks in advance.

Hello,

In one INSERT statement you can only address one table; you have to write 2 separate INSERT statements.

See MSDN INSERT (Transact-SQL) for the syntax and also INSERT Examples (Transact-SQ

In one insert  you cannot, if you want to 'hide' one insert, use a trigger FOR insert on test1 table to insert the data into test2 table.

Hmm..circular reference with not null columns?

This wont work if columns are not null

Even otherwise it cant be done in a single statement as feature like OUTPUT clause wont work if target table has FK relationship on it

Can I ask why you've circular relationship as above?

You can INSERT into both tables within one Transaction but not in one statement. By the way, you would need to alter your dbo.Test1 table to allow null for first INSERT test2_id column

See sample code below:

CREATE TABLE #test1(test1_ID INT IDENTITY(1,1),test2_id INT NULL)
CREATE TABLE #test2(test2_ID INT IDENTITY(1,1),test1_ID INT)

DECLARE @Test1dentity INT
DECLARE @Test2dentity INT

BEGIN TRAN
	-- Insert NULL as test2_ID value is unknown
	INSERT INTO #test1(test2_ID)
	SELECT NULL;

	-- get inserted identity value
	SET @Test1dentity = SCOPE_IDENTITY();

	INSERT INTO #test2(test1_ID)
	SELECT @Test1dentity;

	-- get inserted identity value 
	SET @Test2dentity = SCOPE_IDENTITY();

	-- Update test1 table
	UPDATE #test1
		SET test2_ID = @Test2dentity
	WHERE test1_ID = @Test1dentity; 
COMMIT

SELECT * FROM #test1;
SELECT * FROM #test2;

-- Drop temp tables
IF OBJECT_ID('tempdb..#test1') IS NOT NULL
BEGIN
DROP TABLE #test1
END
IF OBJECT_ID('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END