July 30, 2008 at 9:24 am
Hi,
What is the best way to insert data into multiple tables at once using the same unique Id? Example If Unique_Id = 999 and I'm inserting Table A with some data and Table B with some data but I want to insert the Unique_Id of 999 in Table A and Table B before moving on to the next Insert.
Thanks
July 30, 2008 at 10:32 am
I don't entirely understand the question...
You can insert into two tables:
INSERT INTO A
INSERT INTO B
But that's going to sequential, not simultaneous. In order to do them "at the same time" you would have to have two connections, each one inserting into a seperate table. Further, you can't then guarantee them being "at the same time" because there are going to be differences between the two queries caused by other actions occurring the server and there's no way to have two different connections take part in a common transaction.
I'm pretty sure these aren't the answers you're looking for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2008 at 11:35 am
Thanks....I'll throw another question at you. I have a UDF which is called from a select statement which goes after several tables to get results. Based on what data is found in each of these tables, I want to insert a record into another table. Unfortunately you cannot INSERT within a UDF. I could turn it into a Stored Procedure but how do you take a result from the select and pass it to the Stored Procedure?
For Example:
SELECT DISTINCT
ID AS TEST_ID
FROM TABLEA
ORDER BY ID ASC
How would I get the ID from each row passed to a stored procedure? You can easily pass it to a UDF.
Thanks
July 30, 2008 at 11:38 am
The code that calls the UDF: Is that a proc? Something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2008 at 11:41 am
Yes sorry forgot to mention that the select statement is currently in a Stored Procedure.
July 30, 2008 at 11:53 am
Is there a reason to not move the UDF's logic into that proc? That would solve this whole problem, but you'll have to make sure it won't cause other problems.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2008 at 12:08 pm
dale_keller (7/30/2008)
Thanks....I'll throw another question at you. I have a UDF which is called from a select statement which goes after several tables to get results. Based on what data is found in each of these tables, I want to insert a record into another table. Unfortunately you cannot INSERT within a UDF. I could turn it into a Stored Procedure but how do you take a result from the select and pass it to the Stored Procedure?For Example:
SELECT DISTINCT
ID AS TEST_ID
FROM TABLEA
ORDER BY ID ASC
How would I get the ID from each row passed to a stored procedure? You can easily pass it to a UDF.
Thanks
Well, I wouldn't pass that kind of data to another proc. I'd use it within a derived table:
SELECT..
FROM TableX AS x
JOIN (SELECT DISTINCT ID
FROM TableA
) AS a
ON a.ID = x.ID
But let's say you just inserted 100 rows into a table and you want to use them in the next table:
CREATE #myidlist (id int)
INSERT INTO TableX
OUTPUT ID
INTO #myidlist
INSERT INTO TableB
SELECT ID
FROM #myidlist
Is anything of this what you're looking for?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply