Solved

sql 2005, query syntax

Posted on 2011-09-25
13
188 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
  • 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 59

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
 
LVL 59

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 59

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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 59

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 59

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

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 we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now