Insert INTO (AHHHH!)
-
Thanks for everyones help on the Charindex....
However,
I must be missing something, I want to take the results put them into a temp table then insert into a non-temp table the values
The problem is , record 1,2,3 has LastName
the second insert does the First name but puts it on line 4-5-6, then the initals go into 7-8-9
I want it to insert the records on the same line, since the rows are the same in the temp tabe as the non....
I think my mind hurts and I am just missing something simple..
I've been messing around really but this dumb code bellow is what I have gotten to so far.
use RonnieProd
GO
drop table #emp
Create Table #emp
(
ID INT IDENTITY(1,1) NOT NULL,
Initals [varchar](4),
Name [varchar](50) NULL,
FirstName [varchar](30) NULL,
LastName [varchar](50) NULL
)
Insert into #emp (Initals,[Name])
Select Initals,[Name]
from DirectorsNames
Select * from #emp
use productionDB
go
drop table emp
Create Table emp
(
ID INT IDENTITY(1,1) NOT NULL,
Initals [varchar](4),
FirstName [varchar](30) NULL,
LastName [varchar](50) NULL,
[Role] [int],
[Status] [int]
)
insert into emp (lastname)
select lastname = LEFT([name] , CHARINDEX( ' ', [name],1 )-2)
from #emp as r
where r.id = id
insert into emp (firstname)
SELECT firstname =RIGHT( name , CHARINDEX(' ',reverse(name),1)-1)
from #emp as t
where t.id = id
insert into emp (Initals)
SELECT Initals
from #emp as p
where p.id = id
update emp
set [Role] ='1'
update emp
set [status] ='1'
Select * from emp
-
Is this what you want?
insert into emp (lastname, firstname, initials, [role], [status])
select LEFT([name] , CHARINDEX( ' ', [name],1 )-2),
RIGHT( name , CHARINDEX(' ',reverse(name),1)-1),
Initials, 1, 1
from #emp as r
I'm not sure why you want the intermediate step with the temp table though.
-
I'm pretty sure this is what you want:
[font="Courier New"]USE RonnieProdGO
DROP TABLE #emp
CREATE TABLE #emp
(
ID INT IDENTITY(1,1) NOT NULL,
Initals [varchar](4),
Name [varchar](50) NULL,
FirstName [varchar](30) NULL,
LastName [varchar](50) NULL
)
INSERT INTO #emp (Initals,[Name])
SELECT Initals,[Name]
FROM DirectorsNames
SELECT * FROM #emp
USE productionDB
GO
DROP TABLE emp
CREATE TABLE emp
(
ID INT IDENTITY(1,1) NOT NULL,
Initals [varchar](4),
FirstName [varchar](30) NULL,
LastName [varchar](50) NULL,
[Role] [int],
[Status] [int]
)
INSERT INTO emp
(
lastname,
firstname,
initials,
role,
status
)
SELECT
lastname = LEFT([name] , CHARINDEX( ' ', [name],1 )-2),
firstname =RIGHT( name , CHARINDEX(' ',REVERSE(name),1)-1),
initials,
1,
1
FROM
#emp
SELECT * FROM emp
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Yes, that is it, I dont know what my problem was... DUH:hehe:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply