带子查询的插入语句 [英] Insert statement with sub queries

问题描述

我试图填充INSERT语句中的某些列，但是我试图在一个INSERT语句中使用多个select语句.也许这是错误的.不确定.

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.

我正在使用2个数据库. ADVNET和JANEL

I am working with 2 databases. ADVNET and JANEL

我正在尝试在ADVNET.dbo.KenCatItemTest中填充4列

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

1. 列CategoryItemId {uniqueidentifier，非null}我需要使用NEWID()来生成一个uniqueidentifier，但无法使用它.

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

列ItemId {uniqueidentifier，not null}，我需要从以下语句中获得以下33行:

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

列CategoryID {uniqueidentifier，非空}

Column CategoryID{uniqueidentifier,not null}

我希望指定'0FCA508F-7EB5-4C2E-8803-DE688C4126E5'

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

Linesequence {int，not null}

Linesequence{int, not null}

我需要从1开始，然后以1s为增量.

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

我提出了以下建议:

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'

对于LineSequence列，我正在考虑使用AUTO_INCREMENT功能或进行某种触发.

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.

推荐答案

使用此插入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 

这篇关于带子查询的插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！