Create Table from query output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create Table from query output

Author

Topic

ukkz001
Starting Member

15 Posts

Posted - 2008-05-05 : 16:27:28

I have a query that is spliting up FullName into FistName and LastName columns. Need help writing a query that dump that output into a new table.

Thanks
Kevin

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-05 : 22:49:39

Try 'select ... into new_table from curr_table where ...'.

ukkz001
Starting Member

15 Posts

Posted - 2008-05-06 : 09:52:03

It's not working. Not sure of the proper syntax to use for this query:

SELECT
CASE WHEN CHARINDEX(' ',Exec_Director) > 0 THEN SUBSTRING(Exec_Director,CHARINDEX(' ',Exec_Director)+1,LEN(Exec_Director)-CHARINDEX(' ',Exec_Director)) ELSE '' END AS Dir_LastName,
CASE WHEN CHARINDEX(' ',Exec_Director) > 0 THEN LEFT(Exec_Director,CHARINDEX(' ',Exec_Director)-1) ELSE Exec_Director END AS Dir_FirstName,
CASE WHEN CHARINDEX(' ',Primary_Contact) > 0 THEN SUBSTRING(Primary_Contact,CHARINDEX(' ',Primary_Contact)+1,LEN(Primary_Contact)-CHARINDEX(' ',Primary_Contact)) ELSE '' END AS Cont_LastName,
CASE WHEN CHARINDEX(' ',Primary_Contact) > 0 THEN LEFT(Primary_Contact,CHARINDEX(' ',Primary_Contact)-1) ELSE Primary_Contact END AS Cont_FirstName
From Orgs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-06 : 10:53:06

quote:
Originally posted by ukkz001

It's not working. Not sure of the proper syntax to use for this query:

SELECT
CASE WHEN CHARINDEX(' ',Exec_Director) > 0 THEN SUBSTRING(Exec_Director,CHARINDEX(' ',Exec_Director)+1,LEN(Exec_Director)-CHARINDEX(' ',Exec_Director)) ELSE '' END AS Dir_LastName,
CASE WHEN CHARINDEX(' ',Exec_Director) > 0 THEN LEFT(Exec_Director,CHARINDEX(' ',Exec_Director)-1) ELSE Exec_Director END AS Dir_FirstName,
CASE WHEN CHARINDEX(' ',Primary_Contact) > 0 THEN SUBSTRING(Primary_Contact,CHARINDEX(' ',Primary_Contact)+1,LEN(Primary_Contact)-CHARINDEX(' ',Primary_Contact)) ELSE '' END AS Cont_LastName,
CASE WHEN CHARINDEX(' ',Primary_Contact) > 0 THEN LEFT(Primary_Contact,CHARINDEX(' ',Primary_Contact)-1) ELSE Primary_Contact END AS Cont_FirstName
From Orgs

Are you trying to put this result into another table? WHere's the INTO TableName in your query? What is error that you're getting?

ukkz001
Starting Member

15 Posts

Posted - 2008-05-06 : 11:34:28

Yes. I'm trying to populate a new table called "Contacts" with the output from the query I posted.

Thanks,
Kevin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-06 : 11:35:55

quote:
Originally posted by ukkz001

Yes. I'm trying to populate a new table called "Contacts" with the output from the query I posted.

Thanks,
Kevin

What was error obtained? Also did you specify the values in same order of your destination table?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-06 : 22:28:59

Tried this?

SELECT
CASE WHEN CHARINDEX(' ',Exec_Director) > 0 THEN SUBSTRING(Exec_Director,CHARINDEX(' ',Exec_Director)+1,LEN(Exec_Director)-CHARINDEX(' ',Exec_Director)) ELSE '' END AS Dir_LastName,
CASE WHEN CHARINDEX(' ',Exec_Director) > 0 THEN LEFT(Exec_Director,CHARINDEX(' ',Exec_Director)-1) ELSE Exec_Director END AS Dir_FirstName,
CASE WHEN CHARINDEX(' ',Primary_Contact) > 0 THEN SUBSTRING(Primary_Contact,CHARINDEX(' ',Primary_Contact)+1,LEN(Primary_Contact)-CHARINDEX(' ',Primary_Contact)) ELSE '' END AS Cont_LastName,
CASE WHEN CHARINDEX(' ',Primary_Contact) > 0 THEN LEFT(Primary_Contact,CHARINDEX(' ',Primary_Contact)-1) ELSE Primary_Contact END AS Cont_FirstName
into Contacts
From Orgs

If Contacts exists already, should use:

insert Contacts ...
SELECT
CASE WHEN CHARINDEX(' ',Exec_Director) > 0 THEN SUBSTRING(Exec_Director,CHARINDEX(' ',Exec_Director)+1,LEN(Exec_Director)-CHARINDEX(' ',Exec_Director)) ELSE '' END AS Dir_LastName,
CASE WHEN CHARINDEX(' ',Exec_Director) > 0 THEN LEFT(Exec_Director,CHARINDEX(' ',Exec_Director)-1) ELSE Exec_Director END AS Dir_FirstName,
CASE WHEN CHARINDEX(' ',Primary_Contact) > 0 THEN SUBSTRING(Primary_Contact,CHARINDEX(' ',Primary_Contact)+1,LEN(Primary_Contact)-CHARINDEX(' ',Primary_Contact)) ELSE '' END AS Cont_LastName,
CASE WHEN CHARINDEX(' ',Primary_Contact) > 0 THEN LEFT(Primary_Contact,CHARINDEX(' ',Primary_Contact)-1) ELSE Primary_Contact END AS Cont_FirstName
From Orgs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources