Insert of multiple records in single query

Insert of multiple records in single query in sql server

"&" is not working in sql server


eg : insert into emp ('&name', &no)

if your question is about adding one record at a time:

insert into <[I]tablename[/I]> values(val1,val2,val3,...valn);

where tablename is name of the table, val1, val2 are the values you want to add for that record. the values must be in sequence as it was defined in the table.

ramvidhu,

[B]&[/B] is an SQL-Plus command option, not SQL command option. You can use it in Oracle database command prompt (SQL>) only.:eek:

You can insert multiple records in Enterprise Manager of SQL-Server instead of using a Query Editor.:D

[QUOTE=sutnarcha;10100]ramvidhu,

[B]&[/B] is an SQL-Plus command option, not SQL command option. You can use it in Oracle database command prompt (SQL>) only.:eek:

You can insert multiple records in Enterprise Manager of SQL-Server instead of using a Query Editor.:D[/QUOTE]

Thank you for this reply. But instead of using SQL server tools, if i have to insert records programatically say in C# what could I do?

Another option that can be applied is to use temporary table for inserting 1 record at a time and after some time say in when the server is in offpeak period use this temp table as a input for the main table and refresh all the data in the main table.

e.g.
[U]insert in temp table:[/U]
Create a stored procedure a write these lines :
Create proc InsertInTemp
@name varchar(40),@address varchar(60),@contact varchar(20)
AS

IF EXISTS(SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='tempTable')
drop table tempTable;
go;
Use mydb;
create table tempTable(name varchar(40),address varhcar(50),contact varchar(20), id int identity(100,1) primary key not null);
insert into tempTable Values(@name,@address,@contact);

then at the later point of time use this query:

use mydb;
go;
INSERT INTO mainTable SELECT * FROM tempTable;

If you have any suggestion please reply.

Programmatically inserting records is a good option. Here you have to handle the activities of data collecting, record inserting and transaction commit in a loop structure for multiple records. It works fine only with small and medium sized databases.

For large databases, your other option of using temp database table is good but it occupies additional space in the database for an added table and its structure including the relations and constraints. I think there is another way of handling these activities. I have to come back to this thread for that.

insert into tablename values('&name&','&no&')