Insert statement with sub queries

问题:

I am trying to populate some columns in an INSERT statement, but I am trying to use multiple select statements in one INSERT statement. Perhaps this is wrong. Not sure.

I am working with 2 databases. ADVNET and JANEL

I am trying to populate 4 columns in the ADVNET.dbo.KenCatItemTest

1. Column CategoryItemId{uniqueidentifier,not null} I need to use NEWID() to generate a uniqueidentifier, but can't get it to go.

2. Column ItemId{uniqueidentifier,not null}, I need to get these 33 rows from this statement:

   select itemid
   from janel.dbo.item
   where janel.dbo.item.itemnumber like 'c-%' and listprice > 0
   

3. Column CategoryID{uniqueidentifier,not null}

   I wish to specify '0FCA508F-7EB5-4C2E-8803-DE688C4126E5'

4. Linesequence{int, not null}

   I need to start with 1 and increment in 1s thereafter.

I have come up with the following:

insert into ADVNET.dbo.KenCatItemTest (CategoryItemId,ItemId,CategoryId)
  NEWID();
  select itemid from janel.dbo.item where janel.dbo.item.itemnumber like 'c-%' and listprice > 0;
  '0FCA508F-7EB5-4C2E-8803-DE688C4126E5'

For the LineSequence Column, I was thinking of the AUTO_INCREMENT feature or making some kind of trigger.

I tried to indent as best i could, but the text box here was a little funny.

回答1:

Use this Insert syntax

INSERT INTO ADVNET.dbo.KenCatItemTest
            (CategoryItemId,ItemId,CategoryId)
SELECT Newid(),itemid,'0FCA508F-7EB5-4C2E-8803-DE688C4126E5'
FROM   janel.dbo.item
WHERE  janel.dbo.item.itemnumber LIKE 'c-%'
       AND listprice > 0