Perform "smart" INSERT query on multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Perform "smart" INSERT query on multiple tables

Author

Topic

overbored
Starting Member

12 Posts

Posted - 2002-01-17 : 18:15:05

I log visitors in a database that has two tables: Hits and IPs (this is just a simplification). Hits has three columns: hit_id (identity int), hit_time (datetime), and ip_id (int). The last one is related to the IPs table, which has columns: ip_id and ip_address (varchar(16)).

Currently, I use this stored procedure for logging to simplify things on the ASP side:

>>>

CREATE PROCEDURE insert_hit(@ip_address varchar(16))
AS
DECLARE
@cur CURSOR,
@ip_id int

-- Get the IP ID
SET @cur = CURSOR SCROLL DYNAMIC FOR
SELECT ip_id FROM IPs
WHERE ip_address = @ip_address

OPEN @cur
FETCH FIRST FROM @cur INTO @ip_id

IF @@FETCH_STATUS = -1
BEGIN
INSERT INTO IPs (ip_address) VALUES (@ip_address)
FETCH FIRST FROM @cur INTO @ip_id
END
CLOSE @cur

-- Insert row into Hits
INSERT INTO Hits (ip_id, hit_time) VALUES (@ip_id, GETDATE())

>>>

Now, I'm not having any problems with this script, but as you can see, it's extremely tedious - especially with the various other related data being logged. Moreover, this doesn't seem to me like an unusual case; I figured this is a major aspect of the "R" in "RDBMSs."

My question is: Is there a way to perform a "smart" INSERT statement? That is, one which will automatically insert passed data into a separate table if it isn't there, then use the respective ID from that entry as the value for the related field in the main table?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-17 : 18:29:51

Overboard,

It seems that you are only using the cursor for an existence check..

Try This


CREATE PROCEDURE insert_hit(@ip_address varchar(16))
AS
set nocount on
declare @IP_ID int
--Check for a record
IF NOT EXISTS (Select 1 from Ips where ip_address = @ip_address)
BEGIN
	--Doesn't exists so add it
	INSERT INTO IPs (ip_address) VALUES (@ip_address)
END
--Retrieve ID
Select @IP_ID = IP_ID from Ips where ip_address = @ip_address
--Insert into Hits
INSERT INTO Hits (ip_id, hit_time) VALUES (@ip_id, GETDATE())
go

HTH

DavidM

Tomorrow is the same day as Today was the day before.

overbored
Starting Member

12 Posts

Posted - 2002-01-17 : 21:39:39

Thanks!!! That's a helluvalot better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources