[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

inserting multiple rows

Dear experts
 iam using .net(c#) and sql server 2000 , i want to insert 65 rows at a time in a table i.e., these all are check boxes all are stored a table if a client select all the 65 rows, i want to insert like this

AID          Supplierid
1                   1
2                   1
3                   1............

like this i want to insert 65 rows, now iam inserting each row using loop , its take too much time and sometimes it showing time expired, please help me
0
ninusajil
Asked:
ninusajil
  • 2
1 Solution
 
Pratima PharandeCommented:
you xan try this

INSERT INTO MyTable  (FirstCol, SecondCol)
SELECT  'First' ,1
UNION ALL
SELECT  'Second' ,2
UNION ALL
SELECT  'Third' ,3
UNION ALL
SELECT  'Fourth' ,4
UNION ALL
SELECT  'Fifth' ,5

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/
0
 
RubenvdLindenCommented:
I would recommend to use the loop to create an SQL-statement as mentioned by pratima_mcs and then execute it.
This will save you the time of writing the long SQL-statement.
StringBuilder sb = new StringBuilder();
sb.AppendLine ("INSERT INTO MyTable (AID, Supplierid)");
 
for (int i = 1; i <= 35; i++)
{
    sb.AppendLine("SELECT " + i + ", 1");
 
    if (i != 35)
       sb.AppendLine("UNION ALL");
}

Open in new window

0
 
RubenvdLindenCommented:
Sorry, my previous code example will only loop to 35. I have now included the correct code for an SQL-statement which inserts 65 lines.
StringBuilder sb = new StringBuilder();
sb.AppendLine ("INSERT INTO MyTable (AID, Supplierid)");
 
for (int i = 1; i <= 65; i++)
{
    sb.AppendLine("SELECT " + i + ", 1");
 
    if (i != 65)
       sb.AppendLine("UNION ALL");
}

Open in new window

0
 
Anthony PerkinsCommented:
If you are interested I can post a solution using Xml and a single INSERT statement.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now