SQL Insert

[ArizonaState]

I am trying to do 2 inserts in a stored proc. One is straight forward. The other is an insert into Table A from values in Table B. Here is my code. While the first part works well it does not seem to read the values from Table B...
<CODE

BEGIN

INSERT INTO TableA
(LocationID, SeatID, SeatNumber, DateScheduled)

VALUES
(1, 12, 109, 9/26/2007)

END

BEGIN

INSERT INTO TableA
(StartTime, EndTime)

--using "Values keyword throws an error)

SELECT StartTime, EndTime FROM TableB
WHERE LocationID = 1

END

GO

I tried combining the two INSERTs into one statement... didn't work!

Thanks for your help!!:)
Phoenix

[ck9663]

I am trying to do 2 inserts in a stored proc. One is straight forward. The other is an insert into Table A from values in Table B. Here is my code. While the first part works well it does not seem to read the values from Table B...
<CODE

BEGIN

INSERT INTO TableA
(LocationID, SeatID, SeatNumber, DateScheduled)

VALUES
(1, 12, 109, 9/26/2007)

END

BEGIN

INSERT INTO TableA
(StartTime, EndTime)

--using "Values keyword throws an error)

SELECT StartTime, EndTime FROM TableB
WHERE LocationID = 1

END

GO

I tried combining the two INSERTs into one statement... didn't work!

Thanks for your help!!:)
Phoenix

if you do this, you'll have a record in Table A with empty LocationID, SeatID, SeatNumber, DateScheduled fields but have values for StartTime, EndTime. Maybe the error that's showing are the constraint for those empty fields.

[ArizonaState]

if you do this, you'll have a record in Table A with empty LocationID, SeatID, SeatNumber, DateScheduled fields but have values for StartTime, EndTime. Maybe the error that's showing are the constraint for those empty fields.

Thanks for responding! But the resulting record is exactly the opposite of what you said - the fields that are Null are the StartTime and EndTime. But the direction I'm currently thinking is an UPDATE statement with the just INSERTed fields in the WHERE clause. I am trying to do that as a nested SProc, but I am still getting errors because it does not seem to want to update TableA with records from TableB. Let me know if I am approaching this right or if there is an easier way out.

My new UPDATE statement...

CODE...

UPDATE TableA

SET StartTime = TableB.StartTim e, EndTime = TableB.EndTime
WHERE LocationID = 11 (Whatever LocationID I had specified in the original INSERT statement)

/CODE


Thanks!!

Phoenix

[ck9663]

Thanks for responding! But the resulting record is exactly the opposite of what you said - the fields that are Null are the StartTime and EndTime. But the direction I'm currently thinking is an UPDATE statement with the just INSERTed fields in the WHERE clause. I am trying to do that as a nested SProc, but I am still getting errors because it does not seem to want to update TableA with records from TableB. Let me know if I am approaching this right or if there is an easier way out.

My new UPDATE statement...

CODE...

UPDATE TableA

SET StartTime = TableB.StartTim e, EndTime = TableB.EndTime
WHERE LocationID = 11 (Whatever LocationID I had specified in the original INSERT statement)

/CODE


Thanks!!

Phoenix

the reason that it's the opposite is because the second insert failed. question:

1. are you trying to insert all these values in a single record?

2. what error is showing up when you do this in a single insert?

[ArizonaState]

the reason that it's the opposite is because the second insert failed. question:

1. are you trying to insert all these values in a single record?

2. what error is showing up when you do this in a single insert?

I am splitting teh statement into two parts.
Part 1. Direct insert from user input.
Part 2. Input retrieved from a second table, Table B.

While the Part 1 works just finr, Part 2 is where I am only getting a NULL.

It is not really showing any error. Just that the StartTiem and EndTime that are in the second part of the statement don't get executed. But I guess I will follow another route...

-- Part 2

CODE

INSERT INTO TableA ( StartTime, EndTime )

SELECT StartTime, EndTime

FROM TableB

WHERE LocationID IN ('12')

/CODE

Haven't tried it yet, but I guess I will try it tomorrow. Will keep you posted. In the meanwhile, any help is appreciated!

Thanks,

Phoenix

[azimmer]

I am splitting teh statement into two parts.
Part 1. Direct insert from user input.
Part 2. Input retrieved from a second table, Table B.

While the Part 1 works just finr, Part 2 is where I am only getting a NULL.

It is not really showing any error. Just that the StartTiem and EndTime that are in the second part of the statement don't get executed. But I guess I will follow another route...

-- Part 2

CODE

INSERT INTO TableA ( StartTime, EndTime )

SELECT StartTime, EndTime

FROM TableB

WHERE LocationID IN ('12')

/CODE

Haven't tried it yet, but I guess I will try it tomorrow. Will keep you posted. In the meanwhile, any help is appreciated!

Thanks,

Phoenix

Where (i.e. in which fields) are you getting the NULLs? If you execute two INSERTs, you'll end up with two new rows. If you want to insert to both sets of data into one row, you can combine the two INSERTs into one like this:

INSERT INTO TableA
(LocationID, SeatID, SeatNumber, DateScheduled,StartTime, EndTime)
SELECT 
    1 as LocationID,
    12 as SeatID,
    109 as SeatNumber,
    '9/26/2007' as DateScheduled,
    StartTime,
    EndTime
FROM   TableB
WHERE  LocationID=1

[ArizonaState]

Where (i.e. in which fields) are you getting the NULLs? If you execute two INSERTs, you'll end up with two new rows. If you want to insert to both sets of data into one row, you can combine the two INSERTs into one like this:

INSERT INTO TableA
(LocationID, SeatID, SeatNumber, DateScheduled,StartTime, EndTime)
SELECT 
    1 as LocationID,
    12 as SeatID,
    109 as SeatNumber,
    '9/26/2007' as DateScheduled,
    StartTime,
    EndTime
FROM   TableB
WHERE  LocationID=1

Actually, I figured a solution...

I had to inner join the two tables and do an insert on the resultset.

CODE..

SELECT t1.LocationID, t1.SeatNumber, t1.ScheduledDat e, t2.StartTime, t2.EndTime

FROM TabelA t1

INNER JOIN TableB t2

ON t1.LocationID = t2.LocationID

Declare @ LocationID int, @SeatNumber int, @ScheduledDate datetime, @StartTime datetime, @EndTime datetime

select @StartDate = StartDate FROM TableB
Select @EndDate = EndDate FROM TableB

INSERT INTO TableA
LocationID, SeatNumber, ScheduledDate, StartDate, EndDate

VALUES
@LocationID, @SeatNumber, @ScheduledDate, @StartDate, @EndDate

CODE/

This worked and the Sproc produced the desired result. But it remains to be seen if this can work the way it is intended to. I would like to not supply the StartDate and EndDate (as is parameterized in this)and would like the procedure to retrieve the values from the other table (TableB) on its own.

Thanks again.

Phoenix