Solved

SQL 2008 create new row in multiple tables

Posted on 2013-01-10
22
253 Views
Last Modified: 2013-01-11
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.
0
Comment
Question by:drl1
  • 10
  • 9
  • 2
  • +1
22 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38762034
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

0
 

Author Comment

by:drl1
ID: 38762097
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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38762107
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.
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38762662
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
0
 

Author Comment

by:drl1
ID: 38764062
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.
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38764463
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
0
 

Author Comment

by:drl1
ID: 38764511
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.
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38764587
What happens if id exists in TableA but not in rest?
0
 

Author Comment

by:drl1
ID: 38764611
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.
0
 

Author Comment

by:drl1
ID: 38764657
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!
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38764686
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 ....
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:drl1
ID: 38764705
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.
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38764726
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?
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38764733
Because according to your script on top... 101 will get inserted in TableA and TableC

but will be ignored in TableB
0
 

Author Comment

by:drl1
ID: 38764744
Currently it would not insert, and would simply skip to the next table check as per the original code above.
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38764793
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
0
 

Author Comment

by:drl1
ID: 38764882
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.
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38764905
and you want same but in stored procedure?
0
 

Author Comment

by:drl1
ID: 38765033
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.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38765866
sp_msforeachtable 'if not exists(select * from ''?'' where id = @id) insert into ''?'' select * from ''?'' where id = @id'
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38766264
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
0
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 500 total points
ID: 38766363
for solution first you will have to create a table where you store table names of all 25 tables

CREATE TABLE [dbo].[TableName](
	[Name] [varchar](255) NULL
) 

Open in new window


then create a stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Rehan Yousaf
-- Create date: 2013-01-11
-- Description:	Insert data in multiple rows
-- =============================================
CREATE PROCEDURE [dbo].[InsertID] 
	@id INT, 
	@x VARCHAR(255),
	@y VARCHAR(255)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL VARCHAR(MAX)
	
	SELECT 
		@SQL = COALESCE(@SQL + 'IF NOT EXISTS(SELECT TOP 1 id FROM ' + Name + ' WHERE id = ' + CONVERT(VARCHAR(10), @id) + ') INSERT INTO ' + Name + ' VALUES (' + CONVERT(VARCHAR(10), @id) + ', ''' + @x + ''', ''' + @y + '''); ', 
			'IF NOT EXISTS(SELECT TOP 1 id FROM ' + Name + ' WHERE id = ' + CONVERT(VARCHAR(10), @id) + ') INSERT INTO ' + Name + ' VALUES (' + CONVERT(VARCHAR(10), @id) + ', ''' + @x + ''', ''' + @y + '''); ')
	FROM
		TableName

	PRINT (@SQL )
	EXEC (@SQL )

END
GO

Open in new window


after that you can use the stored procedure to enter data
EXEC [dbo].[InsertID]
		@id = 101,
		@x = 'x data',
		@y = 'y data'

Open in new window


Hope this helps ... if not then we can modify the script
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now