为什么 SQL Server 字段中输入的唯一值是 0？

Question

我有两个表 csvtemp 并在两个表中引导我有一个 is_download 字段，我正在将数据从一个表移动到另一个表

首先我用这个插入到 csvtemp 表中，

CREATE TABLE CSVTemp
(id INT,
firstname VARCHAR(255),
lastname VARCHAR(255),
department VARCHAR(255),
architecture VARCHAR(255),
phone VARCHAR(255),
email VARCHAR(255),
download VARCHAR(255),
comments VARCHAR(MAX),
company VARCHAR(255),
location VARCHAR(255),
is_download VARCHAR(255)
)
GO

BULK
INSERT CSVTemp
FROM 'c:\leads\leads.csv'
WITH
(
DATAFILETYPE = 'char', 
BATCHSIZE = 50, 
FIELDTERMINATOR = ',', 
ROWTERMINATOR = '\n' 
)
GO
--Check the content of the table.
SELECT *
FROM CSVTemp
GO

然后检查 csvtemp 表，这是迄今为止我的

id,firstname,lastname,department,architecture,phone,email,download,comments,company,location,is_download
258,sdf,ssss,NULL,NULL,951-5566,[email protected],"Aldelo for Restaurants","I am just looking right now.  When I get ready to purchasing I will contact you.","my Diner","Aldelo for Restaurants demo download",1

274,Scott,Henry,sales,NULL,485-755-7439,[email protected],NULL,"Need a POS cash registering system that can help us.","The Attak","Contact Us: Contact Form",0

275,Test,Tstt,NULL,NULL,8566778888,[email protected],"Aldelo for Restaurants",testing,Staction,"Aldelo for Restaurants demo download",1

数据很好，1 和 0 按预期输入到 is_download 字段中，但是当我执行下一步操作时，将其从 csvtemp 移动到线索表，其 0

INSERT INTO [SalesLogix].[sysdba].[LEAD] (
    LEADID,
    ASSIGNDATE,
    COMPANY_UC,
    EMAIL,
    ISDOWNLOAD,
    WORKPHONE) 
SELECT 
         'Q' + cast(floor(999997 * RAND(convert(varbinary, newid()))) as varchar(20)) + cast(floor(999 * RAND(convert(varbinary, newid()))) as varchar(20))
       ,CURRENT_TIMESTAMP
       ,replace(UPPER(company), '"', '')
       ,replace(email, '"', '')
       ,is_download
       ,replace(phone, '"', '')

  FROM [SalesLogix].[sysdba].[CSVTemp]

其他所有内容都插入正常，但 is_download 始终为 0

顺便说一句线索表中的 ISDOWNLOAD 是 varchar (80)

更新...

这里是线索表中的一些记录...我之前无法获取这三个记录，因为它们已被删除，但这里看起来全部都是 ISDOWNLOAD 为 0

LEADID   CREATEUSER  CREATEDATE  MODIFYUSER  MODIFYDATE  ACCOUNTMANAGERID    ASSIGNDATE  BUSINESSDESCRIPTION COMPANY COMPANY_UC  CREDITRATING    DATAQUALITY DESCRIPTION DIVISION    DONOTSOLICIT    EMAIL   EMPLOYEES   FAX FIRSTNAME   HOMEPHONE   IMPORTID    IMPORTSOURCE    INDUSTRY    INTERESTS   ISPRIMARY   LASTCALLDATE    LASTNAME    LASTNAME_UC LEADSOURCEID    MIDDLENAME  MOBILE  NEXTCALLDATE    NOTES   PREFERRED_CONTACT   PREFIX  PRIORITY    QUALIFICATION_CATEGORYID    REVENUE SECCODEID   SICCODE STATUS  SUFFIX  TICKER  TITLE   TOLLFREE    TYPE    ISDOWNLOAD  USERFIELD2  USERFIELD3  USERFIELD4  USERFIELD5  USERFIELD6  USERFIELD7  USERFIELD8  USERFIELD9  USERFIELD10 WEBADDRESS  WORKPHONE   LEAD_ADDRESSID  DONOTEMAIL  DONOTFAX    DONOTMAIL   DONOTPHONE
Q102842996      U6UJ9A00000S    36:10.1 U6UJ9A00000G    31:27.0 U6UJ9A00000G    36:10.1 NULL    Lunch Money LUNCH MONEY NULL    NULL    NULL    NULL    0   [email protected] NULL    NULL    Shane   NULL    NULL    NULL    sales   Contact Us: Contact Form    T   NULL    Hubbell HUBBELL L6UJ9A000004    NULL    NULL    NULL    Interested in your software more than your hardware.  Please email first and well setup some time to chat.  NULL    NULL    NULL    NULL    NULL    SYST00000001    NULL    New NULL    NULL    NULL    NULL    NULL    0   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    5185705656  QQ102842996     NULL    NULL    NULL    NULL
Q103999935      U6UJ9A00000S    06:10.2 U6UJ9A00000E    53:43.0 U6UJ9A00000E    06:10.2 NULL    Alajamy ALAJAMY NULL    NULL    NULL    NULL    0   [email protected]    NULL    NULL    Nizar   NULL    NULL    NULL    NULL    Aldelo for Restaurants demo download    T   NULL    Ben Ali BEN ALI L6UJ9A000002    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    SYST00000001    NULL    Follow-up   NULL    NULL    NULL    NULL    NULL    0   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    QQ103999935     NULL    NULL    NULL    NULL
Q109565345      U6UJ9A00000S    36:10.1 U6UJ9A00000G    00:20.0 NULL    36:10.1 NULL    NULL    NULL    NULL    NULL    NULL    NULL    0   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    T   NULL    NULL    NULL    L6UJ9A000004    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    SYST00000001    NULL    Purge   NULL    NULL    NULL    NULL    NULL    0   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    QQ109565345     NULL    NULL    NULL    NULL
Q11143312       U6UJ9A00000S    06:10.1 U6UJ9A00000G    10:27.0 U6UJ9A00000G    06:10.1 NULL    Noobs   NOOBS   NULL    NULL    NULL    NULL    0   [email protected]    NULL    NULL    Eduardo     NULL    NULL    NULL    NULL    Aldelo for Restaurants demo download    T   NULL    Torres  TORRES  L6UJ9A000004    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    SYST00000001    NULL    New NULL    NULL    NULL    NULL    NULL    0   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    QQ11143312      NULL    NULL    NULL    NULL

另一个编辑运行 SELECT '$$' + is_download + '$$' FROM CSVTemp 后

$$1 $$
$$0 $$
$$1 $$

Answer 1

仅使用您发布的代码和插入语句来替换 BULK INSERT（这似乎不是问题），我能够在 CSVTemp 中创建正确转换为 LEAD 表的行。

肯定还有其他事情发生。检查以确保 LEAD 表上没有任何触发器。