create record inside a loop
-
Hello,
I have some difficulties writing t-sql coz am used to a language called Progress, almost english like!
I have 2 tables, related one to many. I would like to create a record in a third table, for as many records as there after the join, smthg like:
for each Table1,
each Table2 where Table1.key = Table2.key:
create Table3
assign Table3.field1 = Table1.key
Table3.field2 = Table2.somefield
end
etc...
help is appreciated! tx!
-
SELECT t1.Key AS Field1, t2.SomeField AS Field2
INTO Table3
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.Key = t1.Key
N 56°04'39.16"
E 12°55'05.25" -
OK tx! this seems to be a good idea, but there is still smthg wrong in the syntax, i can't figure it out (the object Table3 already exists in the db!!??)
and what if i want to select specific fields from table3 to be assigned?
tx again!
-
maybe i wasn't clear, i don't want to CREATE table3, i just want to INSERT INTO it..
-
INSERT Table3 (Field1, Field2)
SELECT t1.Key, t2.SomeField
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.Key = t1.Key
N 56°04'39.16"
E 12°55'05.25" -
ok great!
one more question:
what if i need to add another table in the join, smthg like:
FROM Table1 AS t1
INNER JOIN Table2 AS t2
INNER JOIN newtable ???
and why do I have to do it with INNER JOIN, can't I just use
WHERE t2.Key = t1.Key ??
Tx again and again!
-
You've got it. To add another table to the join is:
SELECT...
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.Key=t2.Key
INNER JOIN Table3 t3
ON t2.Key2= t3.key2
The reason you can't simply add another set of keys is because the SQL engine needs to know what tables you are selecting data from. You can use non-ansi syntax:
SELECT...
FROM Table1 t1,Table1 t2,Table3 t3
WHERE t1.Key = t2.Key, etc.
But you may see performance problems and when you get to OUTER JOINS, you'll get syntax errors in 2005. Stick to the ANSI syntax.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
great, feels good to learn!
now i faced smthg else! in the new table i'm filling, i have a field that should get incremented each time i insert. (i have a procedure that outputs the new value)
declare
@Id int
exec
up_GetNewId @Id OUTPUT
insert mytable(f1,f2,f3)
SELECT t1.ff1, t2.ff2, @Id <----- of course it doens't work
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.Key=t2.Key
INNER JOIN Table3 t3
ON t2.Key2= t3.key2
what can i put in the select statement in order to execute at that moment the procedure that get the new id??
-
Try creating a user defined function that returns your next id and then you can call that function from the select statement.
CREATE Function fn_GetId ()
RETURNS INT
AS
BEGIN
...your code here
END
GO
INSERT INTO Table1
SELECT dbo.fn_GetId(), t2.SomeValue
FROM Table1 t1
INNER JOIN... blah, blah, blah
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
well, this could be an option in the case the functions adds up a simple counter
but it isnt the case since the new id i am calculating is done as follows:
INSERT INTO XtkNewId (Value) values(1)
DELETE FROM XtkNewId Where IdSeed=@@identity
set @iNewId = @@identity
and this can't be done in a function
I thought about a procedure that gets me a list of id's (comma separated); but then 2 issues to be thought:
- i have to pass it the count of the records before, which means i have to do the count of the query before the query!
- how do i take each entry of the returned comma separated list?
-
If you can use identity values, why not use them on the table that you're inserting into?
But, if you can't, you may to look at something like this instead: http://www.sqlmag.com/Article/ArticleID/48165/sql_server_48165.html
If neither of those is viable and you want to keep on in the general way you're doing, use a multi-valued table function and join it into your query to get a set of id's. But, don't use @@identity. It's very unreliable when the system gets put under load. Use scope_identity() instead.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
ok got it
loads of thanks!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply