我有两个完全相同的表（一个在存档数据库中，另一个在常规数据库中）。我的目标是将所有数据合并到一个表中。我在想 insert 进入函数是我应该使用的，但也不知道它是否应该 join 或者 merge .
我尝试插入的代码是：
Insert into [DMSArchive].[dbo].[VEHICLE_TRANSACTION]
(CAST(TRANS_DATE_TIME as DATE) Date
,COUNT(CASE WHEN HOV_TAG_NUMBER = 0 THEN HOV_TAG_NUMBER ELSE NULL End) as "HOV"
,COUNT(CASE WHEN HOV_TAG_NUMBER >= 1 and HOV_TAG_NUMBER <= 15300
OR HOV_TAG_NUMBER >= 30000 and HOV_TAG_NUMBER <= 55999
OR HOV_TAG_NUMBER >= 65000 and HOV_TAG_NUMBER <= 199999
THEN HOV_TAG_NUMBER ELSE NULL End) as "ExpressPass"
,COUNT(CASE WHEN HOV_TAG_NUMBER >= 15301 and HOV_TAG_NUMBER <= 29999
OR HOV_TAG_NUMBER >= 56000 and HOV_TAG_NUMBER <= 64999
OR HOV_TAG_NUMBER >= 200000 and HOV_TAG_NUMBER <= 299999
THEN HOV_TAG_NUMBER ELSE NULL End) as "CDecal"
,COUNT(CASE WHEN HOV_TAG_NUMBER > 299999 THEN HOV_TAG_NUMBER ELSE NULL End) as "Unknown"
,COUNT (HOV_TAG_NUMBER ) as "Total Vehicles")
SELECT
CAST(TRANS_DATE_TIME as DATE) Date
,COUNT(CASE WHEN HOV_TAG_NUMBER = 0 THEN HOV_TAG_NUMBER ELSE NULL End) as "HOV"
,COUNT(CASE WHEN HOV_TAG_NUMBER >= 1 and HOV_TAG_NUMBER <= 15300
OR HOV_TAG_NUMBER >= 30000 and HOV_TAG_NUMBER <= 55999
OR HOV_TAG_NUMBER >= 65000 and HOV_TAG_NUMBER <= 199999
THEN HOV_TAG_NUMBER ELSE NULL End) as "ExpressPass"
,COUNT(CASE WHEN HOV_TAG_NUMBER >= 15301 and HOV_TAG_NUMBER <= 29999
OR HOV_TAG_NUMBER >= 56000 and HOV_TAG_NUMBER <= 64999
OR HOV_TAG_NUMBER >= 200000 and HOV_TAG_NUMBER <= 299999
THEN HOV_TAG_NUMBER ELSE NULL End) as "CDecal"
,COUNT(CASE WHEN HOV_TAG_NUMBER > 299999 THEN HOV_TAG_NUMBER ELSE NULL End) as "Unknown"
,COUNT (HOV_TAG_NUMBER ) as "Total Vehicles"
FROM [DMS].[dbo].[VEHICLE_TRANSACTION]
Where TRANS_DATE_TIME >= '2019-01-01'
GROUP BY
CAST(TRANS_DATE_TIME as DATE)
ORDER BY Date在我看来，这应该工作没有任何错误，但是，我得到的错误：
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.我试过加逗号，去掉，和括号。我会喜欢任何想法！
1条答案
按热度按时间t5zmwmid1#
必须定义目标表列名，而不是强制转换值。
首先检查此查询是否有效
如果上述方法有效：
我假设
[DMSArchive].[dbo].[VEHICLE_TRANSACTION]具体如下：[Date],[HOV], [ExpressPass],[CDecal], [Unknown],[Total Vehicles]如果不是，请在insert表名后面的列列表中使用。