Solved

sql 2005, query syntax

Posted on 2011-09-25
13
193 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

838 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