Data Extract then create new table

tes94acc · #1 (**permalink**) 05-04-04, 15:09

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.

Littlefoot · #2 (**permalink**) 05-04-04, 16:59

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 "||").

tes94acc · #3 (**permalink**) 05-04-04, 17:16

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

Pat Phelan · #4 (**permalink**) 05-04-04, 18:15

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

rameshall2u · #5 (**permalink**) 05-11-04, 03:25

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.

Littlefoot · #6 (**permalink**) 05-13-04, 03:16

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech