insert into multiple tables in ms sql server

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

SQL Server Development (2000)

insert into multiple tables in ms sql server

Author

Topic

yma_lee
Starting Member

12 Posts

Posted - 2004-03-31 : 18:34:27

I have a parent table with 2 child tables that I like to do INSERT INTO
these 3 tables.

I have an autoinc field in the parent table that needs to get carry over
to the 2 child tables. The relation between the parent-children is
one-to-many.

I am using SCOPE_IDENTITY() for the primary key in the parent table.
I like to know how to insert this field into the child tables in the store
procedure or maybe ADO.NET using C#.

thanks,

yma

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-31 : 18:35:59

DECLARE @ident INT

INSERT INTO ParentTable (Column2, Column3)
SELECT 'Tara', 'Duggan'

SET @ident = SCOPE_IDENTITY()

INSERT INTO ChildTable1 (Column1, Column2, Column3)
SELECT @ident, 'San Diego', 'CA'

INSERT INTO ChildTable2 (ColumnA, ColumnB, ColumnC)
SELECT @ident, 'DBA', 'Need more money'

Tara

yma_lee
Starting Member

12 Posts

Posted - 2004-03-31 : 19:43:40

Tara,

That didn't seem to work for me.

Also I have multiple rows for childTable1 and childTable2.
How do I do multiple insert into childTables using
just one store procedure. Can I use array and loops in
a store procedure. Do you recommend triggers for this
scenario ? If so, how do I do this.

yma

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-31 : 20:05:29

Could you provide us an example?

Looping is not recommended but can be done in SQL Server. I don't know what benefit a trigger will be for this because I haven't seen an example yet.

And yes this can all be done in one stored procedure.

Tara

yma_lee
Starting Member

12 Posts

Posted - 2004-03-31 : 20:33:18

example :
parentTable
id int /*primary key; from a radiobutton */
parentName VarChar

ChildTable1
id int /* foreign key, can be multiple, from a listbox */
childName1 VarChar

ChildTable2
id int /* foreign key, can be multiple, from another listbox */
childName2

Thanks in advance for your help.

yma

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-31 : 20:35:51

So childtable1 and childtable2 have no primary key?

Why is the identity the primary key of the parentTable, as opposed to "parentName" ?

- Jeff

yma_lee
Starting Member

12 Posts

Posted - 2004-03-31 : 20:35:56

btw, Tara, I'm using MS SQL Server 2k. Thanks.

yma_lee
Starting Member

12 Posts

Posted - 2004-03-31 : 20:38:58

Jeff,

Do I need primary key for the 2 child tables ?
i have the id in parentTable to create parent_child relationship
between the 3 tables. Please help me with the design if it's not
correct.

Thanks,

yma

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-31 : 20:41:02

Could you provide us a data example? What are you inserting and what do you expect to see in the three tables?

Tara

yma_lee
Starting Member

12 Posts

Posted - 2004-03-31 : 20:51:08

example :
parentTable
id 1
parentName car

id 2
parentName truck

childTable1
id 1
childName Yellow

id 1
childName Red

id 2
childName Pink

id 2
childName Yellow

childTable2
id 1
childName Circle

id 1
childName Triangle

id 2
childName Square

