Skip navigation

so, you’re having problems finding equivalent statements for your mysql sql queries in ms-sql’s t-sql?

well, search no further than microsoft’s very own Transact-SQL Reference (with the root document aptly named “ms189826.aspx” – yeah, real easy to find there, microsoft.)

Anyway, here are some common mysql operations and their t-sql equivalents:

MySQL multiple row insert statement

insert into users
  ( firstname, lastname )
VALUES
  ('bobo', 'bobo'),
  ('john', 'hopkins') ;

T-SQL Multiple row insert statement

-- usual t-sql way is to
-- just write multiple insert
-- statements
insert into users( firstname, lastname )
  VALUES ('bobo', 'bobo') ;

insert into users( firstname, lastname )
  VALUES ('john', 'hopkins') ;

OR (as inspired by pinaldave you can use this weird UNION ALL syntax:

insert into users( firstname, lastname )

SELECT 'bobo', 'bobo'

UNION ALL

SELECT 'john', 'hopkins' ;

Kind of like copying from another table, only not.

Setting the value of an auto_incremented (‘identity’) field in T-SQL

In MySQL, this is easy. If you have a MySQL table with a definition like:

CREATE TABLE users (
  id int primary key not null auto_increment,
  name varchar( 255 ) not null
);

You can always easily specify values for the id column (EVEN THOUGH it is auto_incremented) by just writing MySQL SQL statements like:

insert into users( id, name )
VALUES (1, 'bobo') ;

Now, that doesn’t work in T-SQL. Of course, Microsoft’s version requires you to set some setting, turn some knob, tweak some dial to get the behavior you want.

First of all, the equivalent T-SQL table is:

CREATE TABLE users (
  id int primary key not null identity, -- identity is like mysql's auto_increment
  name varchar( 255 ) not null
);

So if you tried to write a T-SQL statement like:

insert into users( id, name )
VALUES( 1, 'bobo' );

You’d see an error like:


Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘users’ when IDENTITY_INSERT is set to OFF.

Uh, k, so what do we do?

We set IDENTITY_INSERT to ON! Right.

SET IDENTITY_INSERT users ON; -- make it so
-- we can specify actual values for columns
-- in T-SQL table

insert into users( id, name )
VALUES( 1, 'bobo' );

SET IDENTITY_INSERT users OFF; -- turn it off again
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: