Copy all info if not - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copy all info if not

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2012-07-16 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 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

145 Posts

Posted - 2012-07-16 : 06:47:42

Sorry for asking,

I might make a mistake with a.*

Which is a.* Value for you?

Many thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 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

145 Posts

Posted - 2012-07-16 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 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

145 Posts

Posted - 2012-07-16 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 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

145 Posts

Posted - 2012-07-16 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 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

145 Posts

Posted - 2012-07-16 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 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

145 Posts

Posted - 2012-07-16 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 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.

jfm
Posting Yak Master

145 Posts

Posted - 2012-07-16 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources