Identity Error.....grrr - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Identity Error.....grrr

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-12 : 05:32:26

I keep getting the following error when trying to create a table using a SELECT INTO....statment. I have no idea why this is returninig the following error message, as I am using an INTO clause...

Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause

My script:
if exists (SELECT name FROM sysobjects WHERE name = 'INVOICE_TABLE')
begin
drop table INVOICE_TABLE
end
go
SELECT
identity(int,1,1) as DT_Invoice_SRN,
ltrim(rtrim(Creditor_Reference)) as Supplier_Ref,
ltrim(rtrim(Internal_Invoice_Number)) as Invoice_No,
'' as Capture_ID,
ltrim(rtrim(Voucher_Description)) as Voucher_Description,
ltrim(rtrim(Invoice_Date)) as Invoice_Date,
convert(money,Total_Invoice_Amount) as Gross_Amount,
convert(money, VAT_Amount) as VAT_Amount,
money as NET_Amount,
'' as Gross_GBP,
'' as Net_GBP,
'' as VAT_GBP,
'' as Invoice_Currency,
'' as Internal_Ref,
'' as VAT_Desc,
'' as Posting_Date,
'' as Date_Authorised,
ltrim(rtrim(Due_Date)) as Due_Date,
ltrim(rtrim(Date_Paid)) as Payment_Date,
'' as Entry_Date,
'' as Batch_Number,
'' as [Type],
'' as Invoice_Paid,
'' as Division_Code,
'' as Invoice_Line_Ref,
'' ltrim(rtrim(Payment_Method)) as Invoice_Payment_Method,
'' as Payment_Method_Description,
ltrim(rtrim(Payment_Ref_Number)) as Payment_Ref_No
INTO INVOICE_TABLE
FROM LCC_All_Test

Any ideas??? Thanks in advance....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 05:43:33

1) Why do you store DATEs as VARCHAR?
2) Why do you store CAPTURE IDs as VARCHAR?
3) All your empty string, which datatypes and length will they have

SELECT	identity(int,1,1) as DT_Invoice_SRN, 
	ltrim(rtrim(Creditor_Reference)) as Supplier_Ref,
	ltrim(rtrim(Internal_Invoice_Number)) as Invoice_No,
	cast(null as int) as Capture_ID,
	ltrim(rtrim(Voucher_Description)) as Voucher_Description,
	cast(Invoice_Date as datetime) as Invoice_Date,
	convert(money,Total_Invoice_Amount) as Gross_Amount,
	convert(money, VAT_Amount) as VAT_Amount,
	[money] as NET_Amount,
	cast(null as money) as Gross_GBP,
	cast(null as money) as Net_GBP,
	cast(null as money) as VAT_GBP,
	cast(null as varchar(50)) as Invoice_Currency,
	cast(null as varchar(50)) as Internal_Ref,
	cast(null as varchar(50)) as VAT_Desc,
	cast(null as datetime) as Posting_Date,
	cast(null as datetime) as Date_Authorised,
	cast(Due_Date as datetime) as Due_Date,
	cast(Date_Paid as datetime) as Payment_Date,
	cast(null as datetime) as Entry_Date,
	cast(null as varchar(50)) as Batch_Number,
	cast(null as varchar(50)) as [Type],
	cast(null as varchar(50)) as Invoice_Paid,
	cast(null as varchar(50)) as Division_Code,
	cast(null as varchar(50)) as Invoice_Line_Ref,
	ltrim(rtrim(Payment_Method)) as Invoice_Payment_Method,
	cast(null as varchar(50)) as Payment_Method_Description, 
	ltrim(rtrim(Payment_Ref_Number)) as Payment_Ref_No 
INTO	INVOICE_TABLE
FROM	LCC_All_Test

Peter Larsson
Helsingborg, Sweden

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-12 : 05:49:53

Thanks Peter,

Dates are stored as varchar as the data contains invalid dates which I need to flag to the client.

Capture ID's and empty strings will never be updated unless I have the data, so that is why the are blank, as they will be redundant for this case.

Many thanks for the update!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources