You must Sign In to post a response.
  • Category: SQL Server

    Insert the data at a time in two tables using sql

    hi every one,
    how i can display the data in two table my tables like
    table1
    qutionid(identity) qution date

    table2
    optionid(identity) qutionid optionno option date


    Thanks and regards,
  • #763409
    Hello Sadiq,

    If you want to show the data from both the tables simultaneously then you can use Joins.

    Your query should be like this :

    SELECT t1.qutionid, t1.qution t1.date, t2.optionno, t2.option, t2.date FROM Table1 t1 JOIN Table t2 ON t1.t1.qutionid = t2.t1.qutionid;

    If you want to use WHERE Clause the query should be like this :

    SELECT t1.qutionid, t1.qution t1.date, t2.optionno, t2.option, t2.date FROM Table1 t1 JOIN Table t2 ON t1.t1.qutionid = t2.t1.qutionid WHERE t1.qutionid = 1;

    You can use any column in WHERE clause.

    Hope this is what you are looking for.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #763412
    hi sir,
    Thank u for response.
    i don't wan't show the data. i want insert the data in both table at a time.
    Thank u,

  • #763415
    Hello Sadiq,

    If you want to insert data into both table in single query then you can use it like :

    MERGE INTO Table1 AS t1
    USING MyTable ON 1=0

    WHEN NOT MATCHED BY TARGET THEN
    -- INSERT into Table1:
    INSERT (qutionid, qution, date) VALUES (t1.qutionid, t1.qution, t1.date)

    --- .. and INSERT into Table2:
    OUTPUT inserted.ID, MyTable.optionid, MyTable.qutionid, MyTable.optionno, MyTable.option, MyTable.date
    INTO Table2 (ID, optionid, qutionid, optionno, option, date);

    Hope this will is what you are looking for.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #763416
    you have two options

    We do not have insert statement for two tables at a time.

    We have the following options

    1. You can go for Stored Procedure
    2. You can dynamically create the sql query do single call.

    By Nathan
    Direction is important than speed

  • #763418
    Hello,

    You can also try in this way:-

    Suppose there are two tables i.e. tableA and tableB

    Now, table A contains columns/fields :- qutionid(identity), qution date

    and table B contains columns/fields :- optionid(identity),qutionid, optionno, option date

    Now for displaying the two tables value :-

    SELECT * FROM tableA as t1, tableB as t2 WHERE t1.qutionid = t2.qutionid

    Now for inserting the value in the two tables are as follows :-

    a) Inserting data into tableA values :-

    INSERT INTO tableA(qution, date)
    SELECT t1.quition, t1.date FROM tableA as t1, tableB as t2 WHERE t1.qutionid = t2.qutionid



    b) Inserting data into tableB values :-

    INSERT INTO tableB(qutionid, optionno, option date)
    SELECT t2.qutionid, t2.optionno, t2.option date FROM tableA as t1, tableB as t2 WHERE t1.qutionid = t2.qutionid

    Either you can use this query or you can better proceed for stored procedure.

    Hope the above query will help you.

    Thanks

  • #763423
    Hello Ranajoy,

    You told the query which is wrong as per his requirement. As he don't have the data in both the tables and he want to add the data in both tables simultaneously.

    Please read the question properly and than answer if you can understand his question properly.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #763491
    Hi sadiq,

    Your question tag is different and content is different.

    As per Question tag you may expect to insert data into 2 tables when you perform the action, for that create procedure and call 2 insert statements into that or else create 2 separate procedures and call those 2 procedures when you perform insert action.

    As per your content you may expect to display the data using 2 tables, in that case use joins and proper joining conditions for fetching exact result.

    Hope this will helpful to you..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments