Inserting Data - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Inserting Data

Author

Topic

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2010-12-01 : 02:37:48

Dear All,
I am inserting a record through a web page. The parameters are passed to a SP. I want to fetch Ids from two separate tables and insert into a new table record The Sql statement is,


insert into tbdrmaster (vendorid, unit, Details) values (select vendorid from tbglVendormaster where vendorname='SPETS TECHNO EXPORT',
SELECT UID FROM UNITMASTER WHERE UNIT='18 FBSU', @details)

I need help to make the insert work as a single statement.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-01 : 02:41:49

You need to save the results of SELECT statements into varialbes and use them in the INSERT statement

Madhivanan

Failing to plan is Planning to fail

chadmat
The Chadinator

1974 Posts

Posted - 2010-12-01 : 02:43:05

Is there a relationship between tbglVendormaster and UNITMASTER? You need to write it as a single select OR you could do something like:

DECLARE @vendorid int
DECLARE @UID int

SELECT @vendorid = vendorid from tbglVendormaster where vendorname='SPETS TECHNO EXPORT'
SELECT @UID = UID FROM UNITMASTER WHERE UNIT='18 FBSU'

insert into tbdrmaster (vendorid, unit, Details) values (@vendorid, @UID,@details)

-Chad

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2010-12-01 : 02:43:16

Madhivanan,

Is it possible in one statement?

Thanks

chadmat
The Chadinator

1974 Posts

Posted - 2010-12-01 : 02:54:39

While this might work, it doesn't make a lot of sense, and if there are multiple values returned from each table, you will create a Cartesian Product, however, if there is just 1 row in each table, you could do this:

insert into tbdrmaster (vendorid, unit, Details)
select v.vendorid, u.UID, @details
from tbglVendormaster v, UNITMASTER u
WHERE u.UNIT='18 FBSU'
AND v.vendorname='SPETS TECHNO EXPORT'

Not sure why you have a 1 statement requirement, but if there is no join relationship between the tables, you could do this.

-Chad

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2010-12-01 : 03:05:20

Thank U chadmat,
The single statement requirement was just to make the code compact and perhaps more efficient.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-01 : 03:10:40

quote:
Originally posted by mayoorsubbu

Thank U chadmat,
The single statement requirement was just to make the code compact and perhaps more efficient.

I think the one which uses the variable would be more effecient. Otherwise you need to unneccessarily join two non-related tables

Madhivanan

Failing to plan is Planning to fail

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2010-12-01 : 03:17:52

Madhivanan,

Thanks for the advice

Subbu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources