Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    4

    Unanswered: Data Extract then create new table

    Please help in SQL2000

    I have a table called dbo.DataFile which has 31 fields...I need to extract data from this table then create a new table under two conditions:

    1. I only need to extract the data if the data from DF_SC_Case_Nbr field doesn't start with '0000%'

    2. I need to merge [DF_SC_Case_Nbr] & [DF_SC_Def_Nbr] then call it DF_Combo_SC_Nbr (and keep the rest of the columns in tact), so the new table will now have 32 columns

    Can somebody please help with the codes???..thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't speak SQL2000 , but the general idea would be something like this:

    Code:
    CREATE TABLE new_table AS
    SELECT column1, column2, ... column 31,
      DF_SC_Case_Nbr || DF_SC_Def_Nbr DF_Combo_SC_Nbr
    FROM dbo.DataFile
    WHERE DF_SC_Case_Nbr NOT LIKE '0000%';
    Merging two columns can be done using concatenation operator (in Oracle SQL represented by "||").

  3. #3
    Join Date
    May 2004
    Posts
    4

    Question

    Yeah that sounds right...but is there anybody out there who can translate this so sql can understand it?...

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, but this sounds like homework. If you understand the basic idea and at least something about SQL Server 2000, then I think you should be able to type the answer as fast as I can.

    If you are missing something, please explain. If you are trying to get someone to do your homework, sorry, that isn't my style.

    -PatP

  5. #5
    Join Date
    May 2004
    Posts
    5

    SQLLDR problem with direct parellel true

    Hi All,
    I am using the sqlldr to load data with direct=true and parellel=true.
    now my question is i have some column with unique indexes and primary keys also. if at all any record is duplicate then index becomes unusable. so what is the solution to delete those records which are violating unique indexes.

    2) can any one provide me the query which making use of hints which does not make use of the index which is in un usable state and delete the records.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You could create a temporary table which looks exactly like your original table, but without any indexes / unique / primary keys.
    Load data into the temporary table.
    Insert data into original table, eliminating duplicate rows.

    I'd say you'll finish it much faster that way than trying to make Loader do a job itself.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •