Another tricky SQL question

Results 1 to 2 of 2

Thread: Another tricky SQL question

  1. #1
    Join Date
    Dec 1969

    Default Another tricky SQL question

    To me, anyway. Please bear with me on this one, and thanks if you can help (again).<BR><BR>Turns out that some records I have in a SQL server DB need to be broken up into 2 separate records. For example:<BR><BR>1 &#124 FOO BAR &#124 HAHA<BR><BR>needs to be broken up into<BR><BR>1 &#124 FOO &#124 HAHA<BR>1 &#124 BAR &#124 HAHA<BR><BR>And I can accomplish that. What I&#039;m having a b*tch of a time with is a dependent table. <BR><BR>Another table contains the primary Key of the record from the first table, as well as a value from the ORIGINAL record before it was split. Plus, I need to know what the new IDENTITY is, so that I can insert that new identity, plus the ORIGINAL value from the ORIGINAL record, into the dependent table.<BR><BR>What I need to do is get that value. I&#039;ve tried using @@IDENTITY, but it is only returning the last record&#039;s IDENTITY value. <BR><BR>Here is what I have so far:<BR><BR>&#039; this inserts the rows I need to split<BR>insert into ACCOUNTSTEMP (ACCT_NUM, ACCT_TYPE, STORE_NAME, STORE_CITY, <BR>STORE_STATE, STORE_ZIP, STORE_CONTACT, CONTACT_PHONE, CONTACT_FAX, <BR>CONTACT_EMAIL, COMMENTS, REGION_ID, REP_CODE, ACTIVE_STATUS)<BR><BR>&#039; the criteria i need to pick from<BR> SELECT ACCT_NUM, &#039;FABRIC&#039;, STORE_NAME, STORE_CITY, STORE_STATE, <BR> STORE_ZIP, STORE_CONTACT, CONTACT_PHONE, CONTACT_FAX, CONTACT_EMAIL, <BR> COMMENTS, REGION_ID, REP_CODE, ACTIVE_STATUS<BR> FROM ACCOUNTSTEMP<BR> WHERE (ACCT_TYPE = &#039;LEATHER FABRIC&#039;)<BR><BR>&#039; here is where things get really foggy<BR>insert into INVOICESBACKUP (ACCT_ID, INVOICE_DUE_DATE, INVOICE_REC_DATE, <BR>INVOICE_STATUS, INVOICE_DISC, INVOICE_CRR_STATUS_ONE, INVOICE_CRR_STATUS_TWO, <BR>INVOICE_CRR_STATUS_THREE, INVOICE_CRR_STATUS_FOUR)<BR><BR> SELECT @@identity, I.INVOICE_DUE_DATE, I.INVOICE_REC_DATE,<BR> I.INVOICE_STATUS, I.INVOICE_DISC, I.INVOICE_CRR_STATUS_ONE, <BR> I.INVOICE_CRR_STATUS_TWO, I.INVOICE_CRR_STATUS_THREE, I.INVOICE_CRR_STATUS_FOUR<BR> FROM INVOICESBACKUP I<BR> WHERE DATEPART(m, I.INVOICE_DUE_DATE) = 5 or<BR> DATEPART(m, I.INVOICE_DUE_DATE) = 6<BR><BR>did that make any sense at all?

  2. #2
    Join Date
    Dec 1969

    Default The part I don't get...

    Since the new table will have multiple records with an ID field (that is the same as the old PK field, I presume?), how do you convert the dependent table to only refer to one of those records? Do you have to double up the records in the dependent table, too?<BR><BR>I&#039;m not clear on why you couldn&#039;t just continue to use the (now doubled) ID field in the split-up table. Don&#039;t bother to add a new PK to that table. <BR><BR>Now your dependency table depends on *two* records from the split up table, but so what?<BR><BR>And your last query makes no sense at all.<BR><BR>You might be returning 100 records (or more). Yet you want to put the same value (the current @@IDENTITY value) into each record??? What for?<BR><BR>

Posting Permissions

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