INSERT query with SELECT query?

Question

I have the following INSERT statement:

INSERT INTO [StoreTestDB].[dbo].[KitItem]
           ([KitItemGUID]
           ,[KitGroupID]
           ,[Name]
           ,[Description]
           ,[PriceDelta]
           ,[WeightDelta]
           ,[IsDefault]
           ,[DisplayOrder]
           ,[ExtensionData]
           ,[TextOptionMaxLength]
           ,[TextOptionWidth]
           ,[TextOptionHeight]
           ,[InventoryVariantID]
           ,[InventoryVariantColor]
           ,[InventoryVariantSize]
           ,[CreatedOn])
VALUES
...
...
...

I need to run another query to find out the KitGroupID based on the name which will be a font.

Here is how i roughly want to do it:

SELECT TOP1 (KitGroupID)
FROM KitGroup
WHERE Name = "Font"
AND ProductID = 157

I have about 30 productIDs and i have many fonts.

I need to run the insert statement then while its running i need the select query to find out the KitGroupID to then finalise the insert. Does that make sense?

How can i do this, i am quite new to SQL.. Thanks in advance

Sorry bu for me it does not make sense, you are going to INSERT into KITGROUP and SELECT from KITGROUP in the same time? — sll
– sll, Commented Oct 28, 2011 at 8:31

Kirk Broadhurst · Accepted Answer · 2011-10-28 08:36:39Z

5

You can INSERT from a SELECT statement, and include a lot of literals in that SELECT:

INSERT [StoreTestDB].[dbo].[KitItem]
     -- all your column names
SELECT TOP 1
    'Value',
    23,
    [KitGroupId],
    'More values',
    -- ...
FROM [KitGroup] WHERE ...

answered Oct 28, 2011 at 8:36

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

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

4 Comments

PD24 Over a year ago

what do you call this sort of query, i would like to read up on it?

Tom Hunter Over a year ago

It's an INSERT query, using a derived table.

PD24 Over a year ago

please look at my example, i still dont see how the select statement will add the values to the insert?

Kirk Broadhurst Over a year ago

@PD24 There are two ways to INSERT data - you can use VALUES, or you can use SELECT. In your original example, if you change VALUES ( 'value1', 'value2', etc ) for SELECT 'value1', 'value2', etc you'll see that the both insert the same.

StuartLC · Accepted Answer · 2011-10-28 08:34:38Z

2

You can do like so:

INSERT INTO [StoreTestDB].[dbo].[KitItem]
           ([KitItemGUID]
           ,[KitGroupID]
           ,[Name]
           ,[Description]
           ,[PriceDelta]
           ,[WeightDelta]
           ,[IsDefault]
           ,[DisplayOrder]
           ,[ExtensionData]
           ,[TextOptionMaxLength]
           ,[TextOptionWidth]
           ,[TextOptionHeight]
           ,[InventoryVariantID]
           ,[InventoryVariantColor]
           ,[InventoryVariantSize]
           ,[CreatedOn])
SELECT 
  NEWID(),
  (
    SELECT TOP1 (KitGroupID)
    FROM KitGroup
    WHERE Name = "Font"
    AND ProductID = 157
  )
  , anotherConstantValue ...

answered Oct 28, 2011 at 8:34

StuartLC

108k18 gold badges225 silver badges295 bronze badges

4 Comments

PD24 Over a year ago

anotherConstantValue ... ? what does this mean?

StuartLC Over a year ago

The same as your '...' or Kirk's 'More values'. Kirks answer is a bit more elegant than mine, PROVIDED that there is always a KitGroup value for the ProductId and Name. If there isnt' a record, then my answer will attempt a NULL insert into KitValue.KitGroupId whereas Kirk's query won't insert a record at all in this instance.

PD24 Over a year ago

what do you call this sort of query?

StuartLC Over a year ago

INSERT..VALUES isn't the onlt form of insert - you can also INSERT ... SELECT (insert multiple rows, usually from other tables) and INSERT ... EXEC (i.e. insert the output of a PROC). See MSDN examples here - msdn.microsoft.com/en-us/library/dd776381.aspx

PD24 · Accepted Answer · 2011-10-28 09:33:57Z

0

Here is my sql:

INSERT INSERT INTO [StoreTestDB].[dbo].[KitItem]([KitGroupID],[Name],[PriceDelta],[WeightDelta],[IsDefault],[DisplayOrder],[InventoryVariantID]) VALUES (17,'<ml><locale name="en-GB">Gaudi</locale></ml>',0,0,1,1,0)       
SELECT TOP (1) KitGroupID, Name, ProductID FROM KitGroup WHERE (Name = 'Fonts') AND (ProductID = 157)

answered Oct 28, 2011 at 9:33

PD24

7746 gold badges16 silver badges37 bronze badges

1 Comment

Andriy M Over a year ago

Just curious: In what way does your SELECT finalises your INSERT?

Collectives™ on Stack Overflow

INSERT query with SELECT query?

3 Answers 3

4 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related