May 27, 2011 at 3:29 am
Hello there, goodmorning,
Currently I am involved in a project moving an Old MS-Accessdatabase to SQL-Server2005. The structure of the database has to be altered and I am using T-SQL to achieve this. The code below contains a loop through various tables in the linked Access-server.
One of the fields in Access is called [S'mentdate] and with this quote the problem arrives. In my SQL-Server database I want to call this field [DATE_SETTLEMENT].
INSERT INTO @list SELECT [SHORT_CODE], [NOIND] FROM CLIENTS
SET @count_request = @@rowcount
set @j = 1-- Initialisatie van de WHILE @j <= @count_request
BEGIN
SELECT @shortcode = shortcode, @noind = noind FROM @list WHERE [ID] = @j
SET @sourcetabel = 'Stbl_' + @shortcode + '_Transactions'
SET @sql = 'SELECT * FROM ' + @sourcetabel
SET @finalQuery = 'SELECT acc.[ACCOUNT], acc.[NOIND], tr.[TYPE]
FROM OPENQUERY(' + @server + ','+ '''' + @sql + '''' + ') tr
JOIN ACCOUNTS acc ON acc.ID_OLD = tr.IdPar AND acc.NOIND = ' + @noind
EXEC(@finalQuery)
SET @j=@j+1
END
May 27, 2011 at 3:35 am
What is your question?
May 27, 2011 at 3:45 am
Sorry, I was not finished yet. My bad. Here the complete post, including question.
Hello there, goodmorning,
Currently I am involved in a project moving an Old MS-Accessdatabase to SQL-Server2005. The structure of the database has to be altered and I am using T-SQL to achieve this. The code below contains a loop through various tables in the linked Access-server.
One of the fields in Access is called [S'mentdate] and with this quote the problem arrives. In my SQL-Server database I want to call this field [DATE_SETTLEMENT].
QUESTION: How can I 'tell' the system that the source fieldname is [S'mentdate] and destination fieldname has to be [DATE_SETTLEMENT]? Any help appreciated.
INSERT INTO @list SELECT [SHORT_CODE], [NOIND] FROM CLIENTS
SET @count_request = @@rowcount
set @j = 1
WHILE @j <= @count_request
BEGIN
SELECT @shortcode = shortcode, @noind = noind FROM @list WHERE [ID] = @j
SET @sourcetabel = 'Stbl_' + @shortcode + '_Transactions'
SET @sql = 'SELECT * FROM ' + @sourcetabel
SET @finalQuery = 'SELECT acc.[ACCOUNT], acc.[NOIND], tr.[S'mentdate] FROM OPENQUERY(' + @server + ','+ '''' + @sql + '''' + ') tr
JOIN ACCOUNTS acc ON acc.ID_OLD = tr.IdPar AND acc.NOIND = ' + @noind
EXEC(@finalQuery)
SET @j=@j+1
END
May 27, 2011 at 4:17 am
I'm not 100% sure what that code is doing, but I'm guessing that somehow the derived SELECT query is being used to create a table on SQL Server?
If so, just change the SELECT query from SELECT * to explicitly name the fields. Any that you wish to rename can be done via the use of aliases in the query:
select field1 [f1], field2 [f2]
from ...
May 27, 2011 at 4:30 am
Thanks for your answer. Very much appreciated.
For now I am just trying to make a SELECT Query and when that is okay (results, columnnames etc.) I will change it into a INSERT INTO statement. This script ain't finished yet as it is not working.
The renaming of the columns with the alias is problematic just for one field as the source fieldname contains a quote: [S'mentdate]. I can't specify this field as the system 'thinks' the ' is the end of a quotation.
May 27, 2011 at 4:46 am
Ah yes, I see.
You could try this sort of thing:
SET @finalQuery = 'SELECT acc.[ACCOUNT], acc.[NOIND], tr.[S''mentdate]'
May 27, 2011 at 5:24 am
This works. Thanks again!
May 27, 2011 at 5:38 am
Great. My pleasure.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply