Select *, except...

• dlongnecker-802303

  Ten Centuries

  Points: 1280

  More actions

  August 4, 2008 at 12:30 pm

  #196494

  Hi everyone,

  I'm writing a SPROC to clone rows in tables, but with different primary keys. For example,

  create table #temp(

  myId int primary key,

  value1 varchar(50),

  value2 varchar(50)

  )

  insert into #temp

  select 1, 'ThisIsA', 'Test' UNION

  select 2, 'Garbage', 'Data'

  Now when I go to select the record I want to use to clone, I was wondering if there is a way to select all the data from the table except the primary key, akin to

  INSERT into #temp

  SELECT @NewID, * (but not myID) From #TEMP Where myId = @OldID

  My real table has 40 some fields and I don't want to write a query that's 7 lines long.

  Maybe i'm just lazy :doze:

  ---
  Dlongnecker

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 4, 2008 at 12:35 pm

  #852858

  I'd go with the lazy, as there is no way to do that.

  I don't know if this works in SQL 2000 QA, but in SSMS you can drag the columns from the Obkect Browser into the Query window.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 4, 2008 at 12:50 pm

  #852861

  Yes, in QA you can drag the columns to the query window. Drag and drop the columns folder from the object explorer in QA to the query window.

  😎

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738632

  More actions

  August 4, 2008 at 1:53 pm

  #852897

  Use QA to generate the statement, but you'll still need to long query.