Results 1 to 6 of 6

Thread: Insert of multiple records in single query

  1. #1
    Junior Member
    Join Date
    Feb 2007
    Answers
    1

    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)


  2. #2
    Junior Member
    Join Date
    Apr 2007
    Answers
    8

    Re: Insert of multiple records in single query

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

    insert into <tablename> 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.


  3. #3
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    Re: Insert of multiple records in single query

    ramvidhu,

    & is an SQL-Plus command option, not SQL command option. You can use it in Oracle database command prompt (SQL>) only.

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

    Lack of WILL POWER has caused more failure than
    lack of INTELLIGENCE or ABILITY.

    -sutnarcha-

  4. #4
    Junior Member
    Join Date
    Apr 2007
    Answers
    8

    Smile Re: Insert of multiple records in single query

    Quote Originally Posted by sutnarcha View Post
    ramvidhu,

    & is an SQL-Plus command option, not SQL command option. You can use it in Oracle database command prompt (SQL>) only.

    You can insert multiple records in Enterprise Manager of SQL-Server instead of using a Query Editor.
    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.
    insert in temp table:
    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.


  5. #5
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    Re: Insert of multiple records in single query

    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.

    Lack of WILL POWER has caused more failure than
    lack of INTELLIGENCE or ABILITY.

    -sutnarcha-

  6. #6
    Junior Member
    Join Date
    Jul 2007
    Answers
    5

    Re: Insert of multiple records in single query

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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact