Mitch201 Mitch201 - 5 months ago 13
SQL Question

Using a temporary table to query and update existing table

I am still new to using SQL and this query has got me particularly stuck.

I am importing a

.CSV
file into a temporary table (it is only 1 column wide and it imports fine with the exception of the first row which is for some reason blank) for the purpose of updating a table in the existing database upon matching the imported column.

The problem I am having is that it is only matching the last entry in the imported table and doing the update to only 1 record in the existing table. The
.CSV
file is generated from a spread which in turn was generated from a query of the existing DB so I know the names are correct and they are in the temporary table.

I have seen several similar querying problems/solutions and tried to use parts of their solutions to no avail and am hoping that this community can help me out!!

if object_id('dbo.namefile', 'u') is not null
drop table dbo.namefile

create table dbo.namefile
(
name varchar(255) not null primary key
--constraint pk_t1_name check(name not like 'zzzzzzz')
)

bulk insert dbo.namefile
from 'f:\list.csv'
with (datafiletype = 'char',
fieldterminator = '","',
rowterminator = '\r',
errorfile = 'f:\inp_err.log')

update dbo.MeasurementLimit
set LowLimit = 1
from namefile as nf
join EntityName as en on en.EntityName = nf.name
join MeasurementLimit as ml on en.uid = ml.UID
where en.EntityName = nf.name


Thanks for any help

I tried this, this morning

select * from namefile


It returned 113 records the correct number of entries in the list.csv file

This however only returned 1 record

select * from namefile nf
inner join Entityname as en on en.Entityname = nf.listname


Entityname table

Measurementlist

Answer

The solution was in the bulk insert.. The original code (above) would read in a single column csv file but it also added a blank record to the table which screwed up any query you would run against it the corrected insert code is

bulk insert namefile  
from 'f:\list.csv'  
(  
datafiletype = 'char',  
fieldterminator = ',',  <========= This was wrong  
rowterminator = '\n',<====== and this was wrong  
errorfile = 'f:\inp_err.log'  
);  
Comments