Select Into help

Question

I want to select from a table with the following columns (title, firstname, surname, email, state, pcode, question1, question1a, question1b, question3, dob, timetocall, contactname) into another table which has a few more columns which should just have null because they don't have equivalent columns in the table i want to select from. IS this possible?

In the table I am selecting from the last 2 columns are timetocall which will either have (AH or BH) and contactname (which is a phone number) When timetocall is AH I want contactname to be inserted into the column HomePHone in the table I am inserting into and when it is BH I want contactname to go into the BusinessPHone field.

Are these things possible in one query?

Answer 1

You can use the following statement,

Insert Into AnotherTable(title, firstname, surname, email, state, pcode, question1, question1a, question1b, question3, dob, timetocall, contactname)

Select title, firstname, surname, email, state, pcode, question1, question1a, question1b, question3, dob, timetocall, contactname From MainTable

Answer 2

That should work great thanks!

the "another table" doesn't have timetocall or contact name though, it has homephone and businessphone and I need to insert into one of these the value in contactname depending on the value in timetocall in the main table.

 

Answer 3

Ohh Yes, the following query will help you..

Insert Into AnotherTable(title, firstname, surname, email, state, pcode, question1, question1a, question1b, question3, dob, homephone ,businessphone )
Select title, firstname, surname, email, state, pcode, question1, question1a, question1b, question3, dob, Case When timetocall = 'AH' Then Contactname End,Case When timetocall = 'BH' Then contactname End From MainTable

Answer 4

Thanks works really well,

I hate to be a pain, but I want to use this in a stored procedure which also has 2 values sent in that will will propulate two of the columns in "anothertable" with the rest populate as above from the "Maintable"

How do I add that to the above query???

Answer 5

You have to convert that SP into UDF (Function). Bcs you can't use the SPs in the queries..But function will do..

for example,

Create Function dbo.GetContactName(@TimetoCall, @ContactName,@DesiredTimeToCall)
Returns varchar(100)
as
Begin
Declare @Result as Varchar(100);
Select @Result = Case  When @TimeToCall = @DesieredToCall Then @ContactName End
Return @Result
End;

go

Select dbo.GetContactName(TimetoCall, ContactName, 'AH') as HomePhone, dbo.GetContactName(TimeToCall,ContactName,'BH') as BusienssName From Maintable

 

Answer 6

No what you told me last time is perfect and it looks like this; but i am not sure how to insert the campaigncode into the lists table and also the processed date (I know I can use GetDate() but I am not sure where to do this because the rest of the columns are selected from another table?

CREATE Procedure ListsImport(
@campaigncode varchar(100)
)
AS
INSERT INTO Lists
(
title, firstname, lastname, emailaddress, homephone, businessphone, state, postcode, winetype, red, white, price, dob -- here i need to insert campaigncode and date
)
SELECT
title, firstname, surname, email, Case When timetocall = 'AH' Then Contactname End, Case When timetocall = 'BH' Then contactname End, state, pcode, question1, question1a, question1b, question3, dob
FROM
emailcash
WHERE
NOT Processed = '1'

Answer 7

Its something like this..

CREATE Procedure ListsImport(
@campaigncode varchar(100)
)
AS
INSERT INTO Lists
(
title, firstname, lastname, emailaddress, homephone, businessphone, state, postcode, winetype, red, white, price, dob,campaigncode,date
)
SELECT
title, firstname, surname, email, Case When timetocall = 'AH' Then Contactname End, Case When timetocall = 'BH' Then contactname End, state, pcode, question1, question1a, question1b, question3, dob,@campaigncode, getdate()
FROM
emailcash
WHERE
NOT Processed = '1'