0

I have the following code that returns the last value in a table Column 'MatchID':

SELECT TOP 1 MatchID FROM BetfairFootballDB..Match
ORDER BY MatchID DESC;

I have another table called 'Data' with a Foreign Key column, also named MatchID. I have imported data from a csv file, to a temporary table, which contains all of the correct data except for the MatchID. I have added the MatchID column to the imported data table, but all of the values in that column are currently NULL. I need to write the queried value from the code above, to all cells in the MatchID column of my imported data, so that I can then finally write all of the data to my 'Data' table. Please could somebody let me know how this can be done in SQL Server. Thanks

EDIT: As requested, here is my temporary table. Note that the number of rows of data could be any length:

FirstTimeTaken          LatestTimeTaken         Market                  Outcome         Odds    NumberOfBets    VolumeMatched   InPlay  MatchID
2013-03-08 14:04:24.000 2013-03-08 15:22:57.000 Over/Under 3.5 Goals    Over 3.5 Goals  4       4               58              1       NULL
2013-03-08 14:40:08.000 2013-03-08 15:03:09.000 Match Odds              The Draw        5       45              405             1       NULL
2013-03-08 13:20:37.000 2013-03-08 13:20:37.000 Half Time/Full Time     Draw/Wigan      6       2               5               0       NULL
2013-03-08 10:17:21.000 2013-03-08 10:17:37.000 DRAW NO BET             Wigan           2       5               36              0       NULL
Steve W
  • 1,108
  • 3
  • 13
  • 35
  • 3
    share your table structure with a few rows – Ehsan Sajjad Mar 18 '14 at 17:37
  • Do you want to copy the values of MatchId from your BetfairFootballDB to TempTable (which has csv data) ? For that you will need to be able to join the two tables. Do you have any columns in both tables which can be used for the join ? – Erran Morad Mar 18 '14 at 17:42
  • If the first query returns "36", you want "36" in every row in the table? – Joe Mar 18 '14 at 17:47
  • possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) – Joe Mar 18 '14 at 17:48
  • Borat - The single value that I get back from my query above is the MatchID for all of the data in the temp table. So I need to write the same MatchID to all rows of the MatchID column in my table so that all of the data is then tied together by the Foreign Key. Does that make sense? – Steve W Mar 18 '14 at 17:50
  • Yes Joe - I just took a look at the link you posted and I think with that code you must know how many rows of data you have. I am trying to automate the import of some data from csv files and the table could have any number of rows. I need to be able to populate every row of the MatchID column with the Integer value returned by my query. – Steve W Mar 18 '14 at 17:51

1 Answers1

0
     update DATA
     Set MATCHID = (SELECT TOP 1 MatchID FROM BetfairFootballDB..Match
                   ORDER BY MatchID DESC)

or

 INSERT INTO DATA
(
MATCH ID
)

SELECT TOP 1 MatchID FROM BetfairFootballDB..Match
ORDER BY MatchID DESC;
Mahesh Sambu
  • 349
  • 2
  • 15