id 2
childName Diamond
/***************************

parent-child relationship is one-to-many
sorry, the real data is classified, i'm using vehicle/color/shape to
represent my data.

yma

yma_lee
Starting Member

12 Posts

Posted - 2004-03-31 : 20:53:51

Sorry, the source data would come from a webpage.
the parentName is from a radio button control, the 2 childNames
would come from 2 listBox with multiple setting.

Thanks,

yma

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-31 : 20:55:28

So using your sample data:

DECLARE @ident INT

INSERT INTO parentTable (parentName)
SELECT 'car'

SELECT @ident = SCOPE_IDENTITY()

INSERT INTO childTable1 (id, childName)
SELECT @ident, 'Yellow'

INSERT INTO childTable1 (id, childName)
SELECT @ident, 'Red'

INSERT INTO childTable2 (id, childName)
SELECT @ident, 'Circle'

INSERT INTO childTable2 (id, childName)
SELECT @ident, 'Triangle'

INSERT INTO parentTable (parentName)
SELECT 'truck'

SELECT @ident = SCOPE_IDENTITY()

INSERT INTO childTable1 (id, childName)
SELECT @ident, 'Pink'

INSERT INTO childTable1 (id, childName)
SELECT @ident, 'Yellow'

INSERT INTO childTable2 (id, childName)
SELECT @ident, 'Square'

INSERT INTO childTable2 (id, childName)
SELECT @ident, 'Diamond'

Perhaps you need to explain further what you are trying to do.

Tara

yma_lee
Starting Member

12 Posts

Posted - 2004-03-31 : 21:08:04

Problem is this manual insert, it need to be more dynamic base
on user's selection on the web.

Please tell me what's wrong with this ?
create dbo.sp
(
@ident INT
@parentName int,
@childName1 int,
@childName2 int

AS
INSERT INTO parentTable (parentName)
Values (@parentName)
SELECT @ident = SCOPE_IDENTITY()

INSERT INTO childTable1 (id, childName)
values (@ident, @childName1)

INSERT INTO childTable2 (id, childName)
Values(@ident, @childName2)

)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-01 : 12:10:28

Your code is a little off:

CREATE PROC dbo.usp_SomeProc
(@parentName INT, @childName1 INT, @childName2 INT)

AS

DECLARE @ident INT

INSERT INTO parentTable (parentName)
VALUES (@parentName)

SELECT @ident = SCOPE_IDENTITY()

INSERT INTO childTable1 (id, childName)
VALUES (@ident, @childName1)

INSERT INTO childTable2 (id, childName)
VALUES (@ident, @childName2)

RETURN

GO

Tara

yma_lee
Starting Member

12 Posts

Posted - 2004-04-01 : 19:28:33

Yes, thanks, Tara, I realized that I had my @ident declared at
the wrong place. After I moved it after 'AS', then it's fine.
My problem now is that I have multiple @childName1 and @childName2.
I am passing them in as comma deliminated varchar (ex: '1,2,3')
and setting a dynamic insert with select.
I am getting an error :arguments do not match parameters for function.
Regardless, I thank you VERY much for your help.

yma

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-01 : 23:23:11

yma, do a search here for CSV and you'll find several articles dealing with them. The one that comes to mind would be Rob Volk's article on Parsing CSV Values into Multiple Rows. Then once they're in multiple rows, for your child table you could do something like

INSERT INTO childTable1 (id, childName)
SELECT @ident, childname FROM my myCSVTable

You'd likely want to use a temp table or table variable to hold the CSV converted into rows.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

yma_lee
Starting Member

12 Posts

Posted - 2004-04-06 : 19:16:32

thanks AjarnMark for your link to csv articles.
But I am currently experiencing this problem :
I have a syntax error at my dynamic sql statment for insert.
The access_id in access and marking_access tables are defined as int data type, but the '2,3' is passed in as a parameter and defined as varchar(200).

I tried to use convert function on the access_id but it didn't resolve my syntax error. Do you have any recommendation ?

This is my dynamic sql insert stmt :
--------------------------------------
insert into dbo.marking_access (marking_id, access_id) select 123, access_id from Access where access_id in (2,3)
---------------------------------------------------
Syntax error converting the varchar value '2,3' to a column of data type int.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-06 : 19:26:55

Are you sure that's the dynamic SQL Statement? It shouldn't produce the error.

Instead of executing the dynamic SQL - EXEC(@sql), do a PRINT (@sql) and send it to us.

If you don't mind also paste in the stored procedure as you have it written, so we can see that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

yma_lee
Starting Member

12 Posts

Posted - 2004-04-06 : 19:40:50

What I post came from a print @dynamicInsertAccess statment.
my dynamic sql is like this ...

set @dynamicInsertAccess = 'insert into dbo.marking_access (marking_id, access_id) select ' + convert(varchar, @Identity) +
', access_id from Access where access_id in (' + @aggregatedAccess + ')'

@aggregatedAccess is a parameter gets passed in as varchar(200).
It's a multi-selected items from my listbox.

Thanks,

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-06 : 19:49:02

Can you post the ddl for the two tables?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

yma_lee
Starting Member

12 Posts

Posted - 2004-04-06 : 19:53:13

what is ddl ?
both mark_id and access_id are int

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources