如何从表A到表B插入一行但不是Primay键

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

我想将表A中的1行复制到表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.

Id是主键，两个表具有相同的结构。

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).

显然上面的代码失败了，因为它无法插入主键（我不想要它，我希望生成一个新的PK）。

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?

有没有办法插入每一列期望ID作为主键？

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

不：这不是解决方案。请花1分钟时间仔细阅读问题，然后再关闭它们！

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.

我说我知道这可以通过显式插入。我想知道是否可以使用更短的SQL，因为这是一个非常宽的表。

If you have the solution please mention the reference here.

如果您有解决方案，请在此处提及参考。

3 个解决方案

#1

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

除了一些列之外没有select *。您的sql必须包含除两个表的id之外的完整列列表。

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.

但是，这并不意味着您必须手动键入它们。您可以通过从sys.columns或information_schema.columns中选择列列表来生成列列表。如果您使用的是SSMS，则可以展开其中一个表的列列表，只需将列拖到查询窗口即可。如果要将它们全部拖动，只需将列“文件夹”图标拖到查询窗口即可。

#2

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:

如果您担心意外丢失列，可以使用SQL为您生成列列表：

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.

如果您确实需要一个语句来执行您所要求的操作，则可以扩展上述内容以动态生成和执行SQL。不幸的是，这很难阅读，但它确实具有在添加列时自动适应的好处。

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)

#3

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.

编辑：在回应下面的评论，并为了充分披露，PK必须是一个身份，列必须是相同的顺序。

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

#1