BULK Read from flat file and delete

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

General SQL Server Forums

New to SQL Server Programming

BULK Read from flat file and delete

Author

Topic

sqldoubt
Starting Member

17 Posts

Posted - 2010-07-09 : 12:38:05

I am trying to read data from flat file and delete/ update records in different tables.

I am getting these errors:

An expression of non-boolean type specified in a context where a condition is expected, near 'ID'.
Incorrect syntax near '='.
Need help...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

GO
CREATE PROCEDURE [dbo].[SP_DeleteData]
AS

BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
DECLARE @Count int
DECLARE @currentRecords int
DECLARE @Action nvarchar
DECLARE @DBName nvarchar
DECLARE @TableName nvarchar
DECLARE @BatchID nvarchar
DECLARE @StepID nvarchar
declare @controlvalue nvarchar(100)
DECLARE @executeSQL nvarchar

--Create Temporary Tables to read and hold the data from Text files
CREATE TABLE #FlatFileData(
[ActionName] int NOT NULL,
[DbName] varchar,
[TableName] varchar,
[BacthId] int,
[StepId]int
)
--Read the Data from Test.txt into temp table #FlatFileData
SET @sql='BULK INSERT #FlatFileData
FROM ''C:\Test.txt''
WITH (datafiletype = ''widechar'' --To specify the file is unicode use ''char'' for ansi files
,firstrow=2--to skip the first row if the file has column names as first row
,fieldterminator = ''\t''---to speficy it tab delimited
,rowterminator = ''\n'')'

EXEC (@sql)
SELECT @Count = count(1) FROM #FlatFileData --> Boolean error at this line WHILE(@Count > 0)
BEGIN
SELECT @Action = ActionName , @DBName = DbName, @TableName=TableName, @BatchID = batchId, @StepID = StepId from #FlatFileData
if @Action = 'Delete'
BEGIN --> syntax '=' error at this line..
SET @executeSQL= 'delete from ' + @TableName
exec (@executeSQL)
END
IF @action='UpdateStepRunCounter'
Begin

select @controlvalue = (select @controlvalue = attributevalue from importstepattribute where stepid=@Stepid and Batchid=@batchid
and attrbiutename='controlvalue')

update ImportStepRunCounter set CounterValue = @ControlValue where CounterName = 'ControlValue'
AND BatchId = @BatchID AND StepId = @StepID
AND RunId = (Select MAX(runId) from ImportStepRunCounter where Step ID = @StepId AND BatchID = @BatchId)
End
IF @action='UpdateMaxControlValue'
Begin

select @controlvalue = (select @controlvalue=attributevalue from importstepattribute where stepid=@Stepid and Batchid=@batchid
and attrbiutename='controlvalue')

update ImportStepRunCounter set CounterValue = @ControlValue where CounterName = 'ControlValue'
AND BatchId = @BatchID AND StepId = @StepID
AND RunId = (Select MAX(runId) from ImportStepRunCounter where Step ID = @StepId AND BatchID = @BatchId)
End

END

--Drop temporary tables
DROP TABLE #FlatFileData

Commit Transaction

END TRY
BEGIN CATCH

IF @@TRANCOUNT >0
Begin
Rollback Tran

End

IF object_id('tempdb..#FlatFileData') IS NOT NULL
DROP TABLE #FlatFileData

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)With LOG

END CATCH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-09 : 13:00:25

You can't nest @controlvalue: select @controlvalue = (select @controlvalue=attributevalue from importstepattribute where stepid=@Stepid and Batchid=@batchid
and attrbiutename='controlvalue')

Is the WHILE(@Count > 0) supposed to be inside your comment?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources