Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2001
    Posts
    5

    Unanswered: duplicating row with slight changes

    Is there any way I can copy a row through SQL without the identity column being a problem? I have the following table

    Code:
    CREATE TABLE [test] (
    	[id] [int] IDENTITY (1, 1) NOT NULL ,
    	[val] [varchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL 
    ) ON [PRIMARY]
    and i would like to do something like

    Code:
    insert into test select * from test where id = 1
    but I get identity column errors. Is there any way to do this without using listing off column names? I need to duplicate the row, except the id field, but the table design tends to change from time to time, and this query will be hardcoded into a program, so I want to stay away from doing
    Code:
    insert into test (val) select val from test where id = 1
    since in the real table I have a several fields

    Thanks

  2. #2
    Join Date
    Mar 2003
    Posts
    2

    Re: duplicating row with slight changes

    Have you tried using a view over the table that excludes the identity field?

    Originally posted by garwain
    Is there any way I can copy a row through SQL without the identity column being a problem? I have the following table

    Code:
    CREATE TABLE [test] (
    	[id] [int] IDENTITY (1, 1) NOT NULL ,
    	[val] [varchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL 
    ) ON [PRIMARY]
    and i would like to do something like

    Code:
    insert into test select * from test where id = 1
    but I get identity column errors. Is there any way to do this without using listing off column names? I need to duplicate the row, except the id field, but the table design tends to change from time to time, and this query will be hardcoded into a program, so I want to stay away from doing
    Code:
    insert into test (val) select val from test where id = 1
    since in the real table I have a several fields

    Thanks

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245

    Re: duplicating row with slight changes

    I can't remember the syntax, but look up "IDENTITY INSERT" in SQL BOL. I think it will allow you to do what you are trying to do...

    Regards,

    Hugh Scott

    Originally posted by garwain
    Is there any way I can copy a row through SQL without the identity column being a problem? I have the following table

    Code:
    CREATE TABLE [test] (
    	[id] [int] IDENTITY (1, 1) NOT NULL ,
    	[val] [varchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL 
    ) ON [PRIMARY]
    and i would like to do something like

    Code:
    insert into test select * from test where id = 1
    but I get identity column errors. Is there any way to do this without using listing off column names? I need to duplicate the row, except the id field, but the table design tends to change from time to time, and this query will be hardcoded into a program, so I want to stay away from doing
    Code:
    insert into test (val) select val from test where id = 1
    since in the real table I have a several fields

    Thanks

  4. #4
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: duplicating row with slight changes

    Originally posted by hmscott
    I can't remember the syntax, but look up "IDENTITY INSERT" in SQL BOL. I think it will allow you to do what you are trying to do...

    Regards,

    Hugh Scott
    with "identity insert" you will insert the identity-column also. i think there is no other way than "select"-ing all columns except the identity or defining a view and excluding the identity column (which is in fact putting all columns in the select statement of the view).

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    My initial thought on this was "Can't be done", glad I waited to hear what others might say...

    Code:
    create table Test(f1 int not null identity, f2 int, f3 char(1))
    create view TestView as select f2,f3 from Test
    insert into TestView values (1,'A')
    insert into TestView values (1,'B')
    insert into TestView values (1,'C')
    insert into TestView values (1,'D')
    insert into TestView values (1,'E')
    
    select * from TestView
    select * from Test
    
    insert into TestView
    select 2,f3 from TestView
    
    select * from TestView
    select * from Test
    As for the Identity_Insert, this allows you to insert your own values for the identity column. You would still need to explicitly state all columns on the insert statment.

    btw, suggesting a view was a cool solution!
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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