Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 29th, 2007, 11:04 AM
Registered User
 
Join Date: Aug 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default problems to insert and into an empty table

I've got a problem trying to insert data into an empty table
I have an empty table that I need to fill.
I want to use a created view where I have the information i need to use to fill the first table. So I executed the following

insert into PO_expectedResult_CES (TYPE_CES, SystemStartDate, RECEIVERNO, quoteuser, PO, POREGID, item, VENDOR, RECEIVED, WHSE, InvNum, InvDate, FranchiseId, MainSt, Frequency, InvoiceStartDate, InvoiceEndDate, WarehouseOpenDate, WarehouseCloseDate, FranchiseBillPurchaseOrderFlag)

select TYPE_CES, SystemStartDate, RECEIVERNO, quoteuser, PO, POREGID, item, VENDOR, RECEIVED, WHSE, InvNum, InvDate, FranchiseId, MainSt, Frequency, InvoiceStartDate, InvoiceEndDate, WarehouseOpenDate, WarehouseCloseDate, FranchiseBillPurchaseOrderFlag
from PO_TO_PROCESS_CES_1_11_cicle1

BUT THE SYSTEM SHOWS ME THE FOLLOWING MESSAGE
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'PO_expectedResult_CES' when IDENTITY_INSERT is set to OFF.


CAN ANYBODY HELP ME PLEASE?



 
Old August 29th, 2007, 11:21 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

prior to your insert you need to do :

SET IDENTITY_INSERT PO_expectedResult_CES ON

insert into PO_expectedResult_CES (TYPE_CES, SystemStartDate, RECEIVERNO, quoteuser, PO, POREGID, item, VENDOR, RECEIVED, WHSE, InvNum, InvDate, FranchiseId, MainSt, Frequency, InvoiceStartDate, InvoiceEndDate, WarehouseOpenDate, WarehouseCloseDate, FranchiseBillPurchaseOrderFlag)

select TYPE_CES, SystemStartDate, RECEIVERNO, quoteuser, PO, POREGID, item, VENDOR, RECEIVED, WHSE, InvNum, InvDate, FranchiseId, MainSt, Frequency, InvoiceStartDate, InvoiceEndDate, WarehouseOpenDate, WarehouseCloseDate, FranchiseBillPurchaseOrderFlag
from PO_TO_PROCESS_CES_1_11_cicle1

SET IDENTITY_INSERT PO_expectedResult_CES OFF

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========
 
Old September 28th, 2007, 01:36 AM
Authorized User
 
Join Date: Feb 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Another option which you can try is this...

"Exclude the identity column of the table 'PO_expectedResult_CES' while preparing the insert statement"

Caution: In this case identity column will have system generated value.


Cheers,
Pooja Falor





Similar Threads
Thread Thread Starter Forum Replies Last Post
Displaying DetailsView for New/Empty Data Table Sheri B ASP.NET 2.0 Basics 5 December 7th, 2006 03:08 PM
The last empty record in table should not show alie VB Databases Basics 4 March 9th, 2006 07:38 AM
Dynamic Table calls 2 empty rows nancy Dreamweaver (all versions) 1 January 18th, 2006 06:00 PM
Empty table for dropdownlist. macupryk BOOK: ASP.NET Website Programming Problem-Design-Solution 2 August 16th, 2004 06:33 AM
How to insert default value if text box is empty akc Javascript How-To 2 November 7th, 2003 11:55 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.