Insert data into SQL table

Question

The data in my DataTable is similar to

ID ContactName1 Designation1 ContactName2 Designation2 1 A dummy B sam 

Table structure of my table

 ID ContactName Designation 

I pass the values ​​in the stored procedure as:

 @ContactName1 @Designation1 @ContactName2 @Designation2 

I want one insert statement to insert records.

How can i achieve this?

Answer 1

Assuming that your primary key of your identifier is set to auto-increment, and the table has three fields:

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

According to the actual identifier, if you do not have it with auto-increments, which, judging by the comment on Ivan’s answer, you didn’t do this, you could get it using the MAX () instruction:

 SELECT MAX(ID) AS max_id FROM DataTable 

Answer 2

Given that it is stored in a procedure, why is it bothering you, is this one or two INSERT statements? Clearly, the two statements are trivial.

Some DBMSs allow you to list multiple value sentences in one INSERT (@Ivan suggests this):

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

I am not sure if a comma is needed between the lists of values. I also don’t understand whether two records in the table are allowed to have the same identifier, as well as how the identifier is determined - this is probably some kind of material with automatic increment, and different DBMSs also do this.

If your DBMS does not support multiple VALUES clauses in a single INSERT statement, you would be better off not accepting two INSERT statements. If atomicity is a problem, you can consider transactions, although if it is only part of a larger transaction, ROLLBACK on error, in particular, will be a problem. If your DBMS supports SAVEPOINTS, then the procedure can set a save point at input and commit or cancel saving at the save point at exit.

Answer 3

INSERT TABLE OF VALUES (PRESENT THE VALUE ACCORDING TO THE NUMBER AND ORDER SEPARATED BY COMMA)

EX: -

INSERT INTO TABLENAME VALUES(1,'INDIA')

HERE TWO COLUMN S.N. and COUNTRY