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
