选择进入高级
我的数据库中有许多表,我正在使用以下代码收集计算值,并希望将这些值插入到其他表中。我正在使用 SELECT INTO 方法,但数据库告诉我“关键字 INTO 行附近的语法不正确...”。我相信我缺少一些东西,但不确定在哪里。代码看起来不错。这是我的代码。任何帮助将不胜感激。
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;
好的,我使用了 INSERT INTO 语法,一切都很顺利,但现在我遇到了日期时间问题。当我使用以下 sql 命令时,我收到错误,它显示“无法将数据类型位转换为日期时间”我尝试了 Martin 的强制转换方法,但它是相同的。
我的代码是
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;
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;
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;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
注意:下面的答案是假设 SQL Server 编写的。我在收到 VistaDB 说明时删除了它,但在阅读后再次取消删除它
在这种情况下,我认为可以肯定地说,如果它在 SQL Server 中无效,那么它在 VistaDB 中也会无效吗?这是 SQL Server 中的无效语法。
这段代码的目的是什么?它是一个布尔值吗? (即当列与变量匹配时返回 true)。如果是这样,在 SQL Server 中最接近的就是这个。
编辑从评论中我看到它的目的是
另外我没有看到
SELECT ... INTO
此处列为 VistaDB 命令。是肯定支持的吗?NB: Below answer was written assuming SQL Server. I deleted it when receiving the VistaDB clarification but have undeleted it again upon reading that
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.
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.
Edit From the comments I see it is intended to be
Additionally I don't see
SELECT ... INTO
listed as a VistaDB command here. Is it definitely supported?如果您使用的是 SQL Server,
Insert Into
更适合插入到动态创建的表中。这是一种不错的做事方式,但如果表已经存在,那么我会使用这个: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: