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/