moving data between tables

Results 1 to 5 of 5

Thread: moving data between tables

  1. #1
    kriek Guest

    Default moving data between tables

    Hi everyone<BR><BR>I have a very strange situation.<BR>I have data in a flatfile that I&#039;ve managed to import into a temp table in a mssql7 database.<BR>I&#039;ve written a script that is supposed to pull the data from the temnp table, delete the data from the &#039;real&#039; table and insert the new data. This works fine untill the last line (line 2276).<BR><BR>It gives me the following error:<BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80040e14&#039; <BR><BR>[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string &#039;&#039;. <BR><BR>I&#039;ve tried doing it with DTS, but still it bombs out at the same line.<BR><BR>I run the following code :<BR><BR>dim myrs, tempDataconn, realDataconn, mysql, tempArray, counter<BR><BR>response.buffer=true<BR>response.ex pires=0<BR><BR>sub connect()<BR>set tempDataconn=server.createobject("ADODB.Connection ")<BR>"dsn=tempdb;uid=sa;pwd=;") <BR>set realDataconn=server.createobject("ADODB.Connection ")<BR>"dsn=protea;uid=sa;pwd=;") <BR>set myrs=server.createobject("ADODB.Recordset")<BR>end sub<BR><BR>sub disconnect()<BR>set myrs=nothing<BR>set realDataconn=nothing<BR>set tempDataconn=nothing<BR>end sub<BR><BR>function maakskoon(mystring)<BR>mystring=replace(mystring," &#039;","")<BR>mystring=replace(mystring,"""","")< BR>mystring=replace(mystring,"/"," ")<BR>maakskoon=mystring<BR>end function<BR><BR>function rukMembers()<BR><BR>mysql="delete from members"<BR>realDataconn.execute(mysql)<BR>mysql=" select * from Members"<BR> mysql, tempDataconn<BR>tempArray=myrs.getrows<BR>myrs.clo se<BR>for counter=0 to ubound(tempArray, 2)<BR>mysql="insert into members (SOC, MBRNO, SURNAME, Name, [JOIN], RESIGN, DOB, SUSPEND, [PLAN], PlanEffective, BENEFITDATE, PrevPlan, Subcode, Deps, agedDeps, ADD1, ADD2, ADD3, POST) values (&#039;"&maakskoon(tempArray(0, counter))&"&#039;, &#039;"&maakskoon(tempArray(1, counter))&"&#039;, &#039;"&maakskoon(tempArray(2, counter))&"&#039;, &#039;"&maakskoon(tempArray(3, counter))&"&#039;, "&maakskoon(tempArray(4, counter))&", "&maakskoon(tempArray(5, counter))&", "&maakskoon(tempArray(6, counter))&", "&maakskoon(tempArray(7, counter))&", &#039;"&maakskoon(tempArray(8, counter))&"&#039;, "&maakskoon(tempArray(9, counter))&", "&maakskoon(tempArray(10, counter))&", &#039;"&maakskoon(tempArray(11, counter))&"&#039;, &#039;"&maakskoon(tempArray(12, counter))&"&#039;, "&maakskoon(tempArray(13, counter))&", &#039;"&maakskoon(tempArray(14, counter))&"&#039;, &#039;"&maakskoon(tempArray(15, counter))&"&#039;, &#039;"&maakskoon(tempArray(16, counter))&"&#039;, &#039;"&maakskoon(tempArray(17, counter))&"&#039;, "&tempArray(18, counter)&")"<BR>&#039;response.write mysql<BR>realDataconn.execute(mysql)<BR>next<BR>&# 039;response.write (mysql)<BR>end function<BR><BR>connect()<BR>rukmembers()<BR>disco nnect()<BR><BR><BR>Please, I welcome all comments or suggestions on this problem.<BR><BR>Thanks<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: moving data between tables

    I can only suggest that you response.write your query to the screen before executing it, and see where that unclosed quotationmark is.<BR>Maybe you did not surround all your strings with single quotes?

  3. #3
    jyts Guest

    Default RE: moving data between tables

    That&#039;s not hte problem. The query runs in a loop.<BR>It gets about halfway through all the records and only then bombs out. <BR>Strange thing is, when I do the same using DTS , it gets an error on the same spot !

  4. #4
    Sreedharan Surendran Guest

    Default RE: moving data between tables

    I had a similar problem once. I will give you the situation, and how I solved it.<BR><BR>Like you, I inserted records into a temporary table from a text file. The temporary table had the same columns as the real table, except that every column datatype was set to Varchar (to the maximum length in the text file). <BR><BR>(Here I would suggest that you keep both tables in the same database, and then do a direct insert, so that there is a single insert statement. You do not have to loop. Your process will complete much faster.)<BR><BR>I used the following script:<BR><BR>"Insert Into RealTable (Code, Name, Qty, Amount) Select Code, Name, Qty, Amount From TempTable "<BR><BR>Like you mentioned, I got an error. Upon investigating, I found that there were some incompatible characters. Examples are character data in numeric fields, Dollar signs or commas in amount fields.<BR><BR>So what did I do? I gave a where clause:<BR><BR>"Insert Into RealTable (Code, Name, Qty, Amount) Select Code, Name, Qty, Amount From TempTable Where PatIndex(&#039;%$%&#039;, Amount) = 0"<BR><BR>The above statement will exclude those rows which have dollar sign in the Amount column. The rest of the rows will be inserted.<BR><BR>To get the "BAD" rows, you can do the reverse.<BR><BR>"Insert Into CheckTable (Code, Name, Qty, Amount) Select Code, Name, Qty, Amount Where PatIndex(&#039;%$%&#039;, Amount) &#062; 0"<BR><BR>Now you have the good data, and the bad data.<BR><BR>Note that you have to give the WHERE CLAUSE for each column which can have incompatible data. You can debug it by including each column one by one in the INSERT statement, until finally you have all the columns. In this way, you can find out which column has the offending data. You can use the PatIndex function for all the offending characters also.<BR><BR>By the way, you should consider using a stored procedure instead of embedded SQL.<BR><BR><BR><BR><BR><BR><BR>

  5. #5
    Join Date
    Dec 1969

    Default RE: moving data between tables

    Does your data contain any special characters? If the data is string, then it may contain something like Mc&#039;Daniels. This wouls cause the query to bomb. What I would suggest is to do a Replace(string,"&#039;","&#039;&#039;") to all the string data values in your query.<BR><BR>Hope this helps.<BR><BR>Cheers<BR>Santhosh<BR>santhosh_arvin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts