CODEWITHSUNDEEP
sqlsql-servermultiple-records
Shmewnix
Shmewnix

Reputation: 1573

Adding Multiple Records to a table

I have a customer database I would like to add a set of Card numbers to. In the table there are multiple columns that will be the same, however the Card number will increase by 1 until finished. So for example... I want to add Gift cards 1 - 250 with the other columns the same... so it would like something like this:

Cardnum      price    booktype   service 
1             9.99       1         12 
2             9.99       1         12
3             9.99       1         12

etc etc... This would repeat until cardnum was '250' Is it possible to do this with an SQL Query?

Thanks.

Upvotes: 0

Views: 312

Answers (4)

Dominic P
Dominic P

Reputation: 2901

@jimdrang already provided the answer, but since I was just finishing a full example with the CREATE TABLE and a stored procedure to do the job, I guess I might as well post it for the benefit of anyone looking for this down the road.

CREATE TABLE Cards
(
  Cardnum int not null primary key,
  price money not null,
  booktype int not null,
  service int not null
);
GO

CREATE PROCEDURE [dbo].[sp_AddCards] (
  @Price money,
  @BookType int,
  @Service int,
  @NumCards int,
  @StartNum int
)
  AS
BEGIN
  DECLARE @CurCard int
  SELECT @CurCard = @StartNum

  WHILE @CurCard < @StartNum + @NumCards
  BEGIN
    INSERT INTO Cards (Cardnum, price, booktype, service)
    VALUES (@CurCard, @Price, @BookType, @Service)

    SELECT @CurCard = @CurCard + 1
  END
END

GO

EXEC sp_AddCards @Price=9.99, @BookType=1, @Service=12, @NumCards=250, @Startnum=810041;

SELECT * FROM Cards;

DROP TABLE Cards;
DROP PROCEDURE sp_AddCards;

Hope it helps!

Upvotes: 0

markA
markA

Reputation: 1609

Since the table already exists, try this:

DECLARE @book INT
SET @book = 810041
WHILE (@book) < 810291
BEGIN
    INSERT INTO tableName
    VALUES(@book,9.99,1,12)
    SET @book = @book +1
END

Assuming you are using SQL-Server 2005 or later and want to create this table from scratch:

 CREATE TABLE [dbo].[#books](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [price] decimal(6,2) NULL,
        [bookType] [int] NULL,
        [service] [int] NULL)

    INSERT INTO #books
    VALUES(9.99,1,12)
    GO 250

Upvotes: 1

codingbiz
codingbiz

Reputation: 26376

You will need to create a stored procedure to achieve this and the code goes thus

declare @cnt int

set @cnt = 0
while(@cnt < 250)
begin
insert into dbo.xxx(price, booktype, servic) 
values(9, 1, 12)

set @cnt = @cnt + 1
end

Upvotes: 1

Saurabh R S
Saurabh R S

Reputation: 3177

First of all make the cardnum as 'identity'(seed value will be 1). Then in a while loop from 1 to 250 just write an insert statement for the rest three colums. I hope you can write the code yourself.

Upvotes: 1

Related Questions