Help needed to INSERT using a column values variable as a result of a SELECT

Question

Help needed to INSERT using a column values variable as a result of a SELECT

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

pwallis (6/19/2012)
Don't worry if you don't understand Sean,patrickmcginnis59 highlighted the issue yesterday and
Stewart "Arturius" Campbell (6/19/2012) hinted that it may be posssible to get the information using a join...hopefully someone who does understand can assist me to get the code reworked.

I actually have a similar issue on and off, so far I've used a fairly inefficient solution (scalar functions). In the interest of the subject, I'm posting my interpretation of the issue:

CREATE TABLE TABLE_A (

[COL_1] VARCHAR(50),

[COL_2] VARCHAR(50),

[COL_3] VARCHAR(50),

[OTHER_COLS] VARCHAR(50)

)

CREATE TABLE TABLE_B (

[COL_1] VARCHAR(50),

[COL_2] VARCHAR(50),

[COL_3] VARCHAR(50),

[COL_4] VARCHAR(50),

[COL_5] VARCHAR(50),

[COL_6] VARCHAR(50)

)

INSERT INTO TABLE_A VALUES('A','B','C','D,E,F')

-- WE WANT A STATEMENT THAT INSERTS INTO TABLE_B VALUES FROM FROM TABLE_A THE VALUES IN SUCH A WAY THAT TABLE_B

-- WILL LIST LIKE THIS, IE., SPLITTING TABLE_A COLUMN 'OTHER_COLS' INTO SEPARATED VALUES:

--SELECT * FROM TABLE_B

--COL_1 COL_2 COL_3 COL_4 COL_5 COL_6

--A B C D E F

In Sean's defense, making ddl scripts available is the custom here, this way people can jump right into twiddling with queries without having to set up a table and example data. Even a degenerate example of the problem (like I posted) would probably be good enough. If what I posted isn't representative of your issue, its probably even more illustrative of the need to post correct ddl.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

patrickmcginnis59 (6/19/2012)
pwallis (6/19/2012)
Don't worry if you don't understand Sean,patrickmcginnis59 highlighted the issue yesterday and
Stewart "Arturius" Campbell (6/19/2012) hinted that it may be posssible to get the information using a join...hopefully someone who does understand can assist me to get the code reworked.
I actually have a similar issue on and off, so far I've used a fairly inefficient solution (scalar functions). In the interest of the subject, I'm posting my interpretation of the issue:
CREATE TABLE TABLE_A (
[COL_1] VARCHAR(50),
[COL_2] VARCHAR(50),
[COL_3] VARCHAR(50),
[OTHER_COLS] VARCHAR(50)
)
CREATE TABLE TABLE_B (
[COL_1] VARCHAR(50),
[COL_2] VARCHAR(50),
[COL_3] VARCHAR(50),
[COL_4] VARCHAR(50),
[COL_5] VARCHAR(50),
[COL_6] VARCHAR(50)
)
INSERT INTO TABLE_A VALUES('A','B','C','D,E,F')
-- WE WANT A STATEMENT THAT INSERTS INTO TABLE_B VALUES FROM FROM TABLE_A THE VALUES IN SUCH A WAY THAT TABLE_B
-- WILL LIST LIKE THIS, IE., SPLITTING TABLE_A COLUMN 'OTHER_COLS' INTO SEPARATED VALUES:
--SELECT * FROM TABLE_B
--COL_1 COL_2 COL_3 COL_4 COL_5 COL_6
--A B C D E F
In Sean's defense, making ddl scripts available is the custom here, this way people can jump right into twiddling with queries without having to set up a table and example data. Even a degenerate example of the problem (like I posted) would probably be good enough. If what I posted isn't representative of your issue, its probably even more illustrative of the need to post correct ddl.

Thanks Patrick, this was my interpretation of the issue at hand too. This is actually fairly straight forward combining Jeff Moden's splitter and cross tabs.

In the interest of at least 2 rows to confirm that this works I first added a second row to table_A. Then an insert.

INSERT INTO TABLE_A VALUES('g','h','i','x,y,z')

insert Table_B

select col_1, col_2, col_3,

max(Case when d.ItemNumber = 1 then d.Item end) as Col_4,

max(Case when d.ItemNumber = 2 then d.Item end) as Col_5,

max(Case when d.ItemNumber = 3 then d.Item end) as Col_6

from TABLE_A

cross apply dbo.DelimitedSplit8K(OTHER_COLS, ',') d

group by col_1, col_2, col_3

select * from table_b

For anybody else just joining us, please see the link in my signature about splitting strings. It contains the code to generate this function.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pwallis Mr or Mrs. 500 Points: 596 More actions · Answer 3

Thank you all for your assistance.

I am going to tryout the method that Sean has highlighted first as the code needs to be called via dynamicSQL (due to determining the actual columns names etc.). Hopefully it will perform a lot better than the slow function method currently in use.