CODEWITHSUNDEEP
sqlsql-servert-sql
S Nash
S Nash

Reputation: 2499

How to insert one row from Table A to Table B But not Primay key

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.

Upvotes: 0

Views: 137

Answers (3)

Josh
Josh

Reputation: 1744

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

Upvotes: 1

jmc
jmc

Reputation: 393

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)

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 2

Related Questions