help with Insert SQL Query

i want to implement something like let say i have 2 table...customer table and order table...order table has a foreign key of customer table (maybe the customer_id)...is there any way that let say, i want to insert a particular customer_id in the customer table. Then, it will insert the particular customer_id in the order table also. I want to makeone statement query that can solve that situation?

Hello,

if you create the id yourself then you can insert it into the second table. With SQL Server you can send two queries in one command, separated by a;. But if you have the first table set up with an auto incrementing identity then you will first have to find out the id that the database has created for you. This can be done with the SCOPE_IDENTITY() function in SQL Server.

Good luck!

|||

i create the id yourself...thanx for your help...anyway, is there any other way? Because actually i have to add the id from 1st table to many other tables...maybe 5 6 tables...i think a lot of sql query i have to execute if i have to add so many ids from 1st table...

|||

Hi,

As far as I know, there is no other ways. Multiple SQL Statement can be wrapped in a single SqlCommand. However, these muliple statements have to be written manually. The SQL Server itself will not do this for you. In this case, you may need to write 5-6 queries and wrap them in one SqlCommand.

HTH.

|||

I think that you have to look on the structure of your database if you have to insert the same ID to multiple data tables. Other tables should have it as foreign key so it should be inserted only when you add new data to table linked to you main table, and in this case you have to get your ID and insert it together with record data. The best way to do this is stored procedure with included transaction if you need it.

If it is true that you create ID yourself maybe you can use identity column in your main table to do it automatically?

Thanks

JPazgier