continue with error

[UKmedia]

I have the following script:

drop table import_temp

CREATE TABLE import_temp 

([col1] [float] NULL,
[col2] [float] NULL,
[col3] [float] NULL) 

BULK INSERT import_temp FROM 'C:\test\tilldata\2100200850.csv' WITH (FIELDTERMINATOR = ',', FIRSTROW = 2)

declare @f1 float
declare @f2 float
declare @f3 float
declare @f4 float
declare @f5 float

set @f1 = (select col3 from import_temp where col1 = 1)
set @f2 = (select col3 from import_temp where col1 = 2)
set @f3 = (select col3 from import_temp where col1 = 4)
set @f4 = (select col3 from import_temp where col1 = 5)
set @f5 = (select col3 from import_temp where col1 = 27)

INSERT INTO tblgardiff_50

([net_sales]
,[gross_sales]
,[cash_in_draw]
,[cards_in_draw])

VALUES

(@f1
,@f2
,@f3
,@f4
,@f5)

but sometimes the

set @f1 = (select col3 from import_temp where col1 = 1)

might not always contain data so if there is no data how can I tell it to just carry on with the script at the moment I get error:

Msg 110, Level 15, State 1, Line 23
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

UKmedia productions

http://www.uk-media.com

 

[jbenson001]

You will have to check if a value exists first, if not, you will have to assign @f1 a default value.

 

[UKmedia]

k i understand what you mean,

but how can I set a default value of say null or 0 but still see if there is a value in my select query if there is not then just insert the default value.

declare @f5 float
set @f1 = (select col3 from import_temp where col1 = 1)

UKmedia productions

http://www.uk-media.com

 

[jbenson001]

Try:

declare @f1 float
set @f1 = isnull((select col3 from import_temp where col1 = 1), 0.0)
select @f1

 

[UKmedia]

no that still reports that there are more columns than values to insert.

this is my code.

USE rac

drop table import_temp

CREATE TABLE import_temp 

([col1] [float] NULL,
[col2] [float] NULL,
[col3] [float] NULL) 

BULK INSERT import_temp FROM 'C:\test\tilldata\2100200850.csv' WITH (FIELDTERMINATOR = ',', FIRSTROW = 2)

declare @f1 float
declare @f2 float
declare @f3 float
declare @f4 float
declare @f5 float


set @f1 = isnull((select col3 from import_temp where col1 = 1), 0)
set @f2 = isnull((select col3 from import_temp where col1 = 2), 0)
set @f3 = isnull((select col3 from import_temp where col1 = 4), 0)
set @f4 = isnull((select col3 from import_temp where col1 = 5), 0)
set @f5 = isnull((select col3 from import_temp where col1 = 27), 0)

INSERT INTO tblgardiff_50

([net_sales]
,[gross_sales]
,[cash_in_draw]
,[cards_in_draw])

VALUES

(@f1
,@f2
,@f3
,@f4
,@f5)

How can I set @f1, @f2, @f3, @f4, @f5 a value if the select statement returns nothing?

UKmedia productions

http://www.uk-media.com

 

[EKOnerhime]

INSERT INTO tblgardiff_50

([net_sales]
,[gross_sales]
,[cash_in_draw]
,[cards_in_draw])

VALUES

(@f1
,@f2
,@f3
,@f4
,@f5)

You have 4 columns in the INSERT section and 5 variables in the VALUES section.

 

[UKmedia]

oh yeah how silly of me, I think i need a break from this application coding in vb.net and asp.net then SQL is not a good idea your brain goes.

cheers

 

[UKmedia]

how can I insert the current date into my insert query in script?

 

[EKOnerhime]

You can do this using the GETDATE function.

http://msdn.microsoft.com/en-us/library/ms188383(SQL.90).aspx

Example:
INSERT INTO tblgardiff_50

([net_sales]
,[gross_sales]
,[cash_in_draw]
,[cards_in_draw]
,[your-date-column-here])

VALUES

(@f1
,@f2
,@f3
,@f4
,GETDATE())