Modify select statements to create table and insert records

Question

I have a TSQL and want to modify the select statements to create table and insert the selected commands.

set @query = N'SELECT ' + @cols + N'  **create table tablename (insert into**    
        from 
        (
            select substring, Field_Name,
              rn = row_number() over(partition by field_name order by fieldnumber)
            from bear_crossjoin
        ) x
        pivot 
        (
            max(substring)
            for Field_Name in (' + @cols + N')
        ) p '

How can i define the one in Bold letters?

Didn't I already answer this in your previous question: http://stackoverflow.com/questions/27428167/loading-exec-sp-executesql-results-into-a-table/27428404#27428404 ? — Solomon Rutzky, Dec 16 '14 at 16:40
Arun, you should either remove this question (as it is a duplicate of your prior question), or re-word it to be specific to your remaining issue, which is how to get this query to work in your ETL tool. — Solomon Rutzky, Dec 16 '14 at 17:09
This is not a duplicate because you can create a table dynamically In TSQL with sparse columns. I have seen a code my DBA is using for another application. I am looking for some thing similar. — Arun.K, Dec 16 '14 at 17:34
I have no idea what SPARSE has to do with anything, but please go back to that chat session so we can continue there. — Solomon Rutzky, Dec 16 '14 at 17:38
I recommend closing this new thread Arun.K, it sounds like you already have a thread open with your real question. — Dave.Gugg, Dec 16 '14 at 17:45

score 0 · Answer 1 · answered Dec 16 '14 at 16:34

0

How about:

set @query = N'SELECT ' + @cols + N' INTO databasename.schemaname.tablename    
        from 
        (
            select substring, Field_Name,
              rn = row_number() over(partition by field_name order by fieldnumber)
            from bear_crossjoin
        ) x
        pivot 
        (
            max(substring)
            for Field_Name in (' + @cols + N')
        ) p '

answered Dec 16 '14 at 16:34

Dave.Gugg

6,561
3
24
43

Thanks Dave. That will work, but i have another issue. No of columns are more than 1024. I think i missed mentioning in the original post. – Arun.K Dec 16 '14 at 16:39
@Arun.K You cannot overcome that limit. This is why I said in the chat session that the result set needs to be directly sent, not inserted into a table. – Solomon Rutzky Dec 16 '14 at 16:44
I agree with srutky, it sounds like your real question is how to get around SQL Server's 1024 column limit. Given that this is a pivot, is a table with 1024 + columns really going to be useful? – Dave.Gugg Dec 16 '14 at 16:49
@Dave.Gugg Just FYI, this is a follow-up to the following conversation: http://chat.stackoverflow.com/rooms/66691/discussion-between-srutzky-and-arun-k It looks like Arun sent a few messages that I was not notified of for some reason. The bigger issue is how to get this to work in his ETL tool as it does not seem to like results coming from dynamic SQL. – Solomon Rutzky Dec 16 '14 at 16:55
Leave the ETL tool for now, i am asking it to be done on SQL Server using TSQL. Can i create a table dynamically using a TSQL with more than 1024 columns and all column names passed during run time from a stored procedure? Srutzky - I have asked this in your chat session and you have not responded yet thats y i am posting this in the forum again. – Arun.K Dec 16 '14 at 17:19
@Arun.K as I said in my comment above, I was not notified of your comments made in the chat session on Friday. I see them now but you are not in the session anymore so wouldn't see anything I post. Please go back to that chat session so we can continue there. – Solomon Rutzky Dec 16 '14 at 17:37

Modify select statements to create table and insert records

1 Answers1