converting mysql insert statement to sql server insert statement?

Question

I'm getting error while converting mysql insert statement into sql server 2008. because limit and alias is not working can anybody tell me what I'm doing wrong in following query.

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';

Error

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'.

Edited:

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)
 );

Lamak · Accepted Answer · 2012-02-29 13:54:22Z

3

Change:

 (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)

With this:

(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')

Though there seems to be no ORDER BY clause, and you also do two CROSS JOIN, are you sure that this returns your expected output?

answered Feb 29, 2012 at 13:54

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vishwanath Dalvi Over a year ago

I ran what you have suggested not i'm getting Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated. error.

Lamak Over a year ago

@ViswanathanIyer - That's a completely different problem, now its running the query, but fails to insert data because you have different data types between your destination table and the columns on your query. You should let us know your tables definition

Vishwanath Dalvi Over a year ago

Thanks I was inserting status of 6 chars and in create table I've defined as char((5), I modified that now its working fine.

Collectives™ on Stack Overflow

converting mysql insert statement to sql server insert statement?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related