asked on

SQL 2008 create new row in multiple tables

I have an ASP page which checks for the presence of an ID in multiple tables and if not present, inserts a new row. For simplicity, the code looks similar to this...

set dbRS = "select top 1 ID from tableA where ID = @id"
if dbRS.eof then
insert into tableA (id, x, y) values (@id, '@x', '@y')
end if

set dbRS = "select top 1 ID from tableB where ID = @id"
if dbRS.eof then
insert into tableB (id, x, y) values (@id, '@x', '@y')
end if

set dbRS = "select top 1 ID from tableC where ID = @id"
if dbRS.eof then
insert into tableC (id, x, y) values (@id, '@x', '@y')
end if

etc.

This code is repeated for around 25 tables, so each one is checked for the presence of the current ID, and if not present, a new row inserted.

Is there a more elegant way of executing this? I guess an obvious improvement would be to place the table names in to an array and loop through that, but I would still need to be conducting the check on each table and subsequent insert. I'm looking for a performance increase, if there is one, not just reducing lines of code.

Guy Hengel [angelIII / a3]

if you put the list of tables into some config table listing, you could process that dynamically
pseudo-code:

for each tablename in table_listing
   set dbRS = "select top 1 ID from <tablename> where ID = @id"
   if dbRS.eof then
     insert into <tablename> (id, x, y) values (@id, '@x', '@y')
  end if
next

Open in new window

drl1

ASKER

Thanks. That's essentially the array method which will undoubtedly reduce the lines of code, but it doesn't appear to offer any performance gain as it is still checking each table for the ID and if not present, conducting the insert before looping round to check the next table.

I'm looking for improved performance for checking 25 tables instead of doing so one at a time and conducting one insert at a time. I currently make 25 round trips each one involving 1 check and 1 insert. An array or config table, as above, doesn't appear to change that.

Guy Hengel [angelIII / a3]

to avoid the "round trips", you could implement all of this in a stored procedure.
but you won't avoid checking each table unless you can guarantee that either all tables have the ID or none of the tables have the ID; in which case it would be enough to check in 1 table.

RehanYousaf

From the above code it looks like you are checking individual tables and if you want to do in 1 step then probably create a stored procedure and pass the values

In stored procedure you can use the following code

----------------------------------
DECLARE @id INT
DECLARE @x INT
DECLARE @y INT
DECLARE @SQL VARCHAR(MAX)

----------------------------------
SELECT 
	@SQL = COALESCE(@SQL + 'IF NOT EXISTS(SELECT TOP 1 id FROM ' + <table> + ' WHERE id = ''' + CONVERT(VARCHAR, @id) + ''') INSERT INTO ' + <table> + ' VALUES (''' + CONVERT(VARCHAR, @id) + ''', ''' + CONVERT(VARCHAR, @x) + ''', ''' + CONVERT(VARCHAR, @y) + '''); ', 
		'IF NOT EXISTS(SELECT TOP 1 id FROM ' + <table> + ' WHERE id = ''' + CONVERT(VARCHAR, @id) + ''') INSERT INTO ' + <table> + ' VALUES (''' + CONVERT(VARCHAR, @id) + ''', ''' + CONVERT(VARCHAR, @x) + ''', ''' + CONVERT(VARCHAR, @y) + '''); ')
FROM
	<TableName>

EXEC (@SQL )

Open in new window

If you require any help in creating stored procedure do let me know

drl1

ASKER

Thanks. I'm ok for creating Stored Procedures but am unclear on your suggestion, not least the conversion of int variables to varchar. Only ID is an int data type in my example, in fact the other two columns can be forgotten as they're often set to null in some of the tables so inserting a new row with ID only (primary key and in 25 tables) is sufficient to solve my problem. I'll certainly tidy up my code and simply pass an ID in to an SP and let it process the array of tables to perform the insert on.

RehanYousaf

Send me schema of your table and some sample data ... I will send you the stored procedure script

You will also need a table where you store all 25 table names

drl1

ASKER

TableA (id int, x varchar(255), y varchar(255))
TableB (id int, x varchar(255), y varchar(255))
TableC (id int, x varchar(255), y varchar(255))
TableD (id int, x varchar(255), y varchar(255))
TableE (id int, x varchar(255), y varchar(255))
etc.

id field is primary key in all but two of the 25 tables, those being tables where a particular id can have more than one row. Nonetheless, if no row exists in any of the 25 tables for the current id, then my batch process would need to insert one row per table.

RehanYousaf

What happens if id exists in TableA but not in rest?

drl1

ASKER

This would indicate a previous batch insert that failed part-way through and so the insert in to the other tables (TableB, TableC etc) needs to proceed to populate the rows which should have been done first time round. This is in fact one of the issues that sometimes occurs with the current ASP application in that it will create some of the rows but for whatever reason, does not complete the insertion in to all tables.

drl1

ASKER

Sorry, let me clarify that further, if ID exists in TableA but not in the other tables, this suggests a previous part processed batch insert. That would need tidying up, but that is separate to what I'm looking to achieve here.

I should have mentioned that before all of the inserts and checks above there is first of all a statement to set the value of ID, quite simply as follows:

set tempID = select top 1 ID from TableA order by ID desc
ID = tempID+1

So the current ID to be processed is first assigned by incrementing by +1 the highest ID value already allocated in TableA. I know, it should be using IDENTITY but that's a separate issue!

RehanYousaf

So according to your explanation if 100 is last id in TableA and now I want to insert new id ... it is going to be 101 ...
since it is not in TableA it will get inserted
and if it is in TableB .... it will get ignored
and if it is not in TableC ... it will be inserted
and so on ....

drl1

ASKER

Yes, the new ID in that case would be 101. It is not possible, or at least highly unlikely, for that new ID (derived from incrementing the highest value from TableA) to exist in TableB, TableC etc. as that would suggest a previous process having been run using ID 101 in which case 101 would already be in TableA and so the new ID would need to be 102, which is then inserted in to tables A, B, C......X, Y, Z etc.

RehanYousaf

It is probably highly unlikely but I am just trying to understand the logic

If 101 does exist in TableB but not in TableA and TableC will it get inserted into TableA and TableC?

RehanYousaf

Because according to your script on top... 101 will get inserted in TableA and TableC

but will be ignored in TableB

drl1

ASKER

Currently it would not insert, and would simply skip to the next table check as per the original code above.

RehanYousaf

Well looks to me your code individually checking the id on each table which means that id will get inserted in those table where it does not already exist and will be skipped where it does

drl1

ASKER

That is correct, so if an ID is present it would not insert in to the current table and would skip to the next table, as each table check/insert is wrapped in an independent if statement.

RehanYousaf

and you want same but in stored procedure?

drl1

ASKER

No, I can do that easily enough. My original question was really just to see if there was a more efficient way of performing each check/insert (in my ASP page) than performing 25 round trips to the db, but I guess there isn't really so all I can do is shorten my code using a loop.

Alpesh Patel

sp_msforeachtable 'if not exists(select * from ''?'' where id = @id) insert into ''?'' select * from ''?'' where id = @id'

RehanYousaf

I think we are just running in circles ... what I meant by creating a stored procedure was that what you want is to pass the value once and then let stored procedure handle all the inserts

ASKER CERTIFIED SOLUTION

RehanYousaf

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial