0

I came across this situation while migrating our DB from Foxpro to SQL.

Below is the sample scenario.. (My original code is little complicated so just tried to create my own example)

I created several table variables in my sql server function to get what I require.

DECLARE @temp_1 TABLE(ID INT,
Name NCHAR(7),
bday DATE,
m_status NVARCHAR(10)); 

INSERT INTO @temp_1
SELECT Name, bday, m_status from Employee_Info


DECLARE @temp_2 TABLE(ID INT,
City NCHAR(7),
Country NVARCHAR(10),
zip NVARCHAR(10)); 

INSERT INTO @temp_2
SELECT City, Country, zip from Employee_Address

Next comes a situation where I need to use one each of the fields from the above table variables to derive data into my third table variable.

For example,

DECLARE @temp_full TABLE (
Name NCHAR(7),
City NCHAR(7));

INSERT INTO @temp_full
SELECT @temp_1.Name, @temp_2.City FROM @temp_1, @temp_2 WHERE @temp_1.ID = @temp_2.ID

[EDITED TO INCLUDE INSERT USING JOINS]

INSERT INTO @temp_full
SELECT @temp_1.Name, @temp_2.City FROM @temp_1 INNER JOIN @temp_2 ON @temp_1.ID = @temp_2.ID

However, when I do @temp_1.Name, @temp_2.City I get an error saying I must delcare the scalar variable @temp_1, @temp_2.

Can anyone please tell me how to resolve this problem.

Thank you for your help.

rock
  • 585
  • 3
  • 10
  • 26
  • There could be other issues here, but are you trying to run these queries one at a time or are you running both at once? – markA Jun 28 '12 at 19:37
  • IMplicit joins area SQL antipattern. YOu shoudl alwys use explicit joins. THis is the 21st century and this was added to the ANSII standard in 1992. Would you use code in an differnt language that had been replaced in 1992 with something better? – HLGEM Jun 28 '12 at 20:01
  • Shouldn't you relate these two tables then join temp_1 to temp_2 in your last select? – Jerry Jun 28 '12 at 20:27
  • forgive my logic.. I should have used joins.. I just wanted to know why am I not able to use fields from @ temp_1, @ temp_2 in @temp_full.. im running the queries at once from a stored proc.. – rock Jun 28 '12 at 21:38

1 Answers1

2

This is not foxpro and @variable_tables are not cursors like in foxpro, there are no any current record pointer for table, tables are sets of records and you must work with it as with sets.

If you need to fill @temp_full with records combined from tables @temp_1 and @temp_2, you must use select with joining this two tables like this:

INSERT INTO @temp_full
SELECT 
    temp_1.Name, 
    temp_2.City 
FROM @temp_1 temp_1 
JOIN @temp_2 temp_2 on temp_2.ID = temp_1.ID

Also note that you must use alias for variable tables

isevcik
  • 553
  • 1
  • 4
  • 15
  • @fanosek.. for some strange reason my logic did not work until i used aliases for my table variables.. if I remove the aliases for the table variables sql throws "must delcare the scalar variable @ temp_1, @ temp_2" error.. any insight on why this happens? Thanks so much for your help ! – rock Jun 28 '12 at 22:00