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?
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
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.
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
