August 20, 2018 at 9:09 am
Hi!
I have a bit of a problem for a while. I have updated tables in Database A. These tables gets updated from an external source. Now, I have a Database B containing the tables I have in Database A, BUT not the same amount of columns, nor updated data. Database B tables have less columns than A's tables.
The idea is to have Database B getting updated with an insert every time a new table has been updated in Database A. There is a 'update list' table in Database C that contains GUID, Table name, and TimeStamp. The goal is to use a cursor that loops through the Update List Table. Same table can occur many times in this list.
Here is what I have so far:
DECLARE @TargetTable NVARCHAR(MAX);
DECLARE EntitiesInsert CURSOR FOR
select distinct ao.name from DatabaseA.sys.all_columns ac --I Guess I can also use 'SELECT distinct SourceTargetName FROM DatabaseC.dbo.Update_List
inner join DatabaseA.sys.all_objects ao on ao.object_id= ac.object_id
inner join DatabaseC.dbo.Update_List sync on sync.SourceName = ao.name
inner join DatabaseB.sys.all_objects ao2 on sync.TargetName = ao2.name
inner join DatabaseB.sys.all_columns ac2 on ac2.name= ac.name and ac2.object_id=ao2.object_id
OPEN EntitiesInsert
FETCH NEXT FROM EntitiesInsert
INTO @TargetTable
WHILE @@FETCH_STATUS=0
BEGIN
Declare @InsertInto NVARCHAR(MAX)
SET @InsertInto = 'insert into LFTEST_MSCRM_DW.Inno.'+@TargetTable+' ('+(@Columns)+') values'+()+''';' --I can get a list of all values in a variable, but how to connect that to the right table and columns?
--Been trying with a cursor within a cursor below, but I cant wrap my head around it
-- DECLARE ColumnCursor CURSOR FOR
--....
--OPEN ColumnCursor
--FETCH NEXT FROM ColumnCursor
--INTO @ColumnFix
--WHILE @@FETCH_STATUS=0
--BEGIN
--...
--EXECUTE sp_executesql ....
END
FETCH NEXT FROM EntitiesInsert
INTO TargetTable
END
CURSORCLOSE EntitiesInsert
DEALLOCATE EntitiesInsert
August 20, 2018 at 9:23 am
How are you mapping between tables and columns in database A and database B? Are the table names the same? Are the columns that need to be updated named the same?
August 20, 2018 at 9:56 am
Thanks for quick reply Lynn!
yes there is a problem with the connection of tables and columns as you say. I have created a variable that lists all the columns I need from database A. As well as a variable that lists the tables I need data from in database A (SourceTable if you will). Problem is connecting the columns with correct table between the two variables..If its even achievable.
Table names are almost the same. But I have that solved with all_objects so I have a variable that lists the SourceTable. Can easily do a TargetTable. also the Update_List contains both Targettable name with matching SourceTable name
Column names are the same.
August 21, 2018 at 12:58 pm
Honestly, a cursor within a cursor, that effectively needs to be dynamic, just seems a tad much to me... Too difficult to create easily, and overly complex to troubleshoot. Pretty sure I don't want to even TEST such a thing. I might well opt for triggers on the necessary tables. Is it a larger amount of work? Maybe. But probably a lot easier to introduce, fix, and maintain. Cursors, by their very nature, are row by agonizing row. If you're going to do anything all in one fell swoop and use a cursor anyway, you would probably be best served by creating a single table that holds your mappings. You really do NOT need a cursor within a cursor for that, but will need dynamic SQL.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 22, 2018 at 6:14 am
whats the purpose of database B?
you mention Database B has a different schema to database A, is this by design or do you modify the schema before your insert?
***The first step is always the hardest *******
August 23, 2018 at 1:27 am
Database A is updated data, like a snapshot. Database B is supposed to be more streamlined with history tables, and only necessary columns
Sorry, best answer I can give as of now :/
Im thinking a while loop might help?
August 24, 2018 at 8:49 am
coolasice24 - Thursday, August 23, 2018 1:27 AMDatabase A is updated data, like a snapshot. Database B is supposed to be more streamlined with history tables, and only necessary columns
Sorry, best answer I can give as of now :/Im thinking a while loop might help?
If you use a CURSOR, you're going to have one of those anyway. Again, why not use triggers?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 28, 2018 at 1:24 am
Hmmm will have to check out Triggers. Im new to those too 😀
Anyway, I made a solution I thought I post here. The databases names have of course changed, but I think they are correct here. I will take a closer look and correct if wrong later:
I am, however, a bit unsure about the delete statement further down in the code.
I have a TempTable based on the update list. I want to delete the rows in the update list after Ive inserted into tables. I have one with two 'where field IN (corresponding field in TempTable' clauses and another one with a 'delete from ... where exists (tempTable). Temptable has two columns but update list has three in total. Whichever is fastest/best?
DECLARE @InsertInto NVARCHAR(MAX);
DECLARE @SourceID NVARCHAR(MAX);
DECLARE @TableAttribute NVARCHAR(MAX);
DECLARE @SourceViewName NVARCHAR(MAX);
DECLARE @TargetTable NVARCHAR(MAX);
DECLARE @SourceTableName NVARCHAR(MAX);
/*-------Create temp table to be used in cursor-------*/
Declare @SQL_TempTable_Insert NVARCHAR(MAX);
IF OBJECT_ID('tempdb..#Cursor_TempTable') IS NOT NULL DROP TABLE #Cursor_TempTable
CREATE TABLE #Cursor_TempTable (
SourceEntity NVARCHAR(MAX) )
/*-------variable to be used in insert step below-------*/
SET @SQL_TempTable_Insert = 'SELECT SourceLogicalName FROM DataBaseC.dbo.REF_ENTITIES_SYNC group by SourceLogicalName'
/*-------Insert into temp table-------*/
INSERT INTO #Cursor_TempTable EXECUTE (@SQL_TempTable_Insert)
/*-------Create temp table from NeworUpdate table-------*/
Declare @SQL_TempTable_NewOrUpdated NVARCHAR(MAX);
IF OBJECT_ID('tempdb.. #TempTable_NewOrUpdated') IS NOT NULL DROP TABLE #TempTable_NewOrUpdated
CREATE TABLE #TempTable_NewOrUpdated (
[ID] NVARCHAR(MAX),
[TimeStamp] DATETIME )
/*-------variable to be used in insert step below in NewOrUpdate temp table-------*/
SET @SQL_TempTable_NewOrUpdated = 'SELECT ID, TimeStamp FROM DataBaseC.dbo.[REF_POSTS_NewOrUpdated] group by ID, TimeStamp'
/*-------Insert into NewOrUpdate temp table-------*/
INSERT INTO #TempTable_NewOrUpdated EXECUTE (@SQL_TempTable_NewOrUpdated)
/*-------Cursor segment-------*/
DECLARE EntitiesInsert CURSOR FOR
SELECT SourceEntity FROM #Cursor_TempTable
OPEN EntitiesInsert
FETCH NEXT FROM EntitiesInsert
INTO @TargetTable
--LOOP
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN TRY
BEGIN TRAN
SET @SourceViewName = (select SourceName from DataBaseC.dbo.REF_ENTITIES_SYNC where Targetname = @TargetTable);
SET @SourceTableName = (select SourceTableName from DataBaseC.dbo.REF_ENTITIES_SYNC where Targetname = @TargetTable);
SET @TableAttribute = stuff(( select ', ' +char(10)+ ac.[name] from DataBaseB.sys.all_columns ac
inner join DataBaseB.sys.all_objects ao on ao.object_id=ac.object_id
where ao.name = @TargetTable and ac.name not in ('ValidFrom','ValidTo')
FOR XML PATH('')
), 1, 1, '')
--Finds DataBaseA table's Primary Key
SET @SourceID = (select c.name
from sys.index_columns ic
inner join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
inner join sys.indexes i on ic.object_id = i.object_id and ic.index_id = i.index_id
inner join sys.tables t on i.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id
where i.is_primary_key= 1 and t.name = @SourceTableName);
SET @InsertInto = 'INSERT INTO DataBaseB.dbo.'+@TargetTable+' ('+@TableAttribute+')
SELECT '+@TableAttribute+' FROM DataBaseA.dbo.'+@SourceViewName+'
where '+@SourceID+' in (select nu.ID from DataBaseC.Inno.REF_ENTITIES_SYNC sync inner join #TempTable_NewOrUpdated nu on nu.SourceEntity = sync.TargetName where sync.TargetName = '''+@TargetTable+''' group by nu.ID )'
EXEC sp_sqlexec @insertInto
--Delete the records from [DataBaseC].[dbo].[REF_POSTS_NewOrUpdated] that we have inserted.
--DELETE FROM [DataBaseC].[dbo].[REF_POSTS_NewOrUpdated]
-- WHERE ID = (select [ID] from #TempTable_NewOrUpdated)
-- AND TimeStamp = (select [Timestamp] from #TempTable_NewOrUpdated)
----alt2
--DELETE FROM [DataBaseC].[dbo].[REF_POSTS_NewOrUpdated]
-- where exists (select * from #TempTable_NewOrUpdated)
--End TRAN
COMMIT
--End TRY
END TRY
--Catch possible errors
BEGIN CATCH
--IF there is an open transaction then roll back and print error messages.
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));
PRINT 'Actual error message: ' + CAST(@ErrorMessage AS VARCHAR(MAX));
PRINT 'Actual error Severity: ' + CAST(@ErrorSeverity AS VARCHAR(MAX));
PRINT 'Actual error State: ' + CAST(@ErrorState AS VARCHAR(MAX));
END CATCH
FETCH NEXT FROM EntitiesInsert
INTO @TargetTable
END
CLOSE EntitiesInsert
DEALLOCATE EntitiesInsert
GO
September 2, 2018 at 7:35 am
ooch, a bit of over processing, i guess your are looping through and passing different parameters into @SQL_TempTable_Insert
thats going to be really slow.
I would take a look at exactly what it is you want to achieve and look at using triggers
***The first step is always the hardest *******
September 3, 2018 at 4:33 am
As with the others:
A bit of overprocessing.
Maybe you should look at triggers to maintain the seconde database.
Maybe or maybe not usefull :
Use a view on the source table to get the same definition as on the second table. And use the view for further processing. This can be used to circumvent the different definitions (or column_names) in the two tables.
Then a large question which I start with when duplicating data from one table to another table:
How do you process deleted (and recreated) rows ?
If the system can not process deleted (and recreated) rows, there is often something wrong with the 'solution'.
If the datasets are small enough, the EXCEPT construction can be helpfull.
Say you have the views of the table A and the table B so that they have the same definition. You can run an EXCEPT statement to provide the differences.
For (very) large tables this is not efficient.
-- A_View the view of the table which is changed.
-- B_View the view of the 'old' table which is not changed yet. Same format as A_View
;
WITH
New as (SELECT * FROM A_View) -- The New table.
, Old as (SELECT * FROM B_View) -- The Old table
, R as (SELECT * FROM (
SELECT 'new' version, * FROM New
EXCEPT
SELECT 'new', * FROM Old
union
SELECT 'old', * FROM Old
EXCEPT
SELECT 'old', * FROM New
-- union -- This last part is optional, this shows the rows which not have changed.
-- SELECT 'Unchanged', * FROM Old -- Here it is quoted out as comment.
-- intersect
-- SELECT 'unchanged', * FROM New
)xxx)
SELECT * INTO RESULT_TABLE FROM R
The result table contains the rows which are OLD/NEW or both. With a bit of more code, this can be made in INSERT/UPDATE/DELETE statements.
Because of the views the columns are handled with the *. Columns in B not existing in A should recieve a 'default' value.
Ben
(Specific code is not tested, but copied from tested code).
September 4, 2018 at 5:09 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply