Information générale
Forum:
Microsoft SQL Server
Catégorie:
Stored procedures, Triggers, UDFs
Thats pretty straightforward, Joe. Since you're dealing with SQL here, you don't have to deal with recordset issues. Here's an example, assuming that Column 1 and Column 2 are being populated from @Col1SourceVariable and @Col1SourceVariable:
SET @ExecuteString = 'SELECT ' + REPLACE(REPLACE(REPLACE(@Col1SourceVariable, '?',''), '=',',') , '&',',') + REPLACE(REPLACE(REPLACE(@Col2SourceVariable, '?',''), '=',',') , '&',',') + ' INTO NewTable'
But I assume you are getting the original string values from another data source. If so, you will want to load them into a SQL staging table in their raw form, using the Import Wizard in SQL Enterprise Manager. Then change your exec string to look something like this:
SET @ExecuteString = 'SELECT ' + REPLACE(REPLACE(REPLACE(StagingtableCol1Name, '?',''), '=',',') , '&',',') + REPLACE(REPLACE(REPLACE(StagingTableCol2Name, '?',''), '=',',') , '&',',') + ' FROM StagingTable INTO NewTable'
This copies all the rows from StagingTable, cleans up each row and loads it into StagingTable. Note that you don't have to create StagingTable before you do this - SELECT INTO does it for you.
Carl
>Thanks Carl, I appreciate that much tighter code. Now what I need to do is to get the results into a 2 field table where the rows are filled from the cleaned up string like so 'Row1Col, Row1Co2, Row2Col, Row2Co2, Row3Col, Row3Co2' if I am being clear. I hope. I don't know how to loop through the recordset or create the two col table inside a SP.
>I really appreciate your help.
>
>[snip]
>>Good start Joe, with several ammendments. You don't need a continuation character. Also, you don't need the SELECTs on the right side of the statement and you don't need the UNION. Try this:
>>
>>SET @ExecuteString = 'SELECT ' + REPLACE(REPLACE(REPLACE('?=&', '?',''), '=',',') , '&',',')
>>
>>Carl
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement