0

I want to copy 1 row from Table A to Table A2.

Here is my code:

Insert into   A2
select * from A1
where ID=290

Id is a Primary Key and both tables have same structure.

Obviously the above code failed because it cannot insert the Primary Key(And I don't want it to, I want a new PK be generated).

I know I can use explicit insert but this is a very wide table with hundreds of columns.

Is there any way to insert every column expect ID which is the Primary Key?

NO: this is NOT the solution. Please spend 1 minute time and read question Carefully before closing them!

I said I know this is possible with explicit insert . I want to know if can done with shorter SQL since this is a VERY wide table.

If you have the solution please mention the reference here.

S Nash
  • 2,363
  • 3
  • 34
  • 64
  • Maybe [this](http://stackoverflow.com/questions/149784/how-do-you-copy-a-record-in-a-sql-table-but-swap-out-the-unique-id-of-the-new-ro?rq=1). – Nick Aug 06 '15 at 17:02
  • 4
    Yes, and you mention it in the question itself. List out every other column. – Gordon Linoff Aug 06 '15 at 17:02
  • http://csharpocean.blogspot.ro/2014/08/how-to-select-all-columns-except-only.html – Mihai Aug 06 '15 at 17:04
  • As I mentioned this is a very wide table . I want to know if there are other ways. – S Nash Aug 06 '15 at 17:05
  • No, there is no other way. You can, however, right click the table in most query analyzers (such as SSMS) and tell it to script an INSERT statement for you. – Bacon Bits Aug 06 '15 at 17:07
  • Hmm I guessed that . Please provide this as an answer. I do more research and if that's the fact I accept your answer. It's good to have this documented in SO . – S Nash Aug 06 '15 at 17:12
  • Using SSMS make it a bit easier. I was hoping to get a small sql. – S Nash Aug 06 '15 at 17:19
  • 1
    Why not put A1 into a temp table, drop the column that holds the PK, and then insert from the temp table. – Zack Aug 06 '15 at 17:20

3 Answers3

2

There is no select * except some columns. Your sql must contain the full list of columns except the id for both tables.

However, this does not mean you have to manually type them. You can generate the columns list by selecting them from sys.columns or from information_schema.columns.
If you are using SSMS, you can expand the columns list of one of the tables and simply drag the columns to your query window. If you want to drag them all, simply drag the columns "folder" icon to your query window.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
1

Well, this might be a little bit overkill but it'll do what you want. This way no matter how big the tables get (columns added/removed) you'll get an exact copy.

EDIT: In response to the comment below, and in the interest of full disclosure, the PK MUST be an identity and the columns MUST be in the same order.

SELECT * INTO #TMP FROM A1 WHERE ID = <THE ID YOU WANT>

ALTER TABLE #TMP DROP COLUMN ID

INSERT INTO A2
SELECT * FROM #TMP

DROP TABLE #TMP
Josh
  • 1,724
  • 13
  • 15
  • 1
    This is quite a bit simpler and more readable than my solution, but I think it has 2 limitations that anyone using it should understand. First, I believe this will only work if ID is an identity column, if you just have a default on the ID column, then I believe this will fail. Second, A1/A2 must have the columns in the exact same order. – jmc Aug 06 '15 at 17:36
1

You really should just specify column list.

If you are concerned about accidentally missing a column, you could use SQL to generate the column list for you:

declare @columnList NVARCHAR(MAX)
select @columnList = ISNULL(@columnList + N', [', N'[') + [COLUMN_NAME] + N']' from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = 'a1' and [COLUMN_NAME] not in ('Id')
print @columnList

If you really need a statement to do what you asked, you can extend the above to dynamically generate and execute SQL. Unfortunately, this is much harder to read, but it does have the benefit of automatically adapting as you add columns.

declare @dynamicSql NVARCHAR(MAX)
declare @columnList NVARCHAR(MAX)
select @columnList = ISNULL(@columnList + N', [', N'[') + [COLUMN_NAME] + N']' from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = 'a1' and [COLUMN_NAME] not in ('Id')
set @dynamicSql = N'INSERT INTO [a1](' + @columnList + N') SELECT ' + @columnList + N' FROM [a2] WHERE [Id] = 290'
exec(@dynamicSql)
jmc
  • 383
  • 1
  • 6