0

I have the following tables for example:

  • Table 1: id, name
  • Table 2: id, school, t1Id

The requirement is:

  • firstly select t1id from Table1 where name = @name
  • If the record exists, insert Table2 value (@school,t1id)
  • If the record doesn't exists, insert Table1 value(@name) and then return the id of this new-inserted record to execute insert Table2 value (@school,t1id)

My question is:

How to achieve this with one SQL statement or transaction without using stored procedure?

I'm using Spring but right now I separate the executions of these SQL statements and interact with database too many times.

So I want to do that in one execution.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
macemers
  • 2,194
  • 6
  • 38
  • 55

2 Answers2

1

You can execute code block to check exist condition without stored procedure like below. You can also insert data into multiple tables in one statement like mentioned in below link (It have same case like your) :

How can I INSERT data into two tables simultaneously in SQL Server?

IF EXISTS(select t1id from Table1 where name = @name)
BEGIN
      insert into Table2 
      SELECT @school,t1id from Table1 where name = @name
END
ELSE
BEGIN
      insert into Table1 values (@name);
      insert into Table2
      SELECT @school,SCOPE_IDENTITY()
END
Community
  • 1
  • 1
Upendra Chaudhari
  • 6,473
  • 5
  • 25
  • 42
  • Thx for the codes, but do you know how to integrate it with Spring JdbcDaoSupport? Because I need to do that in the context of Spring – macemers Feb 20 '14 at 05:06
0

You should be able to use the IF EXISTS command to help you with this. Here is link to an MSDN article about it. Your situation is slightly different, but the technique should still work. http://blogs.msdn.com/b/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx?Redirected=true

Mike Ciance
  • 41
  • 3