October 22, 2013 at 8:52 am
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
October 22, 2013 at 8:57 am
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/
October 22, 2013 at 8:58 am
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/
October 22, 2013 at 9:13 am
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
October 22, 2013 at 9:50 am
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/
October 22, 2013 at 9:57 am
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.
October 22, 2013 at 11:01 am
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/
October 22, 2013 at 11:47 am
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
October 22, 2013 at 12:11 pm
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/
October 22, 2013 at 12:24 pm
Yes they do. This requirement is for a Content deployment service of sorts
October 22, 2013 at 12:51 pm
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/
October 22, 2013 at 12:54 pm
Oh yeah...just yesterday somebody posted this link for something similar.
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/
October 22, 2013 at 1:59 pm
I am not a fan of cursors either, SqlExecute would be better than that, but I am not a fan of that either.
October 22, 2013 at 11:26 pm
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
Change is inevitable... Change for the better is not.
October 24, 2013 at 11:05 am
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