New answers tagged sequence
This should be good up to about 2,500 values (depending on version): ;WITH x(n) AS ( SELECT TOP (@variableNumberOfIdsNeeded) (ROW_NUMBER() OVER (ORDER BY number)-1) * CONVERT(BIGINT, @SeqIncr) + CONVERT(BIGINT, @FirstSeqNum) FROM master.dbo.spt_values ORDER BY number ) --INSERT @newIds([NewId]) SELECT n FROM x; If you need more, or ...
I managed to produce a working trigger based on ypercube's help. create or replace TRIGGER ROOT_PHYSICIAN_TRG BEFORE INSERT ON UNIQUE_PHYSICIAN REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF INSERTING AND :NEW.is_root_phys = 1 THEN :NEW.root_id := :NEW.unique_id; END IF; END;
If you have a condition that specifies 1005 whether root_id has to get the same value as unique_id, you could use a TRIGGER. Something like this: CREATE OR REPLACE TRIGGER root_unique_trg BEFORE INSERT ON UNIQUE_PHYSICIAN FOR EACH ROW WHEN (NEW.is_root_phys = 1) BEGIN :NEW.root_id := :NEW.unique_id ; END ; Tested in SQLfiddle
My suggestion would be to create a table like this Create Table RelationTable ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, report_id INT UNSIGNED, reportType TINYINT ); WHERE id = unique id report_id = unique id in table1 or table2 reportType = 1 for table1 and 2 for table2 now insert your data into table1 and table2 and then insert the ...
If the two tables share ID space, surely then they should have a common parent table? I would create a parent table and make the two other tables children of it with foreign keys. The parent has the autoincrement field, and you have to insert there first before inserting the child. Add a "type" column to the parent table so you know in which table to look ...
Top 50 recent answers are included