Solved

sql 2005, query syntax

Posted on 2011-09-25
13
198 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:shmz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 36595591
insert into newtable
  select * from oldtable o left join newtable n on o.Colxx = n.colxx where n.colxx is null
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36596156
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
 

Author Comment

by:shmz
ID: 36596875
mwvisa1:
Sorry but your code is not working and is not giving me the corect result. ?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36596881
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
 

Author Comment

by:shmz
ID: 36596894
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36596902
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36598501
Create SSIS package and in dataflow task use the Lookup for check existence of record in new table.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36600177
insert into newtable
  select * from oldtable o left join newtable n on '330' + o.Colxx  = n.colxx where n.colxx is null
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36600332
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36600354
PatelAlpesh: do you need a transformation task or something to add the '330' prefix or will the check existence (Lookup Task) handle that?
0
 

Author Comment

by:shmz
ID: 36640733
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
 

Author Comment

by:shmz
ID: 36640742
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
 

Author Closing Comment

by:shmz
ID: 36641018
thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question