在插入时在 SQL 中自动生成唯一标识符的问题 [英] Issues with getting uniqueidentifier auto generate in SQL on insert

问题描述

我正在尝试将一个表中的数据插入到另一个表中.表 1 没有唯一标识符，但表 2 有.我尝试使用 NEWID() 插入，但出现语法错误.有人可以告诉我我做错了什么吗?

I am trying to insert data from one table into another table. Table1 doesn't have a uniqueidentifier, but Table2 does. I have tried to insert using NEWID(), but I get a syntax error. Can someone please tell me what I am doing wrong?

INSERT INTO Table2 (NEWID(), [Item Description], [Item Cost])
SELECT Description, Cost FROM  Table1
WHERE Id = '1'

推荐答案

如果 Table2 尚未创建，您可以使用此查询:

In case Table2 is not yet created you can use this query:

SELECT
     NEWID() AS [ID]
    ,Description AS [Item Description]
    ,Cost AS [Item Cost]
INTO Table2
FROM Table1
WHERE Id = '1'

但是，如果表 2 的架构已经创建，并且它有一列用于标识符，那么您可以使用:

But, in case the schema for Table 2 has already been created, and it has a column for the identifier then you can use:

INSERT INTO Table2 ([ID], [Item Description], [Item Cost])
SELECT
   NEWID()
   , Description
   , Cost
FROM Table1
WHERE Id = '1'

但是，如果您尚未为 Table2 创建架构，那么我建议使用以下代码为表创建架构并使用 Table1 中的数据填充其中的数据.

But if you haven't created the schema for Table2 yet, then I recommend using the following code to create both the schema for the table and populate the data in it, using data from Table1.

CREATE TABLE Table2 (
    [ID] INT Identity
    ,[Item Description] AS NVARCHAR(MAX)
    ,[Item Cost] AS NUMERIC(18, 2))

INSERT INTO Table2([Item Description] , [Item Cost])
SELECT
    Description
    , Cost
FROM Table1
WHERE Id = '1'

将 ID 列设置为 Identity 将自动为每一行生成一个 UNIQUE 标识符编号，您不必担心填充.数字是递增的，默认每行递增 1，从 1 开始.

Setting the ID column as Identity will auto-generate a UNIQUE identifier number for each row which you don't have to worry about populating. The number is incremental and it increments by default by 1 for each row, starting from 1.

您还可以通过将 [ID] 列定义为 [ID] INTEGER IDENTITY(1000, 2) 来定义起始编号和增量值，这将使起始值 1000 和增量将是 2(但这只是仅供参考).

You can also define a starting number and a incremental value by defining the [ID] column as [ID] INTEGER IDENTITY(1000, 2) and this will make the starting value 1000 and the increment will be 2 (but this is just FYI).

这篇关于在插入时在 SQL 中自动生成唯一标识符的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！