CODEWITHSUNDEEP
sqlsql-server
new user
new user

Reputation: 49

Copy data from one table to another table using sql server

I need to write a query to copy data from one table (CUSTOMER_TABLE with 130 columns) to another table (FAIL_CUSTOMER_TABLE with 132 columns) in sql server.

FAIL_CUSTOMER_TABLE has two extra columns but both have default values declared.

I am getting below exception for the below query. Can any one help me in writing the query with out listing all 130 columns in the query.

INSERT into dbo.FAIL_CUSTOMER_TABLE
select *,'N' AS 'FIXED', CURRENT_TIMESTAMP AS 'DATE' 
from dbo.CUSTOMER_TABLE 
where LTRIM(RTRIM(CustNumber)) !='' 
and Processed = 'N' 
order by ROWID asc 

SET IDENTITY_INSERT dbo.XML_FAIL_RECORDS OFF

Error message

explicit value for the identity column in table 'dbo.FAIL_CUSTOMER_TABLE' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Upvotes: 0

Views: 64

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

You missed to add SET IDENTITY_INSERT dbo.XML_FAIL_RECORDS ON before the insert

Also you need to mention column list while setting SET IDENTITY_INSERT ON

SET IDENTITY_INSERT dbo.XML_FAIL_RECORDS ON --Missed in your query

INSERT into dbo.FAIL_CUSTOMER_TABLE(Identity_Col,col1,col2...)
select Identity_Col,col1,col2,....,'N' AS 'FIXED', CURRENT_TIMESTAMP AS 'DATE' 
from dbo.CUSTOMER_TABLE 
where LTRIM(RTRIM(CustNumber)) !='' 
and Processed = 'N' 
order by ROWID asc 

SET IDENTITY_INSERT dbo.XML_FAIL_RECORDS OFF

Upvotes: 2

Related Questions