TechRepublic|Forums|Software

Software

Question

  • Creator
    Topic
  • August 26, 2011 at 1:34 am #2210724

    SQL TMP Table

    Locked

    by jamesmacfarlane · about 11 years, 3 months ago

    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.

    Topic is locked This conversation is currently closed to new comments.
  • Creator
    Topic

All Answers

  • Author
    Replies
    • August 26, 2011 at 1:34 am #2899390

      Clarifications

      by jamesmacfarlane · about 11 years, 3 months ago

      In reply to SQL TMP Table

      Clarifications

    • August 26, 2011 at 5:03 am #2899377

      You’ve missed select out…

      by tony hopkinson · about 11 years, 3 months ago

      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

  • Author
    Replies
Viewing 1 reply thread