mysql - 将mysql插入语句转换为sql server插入语句？

Question

我在将 mysql insert 语句转换为 sql server 2008 时遇到错误。因为限制和别名不起作用，任何人都可以告诉我在以下查询中我做错了什么。

insert into account (product_cd, cust_id, open_date,
  last_activity_date, status, open_branch_id,
  open_emp_id, avail_balance, pending_balance)
select  a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
  e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join 
 (select b.branch_id, e.emp_id 
  from branch b inner join employee e on e.assigned_branch_id = b.branch_id
  where b.city = 'Woburn' limit 1) e
  cross join
 (select 'CHK' prod_cd, '2000-01-15' open_date, '2005-01-04' last_date,
    1057.75 avail, 1057.75 pend union all
  select 'SAV' prod_cd, '2000-01-15' open_date, '2004-12-19' last_date,
    500.00 avail, 500.00 pend union all
  select 'CD' prod_cd, '2004-06-30' open_date, '2004-06-30' last_date,
    3000.00 avail, 3000.00 pend) a
where c.fed_id = '111-11-1111';

错误

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'limit'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near 'limit'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near 'limit'.
Msg 102, Level 15, State 1, Line 46
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 56
Incorrect syntax near 'limit'.
Msg 102, Level 15, State 1, Line 59
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 69
Incorrect syntax near 'limit'.
Msg 102, Level 15, State 1, Line 74
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 84
Incorrect syntax near 'limit'.
Msg 102, Level 15, State 1, Line 87
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 97
Incorrect syntax near 'limit'.
Msg 102, Level 15, State 1, Line 102
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 112
Incorrect syntax near 'limit'.
Msg 102, Level 15, State 1, Line 119
Incorrect syntax near 'a'.

编辑:

create table account
 (account_id integer  not null identity,
  product_cd varchar(10) not null,
  cust_id integer  not null,
  open_date date not null,
  close_date date,
  last_activity_date date,
  status char(5) not null check(status in ('ACTIVE','CLOSED','FROZEN')),
  open_branch_id smallint ,
  open_emp_id smallint ,
  avail_balance decimal(10,2),
  pending_balance decimal(10,2),
  constraint fk_product_cd foreign key (product_cd)
    references product (product_cd),
  constraint fk_a_cust_id foreign key (cust_id)
    references customer (cust_id),
  constraint fk_a_branch_id foreign key (open_branch_id)
    references branch (branch_id),
  constraint fk_a_emp_id foreign key (open_emp_id)
    references employee (emp_id),
  constraint pk_account primary key (account_id)
 );

Answer 1

改变:

 (select b.branch_id, e.emp_id 
  from branch b inner join employee e on e.assigned_branch_id = b.branch_id
  where b.city = 'Woburn' limit 1) 

这样:

(select TOP 1 b.branch_id, e.emp_id 
  from branch b inner join employee e on e.assigned_branch_id = b.branch_id
  where b.city = 'Woburn')

虽然似乎没有 ORDER BY 子句，并且您还执行了两个 CROSS JOIN，但您确定这会返回您预期的输出吗？