Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need to pull the data out of a row and into a comma separated string referencing the columnID Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:54 PM
Points: 7, Visits: 17
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
Post #1507196
Posted Tuesday, October 22, 2013 8:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:04 PM
Points: 3,572, Visits: 8,008
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1507201
Posted Tuesday, October 22, 2013 8:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1507202
Posted Tuesday, October 22, 2013 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:54 PM
Points: 7, Visits: 17
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
Post #1507209
Posted Tuesday, October 22, 2013 9:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1507227
Posted Tuesday, October 22, 2013 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:54 PM
Points: 7, Visits: 17
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.
Post #1507229
Posted Tuesday, October 22, 2013 11:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1507246
Posted Tuesday, October 22, 2013 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:54 PM
Points: 7, Visits: 17
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
Post #1507269
Posted Tuesday, October 22, 2013 12:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1507276
Posted Tuesday, October 22, 2013 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:54 PM
Points: 7, Visits: 17
Yes they do. This requirement is for a Content deployment service of sorts
Post #1507282
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse