November 9, 2007 at 7:04 am
declare @jobname varchar(30), @jobrole varchar(30)
set @jobname='select jobname from job'
set @jobrole='select jobrole from job'
insert into emp2
(empid,empname,jobname,jobrole)
select emp,empname,@jobname,@jobrole from emp1
I wud like to do tht, but may be syntax is wrong...how will it work?
November 9, 2007 at 7:23 am
hmm... several issues.
First - unless you plan on inserting the literal 'select jobname from job' (meaning - the STRING "'select jobname from job'") into every jobname, the syntax is wrong.
Assuming you're looking to have that execute - you need to use dynamic SQL. But in order for that to work, the string you return needs to be a valid SQL statement. Even if that did what you were hoping for,
insert into emp2
(empid,empname,jobname,jobrole)
select emp,empname,select jobname from job,select jobrole from job from emp1
isn't a valid SQL statement. It's not built right, and is missing some rather crucial components as well.
You need to read up on how to build a JOIN statement. Once you understand how to do that and WHY to do that, then we can worry about making it a dynamic statement.
Questions to get you started:
- how does the JOB table relate to the EMP1 table?
- what happens when you don't JOIN two tables?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 9, 2007 at 8:42 am
its a hard coding...
jobname,jobrole will be same for all the rows in emp
November 9, 2007 at 8:50 am
Mike Levan (11/9/2007)
its a hard coding...jobname,jobrole will be same for all the rows in emp
Does this have anything to do with your tableA-tableB thing? If *all* emp records in emp2 will get the same jobname and jobrole you can do an update statement easily
UPDATE emp2
SET jobname = 'JobNameHere',
jobrole = 'JobRoleHere'
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply