Hi,
I need to compare database tables that use a sequence value in a column.
This value is not transportable between environments.
On the insert I need to include a query to get the next sequence value, thats pretty easy to work around.
But once I have inserted a record into a table I need to run a procedure to update the sequence value table. It is not a valid assumption that the sequence numbers used are truly sequential. ( The first insert may give you sequence # 1 but the 2nd Insert could be #4 as there were 2 other inserts triggered from the first insert. Vendors rules not mine.)
Anyway a bulk insert is not an option and I think I can over come most of these issue if DBForge can generate individual insert statements.
Is generating individual insert statements possible? ie a batch of 1
As a future enhancement could selecting a "post insert action" be included to cater for this exact scenario?
I dont care if the "post insert action" simply creates a place holder so that I can then go through the script and perform a find & replace.
Data Compare - Individual inserts
-
alexa
Re: Data Compare - Individual inserts
Could you please clarify whether the sequence object introduced in the new SQL Server 2012 was implied by 'a sequence value in a column'?
-
mungburger
- Posts: 3
- Joined: Mon 29 Oct 2012 05:45
Re: Data Compare - Individual inserts
No, not sequence nbrs in sql2012. Its a vendor specific implementation.
Each insert gets a number whatever is next in the sequence. The sequences may or may not be maintained per table. This is why between each insert i need to run an explicit stored procedure to update a table. inserting multiple records in a atch is not possible. only single insert can be done.
Here's a sample of what the Generated SQL would look like.
Insert into Tbla (Seqid, col2, col3,...)
Select GetNextID(TableName, ColumnName), Val2, Val3,...;
go
EXEC UpdateNextID TableName, ColumnName;
go
Insert into Tbla (Seqid, col2, col3,...)
Select GetNextID(TableName, ColumnName), Val2, Val3,...;
go
EXEC UpdateNextID TableName, ColumnName;
Each insert gets a number whatever is next in the sequence. The sequences may or may not be maintained per table. This is why between each insert i need to run an explicit stored procedure to update a table. inserting multiple records in a atch is not possible. only single insert can be done.
Here's a sample of what the Generated SQL would look like.
Insert into Tbla (Seqid, col2, col3,...)
Select GetNextID(TableName, ColumnName), Val2, Val3,...;
go
EXEC UpdateNextID TableName, ColumnName;
go
Insert into Tbla (Seqid, col2, col3,...)
Select GetNextID(TableName, ColumnName), Val2, Val3,...;
go
EXEC UpdateNextID TableName, ColumnName;
-
alexa
Re: Data Compare - Individual inserts
Such functionality is not implemented in the product. However, we will review this when developing future versions.