September 15, 2009 at 4:45 am
Hi,
Wondering if anyone can help me, I'm self taught and still new, but looking to transfer some rows from one database to another. The problem I'm having is that the application is legacy and doesn't make use of the autonumber (identity) feature in SQL Server 2005. Instead it manually generates the primary key numbers. In this instance I want to use a newer application with the same database to transfer rows from one database to another. I have tried to get the PK's by selecting the max and adding 1, it works for one row, but not multiple rows. The unique fields are 'PTR' and 'JOBNO'. Lines 12 and 13 are the ones that need attention, I assume it is just getting the one value and repeating it rather than refreshing it for each row.
Any help much appreciated:
use am4_coc
declare @weeknumber as int
set @weeknumber = 200923
declare @todaysdate as int
set @todaysdate = (SELECT (DATEDIFF(dd,0,GETDATE()) + 36163))
insert into am4_cod.dbo.jobs(
PTR, JOBNO, DATE, CUS_ID, NURS_ID, [FROM], [TILL], GRADE, DUTY,
WEEKNO, TS_FROM, TS_TILL, BRK, [HOURS], PRATE, IRATE, COMM, PAY,
IPAY, MILEAGE, MILE_RATE_P, MILE_RATE_I, NI_WEEKNO, TAX_IND, HOUR_IND,
VAT_IND, DETAIL, ODATE, OBY, CNOTE)
SELECT
(select max(ptr) +1 from am4_cod.dbo.jobs as PTR),
(select max(jobno) + 1 from am4_cod.dbo.jobs as JOBNO),
DATE, jobs.CUS_ID, 'Co C Transfer' as NURS_ID, [FROM], [TILL], jobs.GRADE, DUTY,
WEEKNO, TS_FROM, TS_TILL, BRK, [HOURS], PRATE, IRATE, COMM, PAY,
IPAY, MILEAGE, MILE_RATE_P, MILE_RATE_I, NI_WEEKNO, TAX_IND, HOUR_IND,
VAT_IND,'LCOC TRANSFER' as DETAIL, @todaysdate as ODATE, 'SYSTEM' as OBY, NURS_ID as CNOTE
from am4_coc.dbo.jobs
join am4_coc.dbo.customer on am4_coc.dbo.customer.cus_id=am4_coc.dbo.jobs.cus_id
where
am4_coc.dbo.customer.flag3 = 'D'
and (bookno <> '0' or bookno <> '')
and weekno = @weeknumber
Matt
September 15, 2009 at 11:34 am
Hi Matt,
please follow the link in my signature on how to post sample data.
Based on what you've provided so far I think it's a job for the ROW_NUMBER function.
For a more detailed answer please provide ready to use data as described in the link.
September 15, 2009 at 11:39 am
EDIT: Another instance of me typing faster than I can think. Sorry.
September 15, 2009 at 12:12 pm
Dunster-
From what I understand you've got the following: A table that ends at say 20,000 in an "identity column" and you'd like to start from there when you're inserting values into it from another database?
I'd suggest creating an intermediate table that has an identity column that starts at the highest current value (I.E. 20,000). You can specify that like this:
CREATE TABLE dbo.MikeTest
(
IDENT int IDENTITY (20000, 1),
...,
...
)
This table needs all columns minus the two that are your pseudo-identities. Remove both of those, they will be taken care with the IDENT column above. Insert all columns into this table (you're going to need to name them all into the SELECT, no using *). This will now have your identity column that is automatically incremented started at 20,000.
After you've got this, move all these records into your end table. The IDENT column will hold the values you're looking to insert into both the PTR and JB columns.
September 15, 2009 at 12:50 pm
Hi Mike,
I wouldn't use an intermediate table just to get some ID values...
There are much more powerful and less resource intensive options available (like ROW_NUMBER).
The intermediate table would need to hold the data permanently (which is simply duplicating the data without any need) or you'd have to DELETE the data in the table (not TRUNCATE the table, since you'd start with 1 again). Again, a resource intensive task. Or you'd have to do some math when transferring the data (get the current max(ID) from the target table and add the ID from the staging table).
September 15, 2009 at 3:01 pm
I Agree, I would firstly put the Max + 1 for each column into variables then use ROW_NUMBER() to assign the valid value.
Dave
September 15, 2009 at 3:23 pm
Avoid using MAX + 1, really...! This way seems to work with one user in a test environment but it's no solution for production system. First issue always selecting MAX before insert of a new row is quiet slow. Second issue between your SELECT and your INSERT there might be another connection which does the same and you get a primary key violation.
If you are not able to use an IDENTITY column, use a ID-table, get the last valid ID and increment it by the range of IDs you currently need. To avoid many table locks on this ID-table use a caching mechanism.
Greets
Flo
September 16, 2009 at 2:49 am
do the numbers have to be 1,2,3,4,5,6,7,9 or can they be 1,2,3,4,51,52,53,91,92 ?
assuming you have no option to adjust how this was build you could reserve an area of numbers for your application by increasing the max value with a big step. issue is that you need to monitor your not running out of numbers.
example:
your db is at 504. you increase it to 5001. now you have a free range from 505 till 5000 to use in your application. later you could increase it again to make a new range.
a more specific example/variation: you have 500 rows to insert and want 'm all in one set based run: the counter is at 1105: increase the row at the start with 500 and insert your rows
it's probably not a optimal thing (i'm no experienced sql coder) but you could give it a thought.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply