开发者

How come the only value ever entered in the SQL Server field is 0?

开发者 https://www.devze.com 2023-04-10 02:41 出处:网络
I have two tables csvtemp and leads in both tables i have a is_download field and i am moving data from one table to another

I have two tables csvtemp and leads in both tables i have a is_download field and i am moving data from one table to another

First i insert into the csvtemp table with this

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

i then checked the csvtemp table and here is my data so far

id,firstname,lastname,department,architecture,phone,email,download,comments,company,location,is_download
258,sdf,ssss,NULL,NULL,951-5566,kate@gmail.com,"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,aerf99@comcast.net,NULL,"Need a POS cash registering system that can help us.","The Attak","Contact Us: Contact Form",0

275,Test,Tstt,NULL,NULL,8566778888,test@test.com,"Aldelo for Restaurants",testing,Staction,"Aldelo for Restaurants demo download",1

so far so good the 1 and 0 is being entered in the is_download field as expected but when i do this next stell to move it from csvtemp to the leads table its 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].[CS开发者_如何学PythonVTemp]

everything else inserts fine but the is_download is always 0

BTW the ISDOWNLOAD in the leads table is varchar (80)

UPDATE...

here are some records in the leads table...i could not get the three froom earlier because they were deleted but here is what all look like the ISDOWNLOAD is 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   shane.hubbell@gmail.com 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   sfgsd@hotmail.fr    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   fdfgsd@gmail.com    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

ANOTHER EDIT after running SELECT '$$' + is_download + '$$' FROM CSVTemp

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


Using only the code you posted and an insert statement to replace the BULK INSERT (which does not seem to be the problem), I am able to create rows in CSVTemp that translate correctly to the LEAD table.

Something else must be going on. Check to make sure the LEAD table doesn't have any triggers on it.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号