joining tables - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

joining tables

Author

Topic

donjt81
Starting Member

11 Posts

Posted - 2005-03-30 : 12:34:40

I have a query that joins 2 tables as follows

SELECT CELL_L1.Date, CELL_L1.Hour, [timingAdvanceAverage]/[connectionDurationTchCum] AS TA
FROM CELL_L1 INNER JOIN CELL_RR ON (CELL_L1.Date = CELL_RR.Date)
WHERE (((CELL_L1.timingAdvanceAverage)<>0) AND ((CELL_RR.connectionDurationTchCum)<>0) AND (([timingAdvanceAverage]/[connectionDurationTchCum])<>"error"));

now I want to save the result of this query as a table, lets say table1.
how do i do this

please help

TimS
Posting Yak Master

198 Posts

Posted - 2005-03-30 : 12:41:01

SELECT CELL_L1.Date, CELL_L1.Hour, [timingAdvanceAverage]/[connectionDurationTchCum] AS TA
into table1
FROM CELL_L1 INNER JOIN CELL_RR ON (CELL_L1.Date = CELL_RR.Date)
WHERE (((CELL_L1.timingAdvanceAverage)<>0) AND ((CELL_RR.connectionDurationTchCum)<>0) AND (([timingAdvanceAverage]/[connectionDurationTchCum])<>"error"));

I DO NOT RECOMMEND Using SELECT INTO in production code; note it is possible that your DB setting will NOT permit the above code

Tim S

Edit: Under SQL 7.0 enabling SELECT INTO affected the DB backup modes allowed; I know this changed under SQL 8.0 so it is not as much of a problem, but I am NOT DBA enough to know if it is a good idea.

donjt81
Starting Member

11 Posts

Posted - 2005-03-30 : 14:05:48

Thanks Tim,

do you know how I could make the user enter the name of the table and use that table name in the query.

so lets say I have a text field on a form names field1.
field1.value needs to be the name of the table.

How do i do this

TimS
Posting Yak Master

198 Posts

Posted - 2005-03-30 : 19:54:34

I am a SQL Developer, I don't do the GUI stuff much.

You might look into using Dynamic SQL something like below.

SELECT CELL_L1.Date, CELL_L1.Hour, [timingAdvanceAverage]/[connectionDurationTchCum] AS TA
into #tmp_table
FROM CELL_L1 INNER JOIN CELL_RR ON (CELL_L1.Date = CELL_RR.Date)
WHERE (((CELL_L1.timingAdvanceAverage)<>0) AND ((CELL_RR.connectionDurationTchCum)<>0) AND (([timingAdvanceAverage]/[connectionDurationTchCum])<>"error"));

DECLARE @sql_code NVARCHAR(500);

SET @sql_code = 'SELECT Date, Hour, TA INTO ' + QUOTENAME(@table_name) + ' FROM #tmp_table'

EXEC (@sql_code)

Tim S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources