Can anybody provide information regrading how to insert multiple record in once. I am currently doing assignment regarding Web warehouse management system asp.net . For example, when the book stock arrive. there are number of quantity stock there. When come to SQL Server, normally the SQL is perform single transaction. How can i get the SQL Statement can insert multiple record in once with different ISBN number only.

Let said: ProductID, Publisher, Date of publish, Are the same but only ISBN number is different. Instead of insert 1 by 1 is there any method can insert multiple times in once.

Thx in advance.

Here's how. I don't know if it makes a lot of sense since somehow you'll have to get the ISBN numbers into the database one by one unless you can use DTS somehow from a file. Here's an example that works.

CREATE TABLE [ISBNNumbers] (
    [ISBNID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [ProductID] [int] NULL ,
    [ISBN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

With this data:

ISBNID,ProductID,ISBN
1,1,1234
2,1,1235
3,1,1236
4,1,1237

CREATE TABLE [tblProducts] (
    [ProductID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [Publisher] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TheDate] [datetime] NULL
) ON [PRIMARY]
GO


With this data:

ProductID,Publisher,TheDate
1,Johnny,2006-02-02 00:00:00.000

And a table called TheMaster:

CREATE TABLE [TheMaster] (
    [MasterID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [ISBN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ProductID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Publisher] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TheDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

TheMaster table is the target table. Now here's the query that will INSERT multiple rows.

INSERT into TheMaster (ISBN, ProductID, Publisher, TheDate)

(
SELECT ISBNNumbers.ISBN, Table2.ProductID, Table2.Publisher, Table2.TheDate
FROM ISBNNumbers INNER JOIN
                      Table2 ON ISBNNumbers.ProductID = Table2.ProductID
WHERE (Table2.ProductID = 1) )

Try it. It works. Still don't know if this is the best solution but it works.