Inserting into multiple tables
-
Hello,
I need to insert into two tables. '1_id' is primary key auto generated and inserted into table '1_table'.
I may have multiple 'products' for that '1_id' so I'll insert all the 'products' into another table 'products'
i.e
Table 1 '1_table'
insert 1_table (create_date, business_partner, Loan_number, Lname, Fname)
values (getdate(), @business_partner, @Loan_Number, @Lname, @Fname)
Table 2 'products'
insert products....
I'm stuck on table 2. If I had lets say 3 products, how would I insert 3 rows to this table using the single '1_id'? The columns would be, '1_id, p_id, p_name'
Thanks,
-
Please read the top article in my signature for how to post code in order to get the fastest and most accurate answers.
Here are some questions I need answered in order to help you:
Are you really using SQL Server 7 or 2000? I am assuming you are because this is a SQL Server 7, 2000 forum.
Is 1_id an identity column?
If you are on SQL Server 7 and 1_id IS an identity column then after the insert into table 1 you would use @@Identity to assign the value of 1_id to a variable. If you are on SQL Server 2000 and 1_id is an identity column then you should use SCOPE_IDENTITY() to assign the value of 1_id to a variable. If you are using SQL Server 2005 or later you should use the OUTPUT clause of the insert statement to put value of 1_id into a table variable (you can't use a scalar variable) and it doesn't matter if 1_id is an identity column.
Once you have the value of 1_id you can then do 3 inserts, the way you'd do that would vary depending on how you have the data for the 3 products.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Hi Jack,
Thanks for responding. I messed up my first post
I thought I selected SQL2005....Sorry about that..) -
The form will send the product(s) to me as a 'binary type';
i.e
Auto, Home, Flood, Earthquake will all have check boxes and each product will be 0 or 1 so for example if someone checked Flood, I'd receive;
0010
if someone checked Auto and Home, I'd receive;
1100 and so on.
How could I parse this out? I can then easily insert them with the corresponding id. Thanks again.
-
How do you make sure the order used for the binary coding will ALWAYS match the order in your db?
-
Or, to phrase it slightly different:
Wouldn't it be much easier, if the app would send you a separated list of the products checked instead of a binary code? Something like Home,Flood instead of 0110?
I'd prefer it that way so I can make sure to check the items actually selected...
-
it will be forced from the website, at least that's what the web developer is telling me, so I'll know that the first digit will always correspond to Auto for example. If zero, I won't insert, if 1, I will insert.
-
rluke (12/29/2010)
it will be forced from the website, at least that's what the web developer is telling me, so I'll know that the first digit will always correspond to Auto for example. If zero, I won't insert, if 1, I will insert.How will you (or the app developer) ensure, that the first item will ALWAYS refernce to Auto and not to Home?
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply