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

Advanced Insert

Author

Topic

tejendi
Starting Member

2 Posts

Posted - 2010-09-22 : 11:47:07

Hey there peeps, I'm really in need of some help with this thing.
Thing is I got two tables:
Table a: contains a lot of columns[col1][col2][col3][col4][ and so on], some with and some without content. Those without content does in fact contain 6 whitespaces.
Table b: this got two columns [colB1][colB2], none of which contains any whitespaces

What I need is to look at a single row of table a, base on the content of the first column [x].
This was relatively easy to figure out.
But I then need to add the content of specific columns from table a [col1][col3][col9][col30][ and so on], to table b, where in table b [colB1] = [x] and [colB2] = one of the columns from table a that doesn’t contain whitespaces.
A new row is added in table b for each column in table 6 that have content other than the 6 whitespaces.

I've tried to make an example of how it should work, hopefully it will help:

Table a:
COLa1	COLa2	COLa3	COLa4
a		4	
b	1 	32	43

Table b:
COLb1	COLb2
x	2
x	1
y	1

table b after code with COLb1[a] selected:
COLb1	COLb2
x	2
x	1
y	1
a	4

With COLb1[b] selected:
COLb1	COLb2
x	2
x	1
y	1
b	1
b	32
b	43

Looking forward to your replies
//Daniel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 12:43:42

[code]
INSERT INTO TableB
SELECT COLa1,COLa2
FROM TableA
WHERE COLa1=@yourvalue
AND LEN(COLa2) >0
UNION ALL
SELECT COLa1,COLa3
FROM TableA
WHERE COLa1=@yourvalue
AND LEN(COLa3) >0
UNION ALL
SELECT COLa1,COLa4
FROM TableA
WHERE COLa1=@yourvalue
AND LEN(COLa4) >0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources