在插入(SQL)中使用相同的值

我撰寫了一個腳本來用值填充資料庫

這是腳本行

INSERT INTO AbpUsers(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, NormalizedEmailAddress, NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)
VALUES 
('[email protected]', 1, 0, 1, '', UPPER (EmailAddress), '', '', '', 0, GETDATE(), 1, 1, 0, 'AQAAAAEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD1Q==', 0)

對于NormalizedEmailAddress欄位我需要使用EmailAddress到 Upper

我嘗試使用它UPPER (EmailAddress)但收到此錯誤

第 1 行：列名“EmailAddress”無效。

關于如何使用EmailAddress列的任何想法？

uj5u.com熱心網友回復：

我不認為你現在問的是可能的。

作為一種快速的解決方法，您可以將資料插入到臨時表中，UPPER()在列上運行命令以一次全部命中它們，然后SELECT INTO從臨時表中找到所需的表。

CREATE TABLE #TemporaryAbpUsers
(
    EmailAddress...,
    IsActive...
);

INSERT INTO #AbpUsers(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, NormalizedEmailAddress, NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)
VALUES 
('[email protected]', 1, 0, 1, '', '[email protected]', '', '', '', 0, GETDATE(), 1, 1, 0, 'AQAAAAEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD1Q==', 0);

UPDATE #TemporaryAbpUsers
SET NormalizedEmailAddress = UPPER(NormalizedEmailAddress);

SELECT * INTO AbpUsers
FROM #TemporaryAbpUsers;

uj5u.com熱心網友回復：

只需更改UPPER(EmailAddress)為UPPER ('[email protected]')

INSERT INTO AbpUsers(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, NormalizedEmailAddress, NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)
VALUES 
('[email protected]', 1, 0, 1, '', UPPER ('[email protected]'), '', '', '', 0, GETDATE(), 1, 1, 0, 'AQAAAAEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD1Q==', 0)

uj5u.com熱心網友回復：

為 EmailAddress 使用變數

DECLARE @EmailAddress NVARCHAR(100);

SET @EmailAddress = N'[email protected]';
INSERT INTO AbpUsers(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, NormalizedEmailAddress, NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)
VALUES 
(@EmailAddress, 1, 0, 1, '', UPPER(@EmailAddress), '', '', '', 0, GETDATE(), 1, 1, 0, 'AQAAAAEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD1Q==', 0);

對于多個插入，您可以嘗試 Insert-From-Select-From-Values。

INSERT INTO AbpUsers(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, NormalizedEmailAddress, NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin)
SELECT EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, 
UPPER(EmailAddress) AS NormalizedEmailAddress, 
NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin
FROM (VALUES
  ('[email protected]', 1, 0, 1, '', '', '', '', 0, GETDATE(), 1, 1, 0, 'AQAAAAEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD1Q==', 0)
, ('[email protected]', 1, 0, 1, '', '', '', '', 0, GETDATE(), 1, 1, 0, 'AQBBBBEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD2Q==', 0) 
, ('[email protected]', 1, 0, 1, '', '', '', '', 0, GETDATE(), 1, 1, 0, 'AQCCCCEAACcQAAAAEHriGqDq6AuQs33CBdyNdwuiZboPua2e6aXn9MjB/qzo44kbXAAsIY77BIfzKLJD3Q==', 0) 
) q(EmailAddress, IsActive, IsDeleted, IsEmailConfirmed, Name, 
NormalizedUserName, Surname, UserName, AccessFailedCount, CreationTime, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, Password, ShouldChangePasswordOnNextLogin) 

標籤：

上一篇：從系統日期SQL查詢中查找季度的第一個和最后一個日期，包括季度

下一篇：在where子句中計算年齡