Need to pull the data out of a row and into a comma separated string referencing the columnID

  • I am a bit rusty on my SQL Server T-Sql due to work pushing me into MySQL and DB2.

    I am trying to create a t-sql script the will loop through the column ID's and extract the data form that column in the table.

    Example - ColumnID = 1, Column Name is 'ID', I want to grab the data for ID based on the table object_id and the column ID as referenced in syscolumns.

    The output string I am looking for would be:

    'Value1','Value2','Value3',...,'Valuen', where n is the last Column ID value

    I could do this easily in SSIS, but I am not allowed to use SSIS.

    I am using a while loop to step through all the column IDs and that's about where I got stalled.

    Any ideas, any help is appreciated.

    Respectfully,

    David

  • I'm not really clear on how you want your query, but I believe that this article might help you.

    http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I don't think you need a loop here at all. However I am a little unclear on exactly what you are trying to do. Are you trying to generate a comma separated list of values for each column in a table or a specific column? Can you provide an example of what you want for output based on say a 2 column table with 2-3 rows of data? I just can't visualize what you want.

    _______________________________________________________________

    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/

  • I saw that, but it does not work for my application.

    I need to create a adaptive insert statements transferring data in one table to a table on a different server. SSIS is not allowed.

    In order to do this, I need to access the data in the table via the column ID of the columns.

    This is how I get the column Names

    declare @count INT, @columncount int, @dataOut varchar(8000), @tablename varchar(200)

    select @tablename = 'Patient'

    select @columncount = COUNT(*) from Sys.columns where OBJECT_ID = OBJECT_ID(@TableName)

    select @count = 1

    select @dataOut = ''

    while @count < @columncount + 1

    Begin

    select @dataout = @dataout + (Select NAME from sys.columns WHERE OBJECT_ID = OBJECT_ID(@TableName) and column_id = @count)+','

    Select @count = @count +1

    Continue

    End

    select @dataOut

    Now I need to pair the actual data from the row to the columns

    This is for a adpatively generated Insert statement

  • dcesharkman (10/22/2013)


    I saw that, but it does not work for my application.

    I need to create a adaptive insert statements transferring data in one table to a table on a different server. SSIS is not allowed.

    In order to do this, I need to access the data in the table via the column ID of the columns.

    This is how I get the column Names

    declare @count INT, @columncount int, @dataOut varchar(8000), @tablename varchar(200)

    select @tablename = 'Patient'

    select @columncount = COUNT(*) from Sys.columns where OBJECT_ID = OBJECT_ID(@TableName)

    select @count = 1

    select @dataOut = ''

    while @count < @columncount + 1

    Begin

    select @dataout = @dataout + (Select NAME from sys.columns WHERE OBJECT_ID = OBJECT_ID(@TableName) and column_id = @count)+','

    Select @count = @count +1

    Continue

    End

    select @dataOut

    Now I need to pair the actual data from the row to the columns

    This is for a adpatively generated Insert statement

    It is still pretty unclear what you are trying to do here. Are you trying to generate insert statements so you can transfer data? Can you just do remote inserts through a linked server or do you have to script this? Scripting this could get kind of ugly because you have to deal with datatypes. I still don't at all understand why you need to look at each column ID in a loop.

    _______________________________________________________________

    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/

  • In the end I need to be able to access the data in the table using the object_ID of the table and the columnID of the data column along with the where clause that picks the row of data to use.

    This all has to be scripted because the dev team only deals with scripts. They do not see other methods of populating synchronized databases. SSIS and Replication are out of the solution set.

  • dcesharkman (10/22/2013)


    In the end I need to be able to access the data in the table using the object_ID of the table and the columnID of the data column along with the where clause that picks the row of data to use.

    This all has to be scripted because the dev team only deals with scripts. They do not see other methods of populating synchronized databases. SSIS and Replication are out of the solution set.

    I can tell you know exactly what you want but it is getting lost in translation to me. So you want a script that will have two variables @ObjectID and @ColumnID and you want to be able to retrieve those values? I can't even begin to figure out what you mean by the where clause part of this.

    Maybe if you try to explain how this will be used it would help me get it. I am 100% confident I can help you with the query, I just don't yet understand the requirements.

    _______________________________________________________________

    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/

  • Here is the output form the process that is needed:

    INSERT [dbo].[Address] ([ID], [Address1], [Address2], [City], [State], [ZipCode], [AddressTypeID], [IsPrimary], [CountryId], [Address_no_old]) VALUES (4, N'52123 Go Street', N'', N'Othertown', N'CA', N'92610', 73, 1, 741, NULL)

    Where the function that creates this is the core of what I have not been able to convey

    The catch is that the function needs to work for all tables in the database.

    It is easy to get the Insert column list, data is the problem

    Here is how they want to call ....

    Table Key Key

    Name Column Value

    called by exec [dbo].GenerateSingleInsert 'dbo.CLPower', 'CLPowerId', 100

    These values are used in the where clause

  • dcesharkman (10/22/2013)


    Here is the output form the process that is needed:

    INSERT [dbo].[Address] ([ID], [Address1], [Address2], [City], [State], [ZipCode], [AddressTypeID], [IsPrimary], [CountryId], [Address_no_old]) VALUES (4, N'52123 Go Street', N'', N'Othertown', N'CA', N'92610', 73, 1, 741, NULL)

    Where the function that creates this is the core of what I have not been able to convey

    The catch is that the function needs to work for all tables in the database.

    It is easy to get the Insert column list, data is the problem

    Here is how they want to call ....

    Table Key Key

    Name Column Value

    called by exec [dbo].GenerateSingleInsert 'dbo.CLPower', 'CLPowerId', 100

    These values are used in the where clause

    So do the destination and source tables have the same name and same exact ddl?

    _______________________________________________________________

    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/

  • Yes they do. This requirement is for a Content deployment service of sorts

  • dcesharkman (10/22/2013)


    Yes they do. This requirement is for a Content deployment service of sorts

    Gotcha 100% loud and clear now. As you eluded SSIS would be a far better choice for this type of thing. Yuck!!! Lemme see what I can come up with. Might take me a bit as I have my actual job to deal with too. 😉

    Maybe we will get lucky and somebody else will pop by with a ready made solution. 😛

    _______________________________________________________________

    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/

  • Oh yeah...just yesterday somebody posted this link for something similar.

    http://community.dynamics.com/gp/b/ifks/archive/2011/07/20/sql-script-for-generating-insert-statements-from-table-data.aspx

    You might take a look. I think it might be a starting point. The cursor could be avoided. I am pretty sure the performance is not a deal breaker here because it is only looping through columns...I can't believe I sort of just condoned a cursor...I think I may have my membership from the anti-RBAR society revoked.

    _______________________________________________________________

    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/

  • I am not a fan of cursors either, SqlExecute would be better than that, but I am not a fan of that either.

  • dcesharkman (10/22/2013)


    Here is the output form the process that is needed:

    INSERT [dbo].[Address] ([ID], [Address1], [Address2], [City], [State], [ZipCode], [AddressTypeID], [IsPrimary], [CountryId], [Address_no_old]) VALUES (4, N'52123 Go Street', N'', N'Othertown', N'CA', N'92610', 73, 1, 741, NULL)

    Where the function that creates this is the core of what I have not been able to convey

    The catch is that the function needs to work for all tables in the database.

    It is easy to get the Insert column list, data is the problem

    Here is how they want to call ....

    Table Key Key

    Name Column Value

    called by exec [dbo].GenerateSingleInsert 'dbo.CLPower', 'CLPowerId', 100

    These values are used in the where clause

    Why not just build a dynamic BCP OUT statement and be done with it?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Can't use BCP. it is for a selective subset of the table

    Also this is for a remote deployment application that will be inside a db project in VS 2012

Viewing 15 posts - 1 through 15 (of 15 total)

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