Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Unanswered: converting mysql 2 sql 2000

    how do i go about converting the Mysql Script below to Sql Server Script ????

    CREATE TABLE cat (
    id int NOT NULL auto_increment,
    name char(20) NOT NULL default '',
    PRIMARY KEY (id)
    );

    INSERT INTO cat VALUES (1,'Not Categorised');

    ALTER TABLE cat AUTO_INCREMENT = 5;

    i want a script that:
    creates the table
    inserts a row of data and then
    sets the increment field to start at 5

    i tried using identity(1,1) and
    SET IDENTITY_INSERT cat ON;
    INSERT INTO cat VALUES (1,'Not Categorised');

    but when i enter the data in i get the error below

    An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    please can anyone help or advice

    thanks

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: converting mysql 2 sql 2000

    Originally posted by m.inckle
    how do i go about converting the Mysql Script below to Sql Server Script ????

    CREATE TABLE cat (
    id int NOT NULL auto_increment,
    name char(20) NOT NULL default '',
    PRIMARY KEY (id)
    );

    INSERT INTO cat VALUES (1,'Not Categorised');

    ALTER TABLE cat AUTO_INCREMENT = 5;

    i want a script that:
    creates the table
    inserts a row of data and then
    sets the increment field to start at 5

    i tried using identity(1,1) and
    SET IDENTITY_INSERT cat ON;
    INSERT INTO cat VALUES (1,'Not Categorised');

    but when i enter the data in i get the error below

    An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    please can anyone help or advice

    thanks
    create table as:
    CREATE TABLE cat (
    id int NOT NULL ,
    name char(20) NOT NULL default '',
    PRIMARY KEY (id)
    );
    then insert the record:
    INSERT INTO cat VALUES (1,'Not Categorised');


    then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.

  3. #3
    Join Date
    Feb 2004
    Posts
    24

    Re: converting mysql 2 sql 2000

    Originally posted by harshal_in
    create table as:
    CREATE TABLE cat (
    id int NOT NULL ,
    name char(20) NOT NULL default '',
    PRIMARY KEY (id)
    );
    then insert the record:
    INSERT INTO cat VALUES (1,'Not Categorised');


    then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.
    is there any way to do this in a script ???????

  4. #4
    Join Date
    Feb 2004
    Posts
    24

    Re: converting mysql 2 sql 2000

    Originally posted by harshal_in
    create table as:
    CREATE TABLE cat (
    id int NOT NULL ,
    name char(20) NOT NULL default '',
    PRIMARY KEY (id)
    );
    then insert the record:
    INSERT INTO cat VALUES (1,'Not Categorised');


    then go to the enterprise manager->design table and set the identity property of the table to yes with value 5.
    is there a way of altering an identity column in a table like below

    ALTER TABLE cat ALTER COLUMN id INT NOT NULL identity(5,1);

    i always get
    Incorrect syntax near the keyword 'identity'.

  5. #5
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: converting mysql 2 sql 2000

    Originally posted by m.inckle
    is there a way of altering an identity column in a table like below

    ALTER TABLE cat ALTER COLUMN id INT NOT NULL identity(5,1);

    i always get
    Incorrect syntax near the keyword 'identity'.
    no. as far as my knowledge goes you can't add identity to an existing column thru alter table, u have to add a new column for it.
    so the easiest way is to use enterprise manager.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •