SQL Insert Question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL Insert Question

Author

Topic

EduardoAndres
Starting Member

3 Posts

Posted - 2009-01-13 : 15:51:44

Greetings!

I'm having a little bit of a problem with an SQL statement I have to write. The following is the scenario:

I have two tables (1) ChangeService and (2) MonitoredItem. The following are the fields in each of them:

ChangeService (Fields)-Service_ID (int)
-ClientID
-Service_Start_Date
-Service_End_Date
-ActiveSubscription

MonitoredItem (Fields)
-ID
-Service_ID
-Item_Number

The problem is that I need to write a stored Procedure that will allow me to insert some data into the ChangeService Table (which is not a problem), but (this is the problem) insert into MonitoredItem Table WHERE the MonitoredItem Service_ID is equal to the ChangeService Service_ID just inserted in the same statement.

I'm pretty sure this is possible, but I just don't know the sintax.

Any help will be greatly appreciated.

Thanks!
Eduardo

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-13 : 16:00:35

If I understand this right it sounds like you want to update the MonitoredItem data = ChangeService data.

Then I would use an update as opposed to an insert. You may also be directed to write an upsert procedure (search this forum for upsert if you like)

Basically I would run the insert on Changeservice and then update MonitoredItem from those results.

UPDATE MonitoredItem
SET SomeField = ( SELECT ChangeService.SomeField
FROM ChangeService
WHERE MonitoredItem.ID=ChangeService.ID)
WHERE EXISTS
(SELECT ChangeService.ID
FROM ChangeService
WHERE MonitoredItem.ID=ChangeService.ID)

This is a tested query on my server, I modified it a bit to suit your problem. It may be possible to optimize it.

good luck !

r&r

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-13 : 16:01:09

You can't insert in to two different tables at the *exact* same time, one insert will have to be run before the other.

- Lumbago

EduardoAndres
Starting Member

3 Posts

Posted - 2009-01-13 : 16:02:00

Hey,

Thanks for your answer, but it is an Insert and not an update. Is this possible?

Thanks!

Thanks!
Eduardo

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-01-13 : 16:10:25

Here's two methods to accomplish a insert based off the results from another insert. Not sure if I followed what you need exactly, but let me know if this is what you were looking for


declare @T1 table (ID int identity(1,1),Mycol varchar(20))
declare @T2 table (T1ID int, mycol2 varchar(20))

/*In this below query I am taking the output from what I inserted into @T1 and inserting it into table @t2*/

Insert Into @T1(MyCol)
	Output inserted.ID,inserted.MyCol + ' From T1'
	Into @T2(T1ID,mycol2)
select 'hello'

--Here's the Results
select * 
from 
@t1 a
inner Join
@t2 b
on a.ID = b.T1ID


-- OR
delete a from  @t1 a
delete a from  @t2 a

--In this part I am simply creating 2 queries

Insert Into @T1(MyCol)
select 'hello'

--I just inserted a row into @T1

/* I will use Scope_Identity() to grab the ID from the inserted row from @T1 and insert the corresponding values into @T2 by using Scope_Identity()*/

Insert into @T2(t1ID,MyCol2)
select a.ID,a.MyCol + ' From T1'
from
@T1 a
where a.ID = Scope_Identity()

--Here's the results
select * 
from 
@t1 a
inner Join
@t2 b
on a.ID = b.T1ID

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-13 : 16:14:19

I think what I'd try to do is insert your data into a #temp table, then insert whatever you need into ChangeService from #temp and then insert the data from the same #temp table into MonitoredItem. Would that work? Or does the data inserted into ChangeService differ from the data going into MonitoredItem?

EduardoAndres
Starting Member

3 Posts

Posted - 2009-01-13 : 16:30:34

Thank you to both of you for the added answers. For what Vinnie881 is saying, I'm reaserching the Scope_Identity() function. In addition, Vinnie881, I'm not clear with your example, but it feels right.

As far as what Scorch mentioned, the data into the MonitoredItems Table is different from the data being inserted into ChangeService. However, is there a way I could insert the data into ChangeService and a #Temp table?

Again, thanks a lot

Thanks!
Eduardo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources