Software

Question

Gravatar
0 Votes
Locked

SQL TMP Table

By jamesmacfarlane ·
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( 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( 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( 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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

You've missed select out...

by Tony Hopkinson In reply to SQL TMP Table

as in
Insert Into HLG01 (Columns...) Select ... From TmpTable
Number ofcolumns must match, and must have equivalent types. (same or can be implicitly casted)

NB a temporary table has it's name prefixed with a # as in
Create Table #MyTempTable(...)
The table is Created in tempdb, disappears as soon as the transaction ends. and can't be seen by any other session.
You can have gloabl temporary ttables as wel (##) they canm be seesn by other sessions/users/transactions while they are alive.

You've created a permanent table called temp....

HtHs

Back to Software Forum

Start or search

Related Discussions

Related Forums