Click here to Skip to main content
14,453,311 members
Rate this:
Please Sign up or sign in to vote.
See more:
hi frens, i want to insert the values like firstname, surname,phone into Customertable and address,country,town into Addresstable but i want to use the single query string , how to perform this please help me with the code.
with regards bishnu karki
Posted
Comments
Orcun Iyigun 20-Dec-11 13:50pm
   
http://www.codeproject.com/Answers/303107/how-to-insert-into-two-tables-from-single-query#answer3.. SO you had 3 answers in your previous post and you failed to apply it?? Just adding the column names to your question and re-ask it again solved your problem?
Rate this:
Please Sign up or sign in to vote.

Solution 3

You'd do this in 3 SQL statements. The first is going to add the data to the customer table. The next is going to get the ID of the customer just added. The last one will add the address data to its table, using the ID of the customer to maintain the relation.

You can do it with a single SqlCommand, but you're going to call a stored procedure to handle adding the data to the tables. Google for ""c# call stored procedure with parameters[^]" for examples.
   
Comments
Monjurul Habib 20-Dec-11 12:57pm
   
nice advice with link,5!
Rate this:
Please Sign up or sign in to vote.

Solution 1

Inserts are done on single tables, what you can do is start a Transaction and insert into the two tables via two queries and Commit when done.

This will make sure the two queries are done in an atomic fashion.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

I found it over here
   
v2
Comments
Orcun Iyigun 20-Dec-11 13:48pm
   
Well answered. Same question in a different format from the same user..
Monjurul Habib 20-Dec-11 13:49pm
   
:)
Orcun Iyigun 20-Dec-11 13:52pm
   
since there are already 4 solution I didnt want to delete the question..
Rate this:
Please Sign up or sign in to vote.

Solution 4

IMO the best way to do this is to create a parameterized stored procedure to do the inserts, see: CREATE PROCEDURE [^]. Then execute the procedure using SqlCommand.ExecuteNonQuery Method [^]

And as pointed out, use transactions properly, see both BEGIN TRANSACTION [^] and SqlTransaction Class[^]

To be honest, you can actually execute multiple statements with a single call from C#. This is done by executing a batch, but I don't suggest this approach for several reasons:
- probably a maintenance nightmare
- logic is hard to follow
- no good reusing of code etc etc.
   
Rate this:
Please Sign up or sign in to vote.

Solution 5

If you need to do this via front end then just use join in your sql.

Let's see the code,

SELECT * FROM CustomerTable c 
LEFT JOIN AddressTable a ON a.CustomerId = c.CustomerId
WHERE c.CustomerId = @CustomerId


set @CustomerId to your customer id. You will definitely get your result.
   
Rate this:
Please Sign up or sign in to vote.

Solution 6

use storedprocedure to achive this as
CREATE PROCEDURE [dbo].[Insert_Answer] 
	
AS
DECLARE  @LocalError int,
        
         BEGIN TRANSACTION
    
	insert query 1
         insert query 2
         insert query 3
	
       SELECT @LocalError = @@Error
     IF NOT @LocalError = 0
      BEGIN
        ROLLBACK TRANSACTION
    SELECT  Error = @LocalError
        
        END
ELSE
        BEGIN
     COMMIT TRAN
   
    SELECT  Error = 0 
    END
   
Rate this:
Please Sign up or sign in to vote.

Solution 7

HI TRY THIS WAY,


CREATE TABLE CUSTOMERtable(firstname VARCHAR(100),surname VARCHAR(100),phone VARCHAR(100));
CREATE TABLE ADDRESStable (address VARCHAR(100), country VARCHAR(100), town VARCHAR(100))

CREATE proc InserttABLE
(
@firstname VARCHAR(100),
@surname VARCHAR(100),
@phone VARCHAR(100),
@address VARCHAR(100),
@country VARCHAR(100),
@town VARCHAR(100)
)
as
BEGIN
  INSERT INTO @TABLE VALUES(@firstname,@surname,@phone,@address,@country,@town);
INSERT INTO  CUSTOMERtable VALUES(@firstname,@surname,@phone)
 INSERT INTO ADDRESStable VALUES(@address,@country,@town);
 END
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100