Insert from one table into 2 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 2005 Forums

Transact-SQL (2005)

Insert from one table into 2 tables

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-05-20 : 09:51:30

Hi, i need to create a stored procedure that will take data from one table and insert it into one tabe.

I have 3 tables
tblTest is the table that i need to take the informations from.

intD1 to intD4 is a score

I don't know if iexplain my sefl correctly tks in advance!
tblTest
1 , 2 , 'A', 'B', 0 , 1 ,2 , 4

i need this

tblResultat
1, 2, 'A','B'

tblResultatXReponse
1,1,0,0
2,1,1,1
3,1,2,2
4,1,4,3

tblTest
intTestId autonumber
intCoursId
strCodeAbsent1
strCodeAbsent2
intD1
intD2
intD3
intD4

tblResultat
intResultatsId autonumber
intCoursId
strCodeAbsent1
strCodeAbsent2

tblResultatXReponse
intResultatXReponseId autonumber
intResultatsId
intReponseDesc
intOrdre

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-20 : 10:11:10

Still not sure what you want. Check out this topic for a sample of "how to" ask a question. Your question may end up in the "how not to" category

How to ask a question on SQL Team and get a quick answer - i.e. the information you need to provide:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74221

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 10:15:27

[code]DECLARE @Temp table
(
intResultatsId int,
intCoursId int,
strCodeAbsent1 varchar(10),
strCodeAbsent2 varchar(10)
)

INSERT INTO tblResultat (intCoursId,strCodeAbsent1,strCodeAbsent2)
OUTPUT INSERTED.intResultatsId,INSERTED.intCoursId,INSERTED.strCodeAbsent1,INSERTED.strCodeAbsent2
INTO @Temp
SELECT intCoursId,strCodeAbsent1,strCodeAbsent2
FROM tblTest

INSERT INTO tblResultatXReponse (intResultatsId,intReponseDesc,intOrdre)
SELECT tmp.intResultatsId,t.ReponseDesc,t.Order
FROM (SELECT intCoursId,strCodeAbsent1,strCodeAbsent2,intD1 AS ReponseDesc,0 as Order
FROM tblTest
UNION ALL
SELECT intCoursId,strCodeAbsent1,strCodeAbsent2,intD2,1
FROM tblTest
UNION ALL
SELECT intCoursId,strCodeAbsent1,strCodeAbsent2,intD3,2
FROM tblTest
UNION ALL
SELECT intCoursId,strCodeAbsent1,strCodeAbsent2,intD4,3
FROM tblTest
)t
INNER JOIN @Temp tmp
ON tmp.intCoursId = t.intCoursId
AND t.strCodeAbsent1=tmp.strCodeAbsent1
AND t.strCodeAbsent2=tmp.strCodeAbsent2[/code]

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-05-20 : 10:16:57

Hi tks for your reponse i will try it!

quote:

Originally posted by visakh16

DECLARE @Temp table
(
intResultatsId int,
intCoursId int,
strCodeAbsent1 varchar(10),
strCodeAbsent2 varchar(10)
)


INSERT INTO tblResultat (intCoursId,strCodeAbsent1,strCodeAbsent2)
OUTPUT INSERTED.intResultatsId,INSERTED.intCoursId,INSERTED.strCodeAbsent1,INSERTED.strCodeAbsent2
INTO @Temp
SELECT intCoursId,strCodeAbsent1,strCodeAbsent2
FROM tblTest


INSERT INTO tblResultatXReponse (intResultatsId,intReponseDesc,intOrdre)
SELECT tmp.intResultatsId,t.ReponseDesc,t.Order
FROM (SELECT intCoursId,strCodeAbsent1,strCodeAbsent2,intD1 AS ReponseDesc,0 as Order
      FROM tblTest
      UNION ALL
      SELECT intCoursId,strCodeAbsent1,strCodeAbsent2,intD2,1
      FROM tblTest
      UNION ALL
      SELECT intCoursId,strCodeAbsent1,strCodeAbsent2,intD3,2
      FROM tblTest
      UNION ALL
      SELECT intCoursId,strCodeAbsent1,strCodeAbsent2,intD4,3
      FROM tblTest
)t
INNER JOIN @Temp tmp
ON tmp.intCoursId = t.intCoursId
AND t.strCodeAbsent1=tmp.strCodeAbsent1
AND t.strCodeAbsent2=tmp.strCodeAbsent2

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-05-20 : 13:56:14

Thanks i had to change a few thing put it work perfectly!! Tks tks again save me a lot of trouble!

Only one last question when you create a temp table, to you need to destroy it or??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 14:10:42

quote:
Originally posted by lucsky8

Thanks i had to change a few thing put it work perfectly!! Tks tks again save me a lot of trouble!

Only one last question when you create a temp table, to you need to destroy it or??

Nope. the temporary table (# table) is automatically destroyed upon ending the connection. But you may destroy it manually using
DROP TABLE #temp
once you've done with it inorder to free resources early once after use.

What i've used is a table variable @table. No need to destroy it explicitly too. It is cleaned up automatically upon end of query batch/procedure.

More deatils here:-

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources