| Author |
 Topic  |
|
|
jfm
Posting Yak Master
134 Posts |
 Posted - 07/16/2012 : 06:38:53
|
Hi there,
I need to copy into a new_table all the data is not in Table_feb from Table_jan.
We use column id, which is in both tables, to check if the id's in Table_Feb are
in Table_Jan.
any idea?
Thanks
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/16/2012 : 06:41:43
|
select a.*
into newtable
from Table_Jan a
left join Table_Feb b
on a.id = b.id
where b.id is null
Do you see a problem with your design here?
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 07/16/2012 : 06:47:42
|
Sorry for asking,
I might make a mistake with a.*
Which is a.* Value for you?
Many thanks |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/16/2012 : 06:50:38
|
a is an alias for the table Table_Jan, a.* is all columns from that table.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 07/16/2012 : 07:27:40
|
Perfect.
Using that query I will have all the ID's in Jan that they are not in FEb?
select a.*
from Table_Jan a
left join Table_Feb b
on a.id = b.id
where b.id is null
Thanks a lot nigelrivett |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/16/2012 : 07:42:11
|
That's right.
Another way of doing it is
select *
from Table_Jan
where ID not in (select ID from Table_Feb)
That only works if you have a single join column though.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 07/16/2012 : 07:54:57
|
Perfect,
What happens in my case using
select a.*
into newtable
from Table_Jan a
left join Table_Feb b
on a.id = b.id
where b.id is null
is that i cant create the new table:
msg 2705, level 16, state 3, line 1
column name must be unique
I tried in different ways, but still the same issue.
Sorry for asking too much..
Many thanks |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/16/2012 : 09:17:00
|
Is Table_Jan a table?
Are you sure that is what you are running?
Do you have select * rather than select a.*?
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 07/16/2012 : 09:34:10
|
Yes,
The query is as follows:
SELECT * INTO new_table
FROM Table_Jan
INNER JOIN Table_Feb
ON table_jan.ID= table_feb.id
WHERE table_feb.id IS NULL
Im sure Im missing something, but i dont know what.. THanks a lot |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/16/2012 : 09:37:46
|
>> Do you have select * rather than select a.*?
Yes you do and that is the problem.
You've also changed the left join to an inner join so you won't get any rows returned.
Change it to
SELECT a.*
INTO new_table
FROM Table_Jan a
Left JOIN Table_Feb b
ON a.ID= b.id
WHERE b.id IS NULL
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 07/16/2012 : 09:54:42
|
Sorry for the INNER JOIN, I'm running another query too.
I don't understand the first part:
SELECT a.* = SELECT * FROM Table_Jan , It's correct?
>> a is an alias for the table Table_Jan, a.* is all columns from that table
But Im asking twice to the SQL :
SELECT * FROM Table_Jan
INTO new_table
FROM Table_Jan
LEFT JOIN Table_Feb
ON table_jan.ID= table_feb.id
WHERE table_feb.id IS NULL
Sorry again nigelrivett
|
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/16/2012 : 09:58:10
|
SELECT a.* = SELECT * FROM Table_Jan , It's correct?
No.
select a.* returns the columns from a
select * returns the columns from all tables involved. In this case you have two tables with the same column names hence the error.
The values from b will be null anyway so no point in including them.
If you really don't want to alias then
SELECT Table_Jan.*
FROM Table_Jan
INTO new_table
FROM Table_Jan
LEFT JOIN Table_Feb
ON table_jan.ID= table_feb.id
WHERE table_feb.id IS NULL
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 07/16/2012 : 10:17:49
|
Thanks for your replies and your time nigelrivett
After SELECT I have removed FROM Table_Jan because if not I have "msg 156, level 15, state 1, line 2" Removing the first FROM Table_Jan, the values that I have in my new_table, are the same as the ones from Table_jan. It seems the query is not crossing the info.
SELECT Table_Jan.*
INTO new_table
FROM Table_Jan
LEFT JOIN Table_Feb
ON table_jan.ID= table_feb.id
Sorry again and thanks nigelrivett |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/16/2012 : 10:21:26
|
You need the where clause.
SELECT Table_Jan.*
INTO new_table
FROM Table_Jan
LEFT JOIN Table_Feb
ON table_jan.ID= table_feb.id
where table_feb.id is null
Why don't yoou try running what I first posted - it shoould work.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
Edited by - nigelrivett on 07/16/2012 10:22:04 |
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 07/16/2012 : 10:35:33
|
Perfect.
It's the same as the first one that you posted.
The value that I have is too high. That's why I was trying to create another query.
Thanks for your time.
|
|
|
| |
Topic |
|
Copy all info if not
