Combine two tables - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Combine two tables

Author

Topic

kabon
Starting Member

48 Posts

Posted - 2013-04-01 : 00:58:03

I had two table which is A and B.

Table A only have 1 column that is ID for Table B.
for Example data in Table A:

ID|
1|
2|
3|
4|
5|

Table B is data which must use ID from table A.
for example data in table B:
ID|Class|Name|
|1A|James|
|2B|Ko|
|3C|Nick|
|4D|Fei|
|5E|Jo|

I want to combine table A and B and the result is like this:
ID|Class|Name|
1|1A|James|
2|2B|Ko|
3|3C|Nick|
4|4D|Fei|
5||5E|Jo|

can you help me for the script?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 01:00:55

whats the rule for combining? is it like id value thats contained in Class field of tableB to be matched against ID of TableA?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-01 : 02:26:07

--I'm trying to give you simple idea
select a.ID, b.Class, b.Name
from TableA a join TableB b on a.ID = LEFT(b.Class, 1)

I assumed that the column Class in TableB is combination of ID of TableA and Class....
LEFT(b.Class, 1) --> will work for 1 to 9 IDs only...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 02:48:57

using functions on join conditions performs poorly for large datasets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-01 : 05:10:23

DECLARE @A TABLE(ID INT)
INSERT INTO @A VALUES(1), (2), (3), (4), (15)

--Table B is data which must use ID from table A. for example data in table B:
DECLARE @B TABLE(ID INT, Class VARCHAR(5), Name VARCHAR(10))
INSERT INTO @B
SELECT null, '1A', 'James' union all
SELECT null, '2B', 'Ko' union all
SELECT null, '3C', 'Nick' union all
SELECT null, '4D', 'Fei' union all
SELECT null, '15E', 'Jo|'
/*I want to combine table A and B and the result is like this:
ID|Class|Name|
1|1A|James|
2|2B|Ko|
3|3C|Nick|
4|4D|Fei|
5|5E|Jo|*/
select a.ID, b.Class, b.Name
from @B b
join @A a on a.ID LIKE LEFT(b.Class, LEN(a.ID))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 05:46:57

that can be simplified as

b.Class LIKE CAST(a.ID AS char(1)) + '%'

which may still not perform well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-01 : 06:30:00

CAST(a.ID AS char(1)) + '%' --> this will work only for 1 to 9 Ids...
a.ID LIKE LEFT(b.Class, LEN(a.ID)) --> this is for any integer ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 06:39:26

well..i just wrote iyt for posted data

to scale it up just use

b.Class LIKE CAST(a.ID AS varchar(15)) + '%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-01 : 07:02:06

Hi Visakh,
If you don't mind, Check the result with my sample data and yours ON clause...
--It will treat 1, 15 as same IDs
ID Class Name
1 1A James
1 15E Jo|

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 07:28:48

It can still be handled right?



declare @test1 table
(
ID int
)

declare @test2 table
(
Class varchar(5),
Name varchar(100)
)
insert @test1
select 1	
insert @test2
select '1A','James' union all
select 	'15E','Jo'

select *
from @test2 t2
left join @test1 t1
on t2.Class LIKE CAST(t1.ID AS varchar(10)) + '[A-Z]%'


output
----------------------------------
Class	Name	ID
----------------------------------
1A	James	1
15E	Jo	NULL

As I suggested in my first reply I don't recommend this as this would still not perform well though it may be better than the method using functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kabon
Starting Member

48 Posts

Posted - 2013-04-02 : 03:20:42

do you know what is different between CONVERT and CAST?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-02 : 06:48:56

http://searchsqlserver.techtarget.com/tip/The-difference-between-CONVERT-and-CAST-in-SQL-Server

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-02 : 07:05:59

Main differences are:
Both cast and covert serves the same purpose i.e. convert a data type to another.
¦Cast
1.Cast is ANSII Standard
2.Cast cannot be used for Formatting Purposes.
3.Cast cannot convert a datetime to specific format
¦Convert
1.Convert is Specific to SQL SERVER
2.Convert can be used for Formatting Purposes.For example Select convert (varchar, datetime, 101)
3.Convert can be used to convert a datetime to specific format

kabon
Starting Member

48 Posts

Posted - 2013-04-03 : 23:39:44

thank you bandi :)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-04 : 00:14:03

quote:
Originally posted by kabon

thank you bandi :)

welcome

--
Chandu

kabon
Starting Member

48 Posts

Posted - 2013-04-04 : 01:22:29

I have problem again, I want to make 2 different table and the name is A_MAT and A_BEF_MAT.

Which is in one declare and the requirement is select all from table A which is date before 20130404 into table A_BEF_MAT and else into table A_MAT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-04 : 02:08:43

use SELECT INTO syntax

like


SELECT * INTO A_MAT FROM TableA WHERE datefield >= '20130404'

etc

Keep in mind that this will only create table with data so if you've any constraints etc defined on main table which you want to apply to new table then you need to script them out and apply separately

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources