Select Into without copying the PK

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Into without copying the PK

Author

Topic

gregsenne
Starting Member

7 Posts

Posted - 2009-02-13 : 11:57:58

Hi, I want to use a SELECT INTO statement to copy one row from a table into another table. The tables are identical besides the primary key. In one table, the PK is MerchantID, and in the other the PK is MerchSetupID. I want to copy all rows in the MerchSetup table into Merchants table, except for the PK, which I want auto-generated in the Merchants table. Make sense? I don't know how to write the query. I probably want something similar to below, but how do I not copy the MerchSetupID from that table into the Merchants table and generate an auto PK in the Merchants table?

SELECT * INTO Merchants FROM MerchSetup WHERE MerchSetupID=12

Thanks for the help.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-13 : 12:37:22

do a select SELECT with all the fields except the PK of MerchSetup .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 12:48:42

[code]SELECT IDENTITY(int,1,1) AS MerchantID,* INTO Merchants FROM MerchSetup WHERE MerchSetupID=12[/code]

gregsenne
Starting Member

7 Posts

Posted - 2009-02-13 : 13:45:06

When I run the above query, I get a message saying
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Merchants' in the database.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 13:48:44

quote:
Originally posted by gregsenne

When I run the above query, I get a message saying
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Merchants' in the database.

thats because you've already created the table.so either drop it and retry using


DROP TABLE Merchants

SELECT IDENTITY(int,1,1) AS MerchantID,*  INTO Merchants FROM MerchSetup WHERE MerchSetupID=12

or just add a new identity column using 

ALTER TABLE Merchants ADD ColumnName int IDENTITY(1,1)

and it will automatically get assigned autogenerated numbers for existing data

gregsenne
Starting Member

7 Posts

Posted - 2009-02-13 : 14:11:57

Ok, the way posted below works fine, however If I want to take out all the field names (so the query is not so big), is there a way to say
* except for MerchantID (PK) ?

INSERT INTO Merchants (Field Names) SELECT (Field Names) FROM MerchSetup WHERE MerchSetupID = 20001

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-02-13 : 14:20:59

quote:
Originally posted by gregsenne
..If I want to take out all the field names (so the query is not so big), is there a way to say
* except for MerchantID (PK) ?...

Just supply the list of columns you need.

CODO ERGO SUM

gregsenne
Starting Member

7 Posts

Posted - 2009-02-13 : 14:23:16

Yeah I know, but there's about 50 columns in that table. I was just trying to avoid that long of a query. If there's no way of doing it, i will supply the column names.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 01:15:48

quote:
Originally posted by gregsenne

Yeah I know, but there's about 50 columns in that table. I was just trying to avoid that long of a query. If there's no way of doing it, i will supply the column names.

No other way than listing columns one by one. you could simply do this


DECLARE @ColumnList varchar(max)
SELECT @ColumnList=COALESCE(@ColumnList+',','')+COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Merchants'
SELECT @ColumnList

and then copy and paste result into INSERT column list

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-14 : 07:36:06

If your issue is about having to type all the column names, instead use SSMS to generate a select query (right click on the table in the object explorer, then Script Table as -> Select to -> New Query Window) and then edit the query it generates to suit your needs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources