Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql 2005, query syntax

Posted on 2011-09-25
13
Medium Priority
?
204 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
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.
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 anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

916 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