Databases
StackOverflow2013
Postsdbo
POINSERT w/subquery INTO another table - overflow error
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POINSERT w/subquery INTO another table - overflow error
    primarykey
    data
    text
    <p>I've been trying to insert data from one Access table to a linked SQL Server table using the following SQL statement from <a href="https://stackoverflow.com/questions/1468736/sql-can-i-insert-some-fields-from-variables-other-from-another-table">this</a> question, modified for my purposes - but I keep getting 'Overflow' as an error message:</p> <pre><code>INSERT INTO dbo_tblGageActivity(strGageID, strCustJobNum, datDateEntered, datTimeEntered) SELECT [Gage ID] as GageID, [Customer Job#] as JobNum, [Date] as DateEntered, [Time Entered] as TimeEntered FROM TblInstTrak; </code></pre> <p>I've tried this a number of ways, all resulting in the 'overflow' error. I must be missing something, but for the life of me, I don't know what. Is >100,000 records just too much for one Insert subquery to handle?</p> <p>-- Edited 01/25/2011 @ 1540 hours --</p> <p>The data types and sizes of the fields are as follows:</p> <pre><code>TblInstTrak Type:Size Required dbo_tblGageActivity Type:Size Required ---------------------------------------------------------------------------------------------------- Gage ID Text:50 True strGageID Text:50 True Customer Job# Text:50 False strCustJobNum Text:50 True Date Date/Time:8 False datDateEntered Date/Time:8 True Time Entered Date/Time:8 False datTimeEntered Date/Time:8 True </code></pre> <p>-- Edited 01/26/2011 @ 1355 hours --</p> <p>Alright - I've been banging my head on this for a few days (before I came to SO with it, and the day it's been here) and the ONLY differences I can come up with is that the Date Entered field on the local app side is formatted to 'm/d/yyyy' and the Time Entered field on the local app side is formatted to 'Long Time' - neither of these fields in the SQL Server table have any format set.</p> <p>For performance reasons, I am starting to wonder if there would be a better way to pull all the current data now to get the data into the table - then only pull updates from the local app into the mirror SQL Server copy. No data is entered into the SQL Server side copy, it's only used for performance for my test application - the only data that changes is on the local application side. Would this approach be easier than wiping the SQL Server database table and repopulating it every time I want to update it? </p> <p>-- Edited 01/28/2011 @ 1236 hours --</p> <p>After finding some dates from around the Roman Empire days (thanks David Fenton for pointing me onto this) I modified my import SQL statement to the following:</p> <pre><code>INSERT INTO dbo_tblGageActivity(strGageID, strCustJobNum, datDateEntered, datTimeEntered) SELECT [Gage ID] as GageID, [Customer Job#] as JobNum, [Date] as DateEntered, [Time Entered] as TimeEntered FROM TblInstTrak WHERE [Date] &gt;=1/1/1900; </code></pre> <p>I got a couple really great answers, and I can only chose one answer (<em>mental anguish</em>) so please don't get pissed - this really REALLY helped me learn.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USComrad_Durandal
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAre you able to insert any rows at all?
      singulars
      1. POINSERT w/subquery INTO another table - overflow error
      1. USMatthew
    2. COWhat are the datatypes of your source and destination columns?
      singulars
      1. POINSERT w/subquery INTO another table - overflow error
      1. USJoe Stefanelli
    3. CONope, it works for about two minutes - then throws the overflow error - and nothing is the result in the destination table (dbo_tblGageActivity)
      singulars
      1. POINSERT w/subquery INTO another table - overflow error
      1. USComrad_Durandal
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload