Hi I am trying to get a csv file into a table in my database. I was told the way to do this was to create a tmptable which I have below.
create table tmpcustomers (accno char(6) null,custname char(30) null,telephone1 char(30) null, address1 char(30) null,
address2 char(30) null, address3 char(30) null, address4 char(30) null, postcode char(8) null);
I then input the file.
input into tmpcustomers from ‘C:\Users\jmacfarlane\Desktop\customers.csv’ format ASCII;
The tmp table exists and all the data is in the tmp table how do I get the information into the normal tabe. I have tried the below but it did not work.
insert into HLG01_customers (accno ,custname ,telephone1 ,address1 ,address2 ,address3 ,address4 ,postcode)from tmpcustomers;
Below is how the normal table is set out.
ALTER TABLE “DBA”.”HLG01_Customers” ADD “UniqueKey” integer NOT NULL DEFAULT autoincrement;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “AccNo” char(6) NOT NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “ShortName” char(6) NOT NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “OptionsExist” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “CustName” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Address1” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Address2” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Address3” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Address4” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “PostCode” char(8) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Contact1” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Contact2” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Contact3” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Telephone1” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Telephone2” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Telephone3” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Fax1” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Fax2” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Fax3” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Email1” char(50) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Email2” char(50) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Email3” char(50) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “WebAddress” char(60) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Cat1” char(3) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Cat2” char(3) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Cat3” char(3) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “AccStatus” smallint NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “HaulageAcc” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “WarehouseAcc” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “WorkshopAcc” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “SpareAcc1” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “SpareAcc2” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “SpareAcc3” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Active” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Value1” numeric(12,4) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Value2” numeric(12,4) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Str1” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Str2” char(30) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Options2” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Options3” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Options4” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Options5” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Options6” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “WebID” char(6) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “ExchangeID” char(6) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “WebVisibility” char(1) NULL;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “CreatedBy” char(8) NULL DEFAULT ”;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “CreatedDate” “datetime” NULL DEFAULT current timestamp;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Email4″ char(120) NULL DEFAULT ”;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Latitude” double NULL DEFAULT 0;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Longitude” double NULL DEFAULT 0;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “CoordsVerified” char(1) NULL DEFAULT ‘N’;
ALTER TABLE “DBA”.”HLG01_Customers” ADD “Exported” char(1) NULL DEFAULT ‘N’;
Thanks in advance.
