create a temp table that lists all tables in target databaseĬREATE TABLE #ChkSumTargetTables ( varchar(250), varchar(50), chksum int) parameter = if no table name was passed do them all, otherwise just check the one Thanks to answers below for pointing me in the right direction. It worked so well we're doing it on every table in each database. here is the exact approach I ended up taking. But, I'd like to explore the hash idea a little further if possible.įor any future vistors. One approach that intrigues me is this creative use of the union statement. We have Red-Gate data compare but since the tables in question contain millions of rows each I'd like something a little more performant. I'm talking both schema and data.Ĭan I do a hash on the table it's self like I would be able to on an individual file or filegroup - to compare one to the other. What is the quickest way to verify that those tables (on two different servers) are in fact identical. When all is said and done there are a bunch of tables that should be identical. The data of the players was taken from Premier League's homepage as of today () from the teams Burnley FC and Arsenal.We're doing an ETL process. This is a very quick example bases on some assumptions. Retrieve Simple Match Overview SELECT m.id, m.matchdate, p.name Insert Selected Unpivoted Data into MatchPlayer insert into matchplayer Select Match.id as match_id, Match.away_player3 as player_id from Match ![]() Select Match.id as match_id, Match.away_player2 as player_id from Match Select Match.id as match_id, Match.away_player1 as player_id from Match Select Match.id as match_id, _player3 as player_id from Match Select Match.id as match_id, _player2 as player_id from Match (select Match.id as match_id, _player1 as player_id from Match Selected Unpivoted Data select match_id, player_id from ![]() This is the table we are going to use to join afterwards create table MatchPlayer and some data to go with it: insert into Match (id, matchdate, home_player1, home_player2, home_player3, away_player1, away_player2, away_player3) I've created a small example on dbfiddle to show you how you could unpivot the data from the match table into a new table to retrieve the player's name with slightly more ease. You always get a full list of players that participated in a given match. It doesn't matter if you have only 11, 10 or 12 players for that match. ![]() You then JOIN on the match_id and then on the player_id. This simplistic table would consist of the two columns match_id and player_id. If it helps, the database is in sqlite and it does not matter if all the names end up in one column with a separator between them.Ī better (design) solution would be to have another table named matchplayer. Is there any convenient way to do what i want? This is both inconvenient and in my opinion very inefficient as the number of rows is large. Where _player1 = p1.id and _player2 = p2.idĪnd if I want to get all the 22 players, i have to do 22 joins. which will be: select Match.id, Match.date, p1.name, p2.name Now if I want to get for example player2's name too, I have to do another join. to do so, I have to join these two tables 22 times, one for each player.įor example to get player1's name: select Match.id, Match.date, p1.name In the "Match" table, I have player ids in their respective columns, lets say I want a Query that shows the names of the Players in each match.(I want to replace their ids with their names). In "Player", There are two columns: Player.id and Player.name and in the "Match" table I have many columns related to about 20000 soccer matches, 22 of them relating to the players that have played in a particular match. I have two tables named "Player" and "Match".
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |