Insert multiple T2 records on T1 insert

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 multiple T2 records on T1 insert

Author

Topic

tbode
Starting Member

6 Posts

Posted - 2007-04-02 : 16:10:13

Hi all,
Been looking through the forums for the best answer but haven't found one yet.
Given Table 1 with Column A, Column B, Column C. When a record is inserted into Table A, I want to insert 20 records into Table B where Column A in Table B is Column C in Table A. Is this best done with a trigger or stored procedure? Example?

Thanks,

ElManiak
Starting Member

4 Posts

Posted - 2007-04-02 : 16:23:47

Well it depends on your database and your application.
If this is a database requirement then use triggers, this way all the application that inserts in the database will reflect the requirement.
If it is an application requirement than it is your call, you can do it by stored proc but then you will have to call it from your application.

ELManiak

tbode
Starting Member

6 Posts

Posted - 2007-04-02 : 16:43:50

I am designing the database right now so the decision is mine. I would like to use triggers, but does this entail using a cursor to insert the rows? Also, Column C is type UNIQUEIDENTIFIER. Does this require T2-Column A to be one too?
Can you point me to an example of using a trigger on INSERT for multiple rows? I a under a mild time crunch and will continue looking on my end, but all help is appreciated.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-02 : 16:57:29

Where is the data for the 20 rows that will be inserted into table 2? If you use a trigger you won't be able to use anything other than data that is inserted into table 1 and any existing data in the database. You shouldn't need a cursor, but it depends where those 20 rows are coming from?

tbode
Starting Member

6 Posts

Posted - 2007-04-02 : 17:25:37

Table 2 has 5 columns. Columns 2-5 have default values. What I am trying to create is a 1 to many insert with Table 1 Column C UNIQUEIDENTIFIER being used 20 times in Table 2 Column A. There will be a user interface to modify the default data.
Again, does Table 2 Column A need to be type UNIQUEIDENTIFIER?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-02 : 17:31:27

Yes you should use the same data type in the foreign key as you use in the primary key.

So then you're just inserting 20 copies of the same row into table 2?

tbode
Starting Member

6 Posts

Posted - 2007-04-02 : 22:16:16

Column B will contain a value from 1 to 20. Columns C,D,E will have initial default values. Sorry for the lack of detail.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-02 : 22:29:15

"I would like to use triggers"
Do it in stored procedure where as possible.

"but does this entail using a cursor to insert the rows?"
No. No cursor is required.


insert into Table2(ColumnA, ColumnB) -- ColumnC,D,E use initial default values
select @Tab1ColC, n
from   (
           select 1 as n union all select 2 union all select 3 union all . . . . select 20
       ) n

tbode
Starting Member

6 Posts

Posted - 2007-04-03 : 09:46:11

In my stored procedure I INSERT a record into table 1. Column C is defined as UNIQUEIDENTIFIER with default set as (newid()). I now want to use the new Column C value and INSERT 20 new records into table 2 with table B column A being table 1 column C value and table 2 column B be the values 1-20. The union shown above works for the column B value. My question is in my stored procedure, how do I access the new UNIQUEIDENTIFIER value generated on the table 1 INSERT?

Table 1
A varchar(4)
B varchar(4)
C uniqueidentifier DEFAULT (newid)())

Table 2
A uniqueidentifier
B int

create procedure dbo.spADDTable1
(@a varchar(4),
@b varchar(4)) as

BEGIN TRANSACTION
INSERT into table1(A,B) VALUES(@a,@b)
COMMIT

Table1 Column C should now exist with a UNIQUEIDENTIFIER value

insert into Table2(A,B)
select @Tab1ColC, n
from (
select 1 as n union all select 2 union all select 3 union all . . . . select 20
) n

EXAMPLE DATA

Table 1
HELLO WORLD {F4074B47-78FB-447F-A5BA-0C1348EFDAF4}

Table 2
{F4074B47-78FB-447F-A5BA-0C1348EFDAF4} 1
{F4074B47-78FB-447F-A5BA-0C1348EFDAF4} 2
{F4074B47-78FB-447F-A5BA-0C1348EFDAF4} 3
.
.
.
{F4074B47-78FB-447F-A5BA-0C1348EFDAF4} 20

I do not understand the @Tab1ColC var usage / syntax

tbode
Starting Member

6 Posts

Posted - 2007-04-03 : 11:15:33

I just realized that I posted in the SQL 2005 forum. I am using 2000. Can this be moved to that forum? I believe that the answer would be the same, but I might get a different audience.

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-03 : 13:17:35

Now that the thread is here it may as well stay here because in this case the answer will be the same.

In your trigger you need to access the inserted table. It is a virtual table that gives you access to the inserted data inside a trigger, so basically your trigger will be something like this

CREATE TRIGGER trigAddToTable2 ON Table1
FOR INSERT 
AS
INSERT INTO Table2(A,B) 
SELECT inserted.C, n.n
FROM inserted
CROSS JOIN (
SELECT 1 AS n UNION ALL 
SELECT 2 UNION ALL 
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20
) AS n

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources