Complicated insert
-
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
-
Does table 2 contain any data currently?
If you need to control the value of EmpNo in table2, you should set it to be an INT, but not an IDENTITY.
Can you write a SELECT query which pulls the results from table 1 that you wish to INSERT?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Yes, table2 already has data.
Table2 EmpNo is a PK with INT datatype and also has identity seed.
I want a insert into select Query(EmpName, Dept, Loc) where I can get all the rows, but the ones with location "xx", needs to be inserted twice.
-
SELECT ca1.*
FROM dbo.Table1 t1
CROSS APPLY (
SELECT EmpNo, EmpName, DeptName, Location
UNION ALL
SELECT EmpNo, EmpName, DeptName, Location
WHERE DeptName = 'xx'
) AS ca1SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Thnk u, this resolved my issue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply