SELECT INTO advanced

Question

I have many tables in my database and I am collecting calculated values with following code and would like to Insert those values into other table. I am Using SELECT INTO method but database tells me that "Incorrect syntax near the keyword INTO line ...". I believe that there is something I am missing but not sure where. Code looks fine. Here is my code. Any help would be appreciated.

SELECT (second.[cdate]=@enddate) AS 'Date', first.[machine_no] AS 'No', 
tbl_machines.[manufacturer] As 'Manufacturer',
tbl_machines.[type] As 'Machine Type',tbl_machines.[game_name] AS 'Game Name',
tbl_machines.[accounting_denomination] AS 'Denom', 
(second.[turnover])-(first.[turnover]) AS 'Turnover',
(second.[total win])-(first.[total win]) AS 'Total win',
(second.[games played])-(first.[games played]) AS 'Games Played',
(second.[Bill in])-(first.[Bill in]) AS 'Bill In', 
(second.[credit in])-(first.[credit in]) AS 'Credit IN', 
(second.[cancel credit])-(first.[cancel credit]) AS 'Cancel Credit',
tbl_rate.[euro] AS 'euro rate',
tbl_rate.[dollar] AS 'dollar rate' 
INTO  tbl_daily
FROM tbl.meter first,tbl.machines,tbl_rate  
INNER JOIN tbl_meter second ON first.[Machine_No] = second.[Machine_No] 
AND 
tbl_machines.[local_no]=first.[machine_no]
WHERE first.[cDate] = @StartDate 
AND second.[cDate] = @EndDate 
AND tbl_rate.[cdate]=@enddate;

Answer 1

NB: Below answer was written assuming SQL Server. I deleted it when receiving the VistaDB clarification but have undeleted it again upon reading that

VistaDB can be thought of as a subset of Microsoft SQL Server T-SQL. All of our syntax is supported in SQL Server, but not the other way around

In that case I assume it is safe to say that if it is invalid in SQL Server it will be invalid in VistaDB also? This is invalid syntax in SQL Server.

SELECT (second.[cdate]=@enddate) AS 'Date'

What is the purpose of this bit of code? Is it meant to be a boolean? (i.e. return true when the column matches the variable). If so in SQL Server the closest to that would be this.

SELECT CAST((CASE WHEN second.[cdate]=@enddate THEN 1 ELSE 0 END) AS BIT) AS 'Date'

Edit From the comments I see it is intended to be

SELECT @enddate AS 'Date'

Additionally I don't see SELECT ... INTO listed as a VistaDB command here. Is it definitely supported?

Answer 2

If you are using SQL Server, Insert Into is more for inserting into a table that is created on the fly. It is an ok way of doing things but if the table already exists then I would use this:

INSERT INTO table (column1, columns2, ...)
SELECT
  Value1,
  Value2,
  ...
FROM ...

Answer 3

Ok, I used INSERT INTO syntax, everything is going well but now I have problem with datetime. When I used following sql command I am getting error and it says " Cannot convert data type bit to datetime" I tried Martin's cast method but it's same.

My code is

INSERT INTO tbl_daily SELECT tbl_machines.[ID] AS 'ID', (second.[cdate]=@enddate) AS 'CDate', first.[machine_no] AS 'No',
 tbl_machines.[manufacturer] As 'Manufacturer',
tbl_machines.[type] As 'MachineType',
tbl_machines.[game_name] AS 'GameName',
tbl_machines.[accounting_denomination] AS 'Denom', 
(second.[turnover]-first.[turnover]) AS 'Turnover',
(second.[total win]-first.[total win]) AS 'Totalwin',
 (second.[games played]-first.[games played]) AS 'GamesPlayed', 
(second.[credit in]-first.[credit in]) AS 'CreditIN',
 (second.[Bill in]-first.[Bill in]) AS 'BillIn', 
 (second.[cancel credit]-first.[cancel credit]) AS 'CancelCredit',
tbl_rate.[euro] AS 'eurorate',
tbl_rate.[dollar] AS 'dollarrate' 
 FROM tbl_meter first,tbl_machines,tbl_rate 
INNER JOIN tbl_meter second ON first.[Machine_No] = second.[Machine_No] AND tbl_machines.[local_no]=first.[machine_no] 
WHERE first.[cDate] = @StartDate AND second.[cDate] = @EndDate AND tbl_rate.[cdate]=@enddate;