I have this SQL:
INSERT INTO VersionProduction (AddUser,AddDate,ModUser,ModDate,Date,Completed,Started,Ended,Synchronization,Note,NoteForAdministrator,
NoteForDBA,NoteForProductManager)
SELECT 958,Temp.Date,958,Temp.Date AS Date2,Temp.Date AS Date3,1,Temp.Date AS Date4,Temp.Date AS Date5,'','','','','' FROM
(SELECT CONVERT(CHAR(4),YEAR(Version.Date))+CONVERT(CHAR(2),MONTH(Version.Date))+CONVERT(CHAR(2),DAY(Version.Date)) AS NoName,
MAX(Version.Date) AS Date,COUNT(*) AS NoName2
FROM Version WHERE Date>'1899-12-30 00:00:00.000'
GROUP BY CONVERT(CHAR(4),YEAR(Version.Date))+CONVERT(CHAR(2),MONTH(Version.Date))+CONVERT(CHAR(2),DAY(Version.Date))) Temp
ORDER BY Date DESC
It groups all records from Version, based on the Date field, on a single day and insert a record into VersionProduction.
So, at this point, I have 760 records from Version and 106 records from VersionProduction.
So, each record in VersionProduction represents a relationship with one or many records in Version.
Now, the trick is to enhance that SQL so each record in the Version table will get a value in Version.NoVersionProduction, thus the newly added record for each record that is being inserted into VersionProduction. Because, I need to have a relationship between the two.
So, there are two challenges. The first one is to grab the primary key VersionProduction.Numero and plug that into one or several records in Version.NoVersionProduction. The second one is that this is originally from a GROUP BY, we will have to locate back the records in Version based on the date of the group by.
Anyone would have an idea on how to enhance that or maybe there is a better approach.