Sql query for bulk insert

Question

I am trying to avoid Inserting one record at a time so i need some help;

Here is an example table named Foods:

ID   UniqueID       Name        Type
1     Apple101      Apple       Fruit
2     Ban 11        Banana      Fruit
3     abc 123       Carrot      Vegetable
4     xyz123        Mango       Fruit
5     bnb123        Spinach     Vegetable

Now i have another Table name "Food Data" which has the following fields:

ID  UniqueID   FoodPrice    FoodExpiry

What i am trying to do is create the table above by recovering all the records from the table Foods which are Fruits and inserting into this table the thing is i have some external parameters (FoodPrice,FoodExpiry) . At the end i need the table Food Dataas below:

ID  UniqueID   FoodPrice    FoodExpiry
1   Apple101   100$         2 months
2   Ban 11     100$         2 months
3   xyz123     100$         2 months

The FoodPrice & FoodExpiry will remain the same for a given Query.

In Algorithmic terms i need an equivalent of :

Select UniqueID  from Foods where Type ='Fruit' and loop all the UniqueID   
and Insert into the table food Data , i want to this in one query .

snow_FFFFFF · Accepted Answer · 2014-12-28 23:26:12Z

2

The following assumes that FoodData.ID is an identity column and would automatically be populated. I also made some assumptions when declaring the types of the parameters:

DECLARE
  @Price decimal(19, 4)
  , @Expiry nvarchar(50)

SET @Price = 100.00
SET @Expiry = '2 months'

INSERT INTO FoodData (UniqueID, FoodPrice, FoodExpiry)
SELECT
  UniqueID
  , @Price 
  , @Expiry
FROM Food
WHERE Type = 'Fruit'

edited Dec 28, 2014 at 23:26

answered Dec 28, 2014 at 22:46

snow_FFFFFF

3,31923 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

confusedMind Over a year ago

Hi , Just tested the query ran no error but nothing gets inserted into the table?

snow_FFFFFF Over a year ago

Couple of things - I just noticed in your description of the FoodData table, the one column is named "Food.ID" - does the column name really have a dot in it? If so, you would need to change the insert to INSERT INTO FoodData ([Food.ID], FoodPrice, FoodExpiry) and to be real explicit, you could change the 3rd line to FoodID as [Food.ID]

snow_FFFFFF Over a year ago

Also, the question about the identity column is important. What are the specifics of the FoodData.ID column? Is it a plain old int or is its identity specification set to true?

snow_FFFFFF Over a year ago

Okay - same question - is FoodData.ID an identity column (meaning, that the server will automatically assign the next incremental value for you), or will you need to assign this value yourself?

confusedMind Over a year ago

FoodData.ID is an identity Column.

|

Collectives™ on Stack Overflow

Sql query for bulk insert

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related