Insert Data into SQL Table

Question

The Data in my DataTable is like

ID ContactName1  Designation1 ContactName2 Designation2
1  A             dummy        B            sam 

The Table structure of my Table is

ID ContactName Designation

I am passing the values in the stored procedure as:

@ContactName1
@Designation1

@ContactName2
@Designation2

I want a single insert statement to insert the records.

How can I achieve this?

Answer 1

INSERT INTO TABLENAME VALUES(GIVE VALUE ACCORDING TO NUMBER AND ORDER SEPARATED BY COMMA)

EX:-

INSERT INTO TABLENAME VALUES(1,'INDIA')

HERE IS TWO COLUMN S.N. & COUNTRY

Answer 2

Assuming your ID primary key is set on auto-increment, and your table has three fields:

INSERT INTO DataTable (ContactName, Designation) VALUES 
    (@ContactName1, @Designation1), 
    (@ContactName2, @Designation2);

As per the actual ID, if you don't have it on auto-increment, which judging from the comment on Ivan's response, you don't, you could actually get it using the MAX() statement:

SELECT MAX(ID) AS max_id FROM DataTable

Answer 3

Given that it is in a stored procedure, why do you care whether it is one or two INSERT statements? Clearly, two statements is trivial.

Some DBMS allow you to list multiple values clauses in a single INSERT (@Ivan suggests this):

INSERT INTO Table(ID, ContactName, Designation)
    VALUES(1, @ContactName1, @Designation1)
    VALUES(1, @ContactName2, @Designation2);

I'm not certain whether a comma is needed between the values lists. I'm also not clear whether the two records in Table are allowed to have the same ID, nor how the ID is determined - that is probably some auto-increment stuff, and different DBMS do that differently too.

If your DBMS does not support multiple VALUES clauses in a single INSERT statement, then you will be best off accepting two INSERT statements. If atomicity is an issue, you can consider transactions - though if this is just a part of a bigger transaction, ROLLBACK on error, in particular, would be a problem. If your DBMS supports SAVEPOINTS, then the procedure could establish a savepoint on entry, and commit or rollback to the savepoint on exit.