Need Help Inserting Records | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need Help Inserting Records

I have the following scenario that I need help with. I need to update an existing table by insert missing records. Existing Data ID Code Date Type
0000000130591SLS1999-01-01 7SM
0000000130591SLS1999-01-01 4CT
0000000130591SLS2003-02-01 UV4
5000001130591SLS1999-01-01 000
5000001133211Q902001-01-01 2P8
5000001130591SLS2004-01-01 2PI The four fields make up the primary key of the table.
The Resulting Records should be: ID Code Date Type
0000000130591SLS1999-01-01 7SM
0000000130591SLS1999-01-01 4CT
0000000130591SLS2003-02-01 UV4
0000000130591SLS2003-02-01 7SM
0000000130591SLS2003-02-01 4CT
5000001130591SLS1999-01-01 000
5000001133211Q902001-01-01 2P8
5000001130591SLS2001-01-01 000
5000001130591SLS2004-01-01 2PI
5000001133211Q902004-01-01 2P8
5000001130591SLS2004-01-01 000 Hopefully I can explain what should happen clearly. I need to create new records within the same ID group where the combination of Code and Type have a date earlier than an existing Code, Type and Date combination. Thanks in advance.
Unless I am missing something, you should not have a problem inserting new rows as long as you do not have duplicate keys. INSERT INTO tablename VALUES (‘00000000′,’130591SLS’,’1/1/1998′,’7SM’) should work. Your index will determine in what order the rows are returned to a query.
Hi ya, I think that the following select should give you the rows you want, you can then feed this into an insert into tbl select… to insert the ‘missing’ rows select a.id, a.code, a.date, a.type
from tbl a
inner join tbl b
on a.id = b.id
and a.code = b.code
and a.date < b.date
Cheers
Twan
Thanks for all your help I was able to generate my records.
]]>