SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Combine two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kabon
Starting Member

Indonesia
48 Posts

Posted - 04/01/2013 :  00:58:03  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 04/01/2013 :  01:00:55  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/01/2013 :  02:26:07  Show Profile  Reply with Quote
--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...

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/01/2013 :  02:48:57  Show Profile  Reply with Quote
using functions on join conditions performs poorly for large datasets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/01/2013 :  05:10:23  Show Profile  Reply with Quote

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))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/01/2013 :  05:46:57  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/01/2013 :  06:30:00  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/01/2013 :  06:39:26  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/01/2013 :  07:02:06  Show Profile  Reply with Quote
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|
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/01/2013 :  07:28:48  Show Profile  Reply with Quote
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
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 04/02/2013 :  03:20:42  Show Profile  Reply with Quote
do you know what is different between CONVERT and CAST?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/02/2013 :  06:48:56  Show Profile  Reply with Quote
http://searchsqlserver.techtarget.com/tip/The-difference-between-CONVERT-and-CAST-in-SQL-Server
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/02/2013 :  07:05:59  Show Profile  Reply with Quote
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
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 04/03/2013 :  23:39:44  Show Profile  Reply with Quote
thank you bandi :)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/04/2013 :  00:14:03  Show Profile  Reply with Quote
quote:
Originally posted by kabon

thank you bandi :)


welcome

--
Chandu
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 04/04/2013 :  01:22:29  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/04/2013 :  02:08:43  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000