Databases
StackOverflow2013
Postsdbo
POInserting batch of records into one table in SQL Server 2008 which contains composite key
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInserting batch of records into one table in SQL Server 2008 which contains composite key
    primarykey
    data
    text
    <p>I am new to spring source and SQL Server, sorry for this question. I am trying to insert rows into a table (<code>IN136_DMV_USED_CAR_SALES</code>). This table has a composite key which is connected to another parent table <code>EXT_INF_LOAD</code>.</p> <p>So far, I was able to read from an input file and load all rows to <code>IN136_DMV_USED_CAR_SALES</code> table which has two keys: <code>EXT_INF_LOAD_ID</code> and <code>IN136_ID</code> with the following SQL statement:</p> <p>INSERT statement:</p> <pre><code> String sql = "insert into dbo.IN136_DMV_USED_CAR_SALES (EXT_INF_LOAD_ID, IN136_ID, " + "DEALER_NUM, " + "VSL_PURCHASE_DATE," + "OWNERSHIP_ISSUED_DATE, " + "LAST_TRANSFER_DATE, " + "ODOMETER_CURRENT_DATE, " + "WORK_DATE, " + "PURCHASE_PRICE, " + "VIN_HIN, " + "CURRENT_LICENSE, " + "REG_OWNER_NAME, " + "REG_OWNER_ADDRESS2, " + "REG_OWNER_ADDRESS3, " + "REG_OWNER_CITY_OR_STATE," + "LEGAL_OWNER_NAME," + "LEGAL_OWNER_ADDRESS2," + "LEGAL_OWNER_ADDRESS3," + "LEGAL_OWNER_CITY_OR_STATE," + "MAKE_BUILDER," + "YEAR_MODEL," + "VLF_CLASS," + "IRP_ACCOUNT_MX_NUM," + "IRP_ACCOUNT_NUM," + "IRP_ACCOUNT_USDOT_NUM," + "IRP_FLEET_MX_NUM," + "IRP_FLEET_NUM," + "IRP_FLEET_USDOT_NUM," + "IRP_VEH_MX_NUM," + "IRP_VEH_USDOT_NUM) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; JdbcTemplate jdbctempl = new JdbcTemplate(dataSource); jdbctempl.update(sql, new Object[] {id1, id2++, rec.getDealerNum(), rec.getVslPurchaseDate(), rec.getOwnershipIssueDate(), rec.getLastTransferDate(), rec.getOdometerCurrentDate(), rec.getWorkDate(), rec.getPurchasePrice(), rec.getVinHin(), rec.getCurrentLicense(), rec.getRegOwnerName(), rec.getRegOwnerAddress2(), rec.getRegOwnerAddress3(), rec.getRegOwnerCityOrState(), rec.getLegalOwnerName(), rec.getLegalOwnerAddress2(), rec.getLegalOwnerAddress3(), rec.getLegalOwnerCityOrState(), rec.getMakeBuilder(), rec.getYearModel(), rec.getVlfClass(), rec.getIrpAccountMxNum(), rec.getIrpAccountNum(), rec.getIrpAccountUsdotNum(), rec.getIrpFleetMxNum(), rec.getIrpFleetNum(), rec.getIrpFleetUsdotNum(), rec.getIrpVehMxNum(), rec.getIrpVehUsdotNum()}); </code></pre> <p>My problem is that in <code>IN136_DMV_USED_CAR_SALES</code> (the child table), one of the columns of the composite key (<code>EXT_INF_LOAD_ID</code>) is connected to the <code>EXT_INF_LOAD</code> parent table.</p> <p>So, I guess what I really need to do (but I don’t know how) is: </p> <ol> <li><p>The information about <code>EXT_INF_ID</code> will be passed via parameter (I am calling it "counter" for now). I will have to read <code>EXT_INF</code> table, to see if I can find a match (<code>Counter = EXT_INF_ID</code>). So now that I found the row in <code>EXT_INF</code> table, I can get the value of <code>EXT_INF_NAME</code> column to populate in <code>EXT_INF_LOAD</code> table (<code>EXT_INF_FILE_NAME</code> column). </p></li> <li><p>Since I know the row exists in the <code>EXT_INF</code> table, I can insert a new row into the parent table (<code>EXT_INF_LOAD</code>) first, before starting to add new rows into the child table (<code>IN136_DMV_USED_CAR_SALES</code>).<br> The new row to be inserted into the parent table will have the following values:<br> 2.a <code>EXT_INF_ID = Counter</code>,<br> 2.b <code>EXT_INF_FILE_NAME = EXT_INF_NAME</code> (from the <code>EXT_INF</code> table)<br> 2.c <code>EXT_INF_LOAD_DATE</code> = todays-date<br> 2.d But what about the for <code>EXT_INF_FILE_NAME</code>? </p></li> <li><p>That way, I will be able to keep adding other new input files into <code>IN136_DMV_USED_CAR_SALES</code> table, as they arise. And as long as I use the same value <code>EXT_INF_LOAD_ID</code> for both : <code>EXT_INF_LOAD</code> (parent table) and <code>IN136_DMV_USED_CAR_SALES</code> (child table), right? </p></li> </ol> <p>But then I realized that (2.d) cannot also be passed via another <code>counter2</code> parameter for example, because the values of <code>EXT_INF_ID</code> and <code>EXT_INF_LOAD_ID</code> have to be unique, i.e., no duplicates are allowed... </p> <p>I was hoping that someone could help me find the correct way to create all the required SQL statements because I have been having so much problems / exception errors.(I am also new to SQL Server) </p> <p>Thanks in advance for your interest in this matter. Sorry could not upload a picture of the tables, but here are the SQL statements for all 3 tables:</p> <p>Table 1: <strong>EXT_INF</strong></p> <pre><code>SELECT [EXT_INF_ID] ,[EXT_INF_NAME] ,[EXT_INF_DESC] FROM [boeafspdev1].[dbo].[EXT_INF] where EXT_INF_ID = counter </code></pre> <p>Table 2: </p> <pre><code>SELECT [EXT_INF_ID] ,[EXT_INF_LOAD_ID] ,[EXT_INF_FILE_NAME] ,[EXT_INF_LOAD_DATE] ,[EXT_INF_COMMENTS] FROM [boeafspdev1].[dbo].[EXT_INF_LOAD] where EXT_INF_ID = counter </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USWanda Larangeira
    1. USWanda Larangeira
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POInserting batch of records into one table in SQL Server 2008 which contains composite key
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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