Resolved Complicated insert

[mansoorabid]

I want to insert values from Table1 to Table2

Conditions: when thier is a location with "xx", the row needs to be inserted twice

the Empno in table2 is a primary key with identity seed.



Expected Output

 

[jmcilhinney]

Are you implying that the duplicated records need to be inserted with sequential IDs? If so then you really shouldn't be using an identity in the first place because they are for columns where the values don't actually matter. If you don't care about the order then just do two queries to get all the data and then just the "xx" data. If you do care about the order then you could sort the data first or use a cursor in a loop and insert twice if the value is "xx".

 

[Skydiver]

Unless Table2 is dropped or cleared out every time you insert from Table1 to go into Table2, it looks like things will get complicated relatively quickly when you people with duplicate names and locations. Imagine Table1:

EmpNo, EmpName, DeptName, Location
:
253, John Smith, xx, usa
:
315, John Smith, xx, usa

After first Table1 to Table2 insertion, Table2 may look like:

EmpNo, EmpName, DeptName, Location
:
400, John Smith, xx, usa
401, John Smith, xx, usa
:
627, John Smith, xx, usa
628, John Smith, xx, usa

So later Table1 gets updated to add another "John Smith" in department xx in the USA:

EmpNo, EmpName, DeptName, Location
:
253, John Smith, xx, usa
:
315, John Smith, xx, usa
:
352, John Smith, xx, usa

How to update Table2?