sql 2005, query syntax

Hi,

I have an old table in sql server 2005. I like to migrate data from old table to the newly created table.

This is only a table look up. I have already populated the newly created table with known records but the old table has 5 years of historical data and I need to compare if something from old table does not exists in the new table then I create it as a new record, how that can be done? The comparison needs to be done on ColXX only.

eg:
oldTable                                            newTable
Col1, cols2, ColXX                           Col1, Col2, ColXX

Thanks in advance

shmzAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
No that is not correct. The compare is happening in the WHERE clause; therefore, putting it in the SELECT ONLY will yield incorrect results. In addition, DISTINCT keyword is misplaced.


insert into newtable(col1, col2, colxx)
select distinct col1, col2, ('330' + colxx) as colxx
from oldtable o
where not exists (
   select 1
   from newtable n
   where n.colxx = ('330' + o.colxx)
)
;

Open in new window

0
 
dqmqCommented:
insert into newtable
  select * from oldtable o left join newtable n on o.Colxx = n.colxx where n.colxx is null
0
 
Kevin CrossChief Technology OfficerCommented:
The above should work, though you may want to explicitly define the columns you are inserting into newtable OR at a minimum alias the * to denote it is the columns from oldtable and not the combination from the join. Because of this, I tend to use NOT EXISTS and avoid additional/unnecessary columns simple to check for existence.

insert into newtable(col1, col2, colxx)
select col1, col2, colxx
from oldtable o
where not exists (
   select 1
   from newtable n
   where o.colxx = n.colxx
)
;

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
shmzAuthor Commented:
mwvisa1:
Sorry but your code is not working and is not giving me the corect result. ?
0
 
Kevin CrossChief Technology OfficerCommented:
What does not working mean? If you are getting no results, that means you have no records in the oldtable that are not also in the newtable based on colxxx.
0
 
shmzAuthor Commented:
not usre if I am correct,
what I have is actually something like this:

insert into newtable(col1, col2, colxx)
select col1, col2, Distinct  '330' + colxx as colxx
from oldtable o
where not exists (
   select 1
   from newtable n
   where o.colxx = n.colxx
)
;

the prefix 330 must be added to old talb ecolxx before I do comparison...
0
 
Alpesh PatelAssistant ConsultantCommented:
Create SSIS package and in dataflow task use the Lookup for check existence of record in new table.
0
 
dqmqCommented:
insert into newtable
  select * from oldtable o left join newtable n on '330' + o.Colxx  = n.colxx where n.colxx is null
0
 
Kevin CrossChief Technology OfficerCommented:
As I said above, LEFT JOIN works; however, you need to alias the * at a minimum if you are not going to list out all the columns, which is highly recommended that you do.

insert into newtable
select o.*
from oldtable o
left join newtable n on '330' + o.Colxx  = n.colxx
where n.colxx is null
;
0
 
Kevin CrossChief Technology OfficerCommented:
PatelAlpesh: do you need a transformation task or something to add the '330' prefix or will the check existence (Lookup Task) handle that?
0
 
shmzAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for shmz's comment http:/Q_27326548.html#36596894

for the following reason:

Did Work beautifully.<br /><br />Thanks
0
 
shmzAuthor Commented:
It was a mistake, I like to assign the point to someone who helped me. so if you make it available so that I can assign the point, it would be great. thanks
0
 
shmzAuthor Commented:
thanks
0
All Courses

From novice to tech pro — start learning today.