[newbieHQ]

good evening everyone ...
currently i am trying to create a temporary table and then bulk insert the data from text file to the temporary table. and of course after that i would like select certain data from the temporary table to the actual table in SQL server. i am trying to using store procedure to do this. and the problem is i dont find any data in the actual table.the table is empty !

USE [CustOrderManage]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[insertdata]



AS
BEGIN

	SET NOCOUNT ON;

create table #temptbl
(
productbatch varchar(50),
batchno varchar(50),
date varchar(50),
time datetime,
mixingtime varchar (50),
FB10 numeric(8,2),
FB11 numeric(8,2), 
FB12 numeric(8,2),
FB13 numeric(8,2),
FB14 numeric(8,2),
FB15 numeric(8,2),
FB16 numeric(8,2),
FB17 numeric(8,2),
FB18 numeric(8,2),
FB19 numeric(8,2),
N1 numeric(8,2),
F817 numeric(8,2),
F818 numeric(8,2),
F819 numeric(8,2),
FB23 numeric(8,2)
)

BULK INSERT	#temptbl
from 'C:\test4.txt'
with
(
fieldterminator = '\t',
rowterminator = '\t\n'
)

insert into hehe (productbatch,date,batchno,time)
select productbatch,date,batchno,time from #temptbl


END

the above store procedure execute successfully but i dont see any data exist when i select * from hehe ..

the table is totally empty..can anyone tell me how can i solve this problem?

thank you so much ...

[newbieHQ]

hi everyone ..i have been chaging my code to dynamically sql ..below is the code.. i am trying to compare the existing data with temporary table data in order to prevent the duplicated data happen by using SQL - except ... after that i will insert the data produced after comparison to particular table which is haha .. the whole store procedure successfully run but why i dont get any result from table haha...and the table is still empty ..what am i mising here actually ? thank you everyone ..

create table #temptbl
(
productbatch varchar(50),
batchno varchar(50),
date varchar(50),
time varchar(50),
mixingtime varchar (50),
FB10 numeric(8,2),
FB11 numeric(8,2), 
FB12 numeric(8,2),
FB13 numeric(8,2),
FB14 numeric(8,2),
FB15 numeric(8,2),
FB16 numeric(8,2),
FB17 numeric(8,2),
FB18 numeric(8,2),
FB19 numeric(8,2),
N1 numeric(8,2),
F817 numeric(8,2),
F818 numeric(8,2),
F819 numeric(8,2),
FB23 numeric(8,2)
);

declare @path varchar (2000);
declare @sql varchar (2000);
declare @sql2 varchar (2000);
select @path = 'c:\test5.txt';
set @sql = 'bulk insert #temptbl from ''' + @path +''' with ( fieldterminator = ''\t'',rowterminator = ''\t\n'') ' ;


print @sql;
exec (@sql);

set @sql2 = 'insert into haha (productbatch,date,batchno,time)
select * from (
SELECT dailyfbsr.productbatch, DailyFBSR.date, DailyFBSR.batchno, DailyFBSR.time
FROM dailyfbsr
except 
SELECT #temptbl.productbatch, #temptbl.date, #temptbl.batchno, #temptbl.time
FROM #temptbl
)
as #nghangkuan';
exec (@sql2);

drop table #temptbl;

1 thing i am curious and feeling weird is if i manually select the whole sql syntax above...the sql program manage to insert the data to haha table but why when i execute the whole procedure ... it return nothing on the table - haha .. and the table is empty ...

[baavgai]

You don't need the dynamic sql. If it doesn't work the first way, loading the sql into a string where the syntax can't even be validated probably isn't going to help.

You want to test things. You do NOT want SET NOCOUNT ON for testing. Without a stored procedure, just run this:

create table #temptbl (
productbatch varchar(50),
batchno varchar(50),
date varchar(50),
time datetime,
mixingtime varchar (50),
FB10 numeric(8,2),FB11 numeric(8,2),FB12 numeric(8,2),FB13 numeric(8,2),FB14 numeric(8,2),
FB15 numeric(8,2),FB16 numeric(8,2),FB17 numeric(8,2),FB18 numeric(8,2),FB19 numeric(8,2),
N1 numeric(8,2), F817 numeric(8,2), F818 numeric(8,2), F819 numeric(8,2), FB23 numeric(8,2)
)

BULK INSERT #temptbl
	from 'C:\test4.txt'
	with (
		fieldterminator = '\t',
		rowterminator = '\t\n'
	)

select count(*) from #temptbl

select top 10 * from #temptbl

Did anything get loaded? If not, then the insert into really doesn't matter. Also, does 'C:\test4.txt' exist on the SQL Server? If it's on a different machine, it won't work.

[newbieHQ]

yup ..it works all the time ... i have been testing on it before this...and also after added the insert statement there but i cant fit all the sql queries into the vb script there... store procedure will be the best way for me to process the data right ?
the test4.txt is exist and in 'C:\test4.txt'...
once the store procedure successfully run completed ... there are no data inside the table (hehe) after insert sql processed... wat am i missing here actually ?

This post has been edited by newbieHQ: 22 April 2012 - 03:05 AM

[newbieHQ]

everything are working fine now even without using store procedure and just run the queries in string format...if i am not using store procedure...i cant fit in all the queries into the vb.net script there so i have to call store procedure to processes the data for me ... eventually the store procedure execute successfully but the table- haha does not shows any data inside it..and i have to manually 'highlight' the queries then everything only can work and data only inserted inside the data............ else nothing gonna happen on the table and remain empty .... haiz.....wat am i missing here actually ? @@

This post has been edited by newbieHQ: 22 April 2012 - 03:44 AM

[baavgai]

There is nothing wrong with your initial select insert that I can see. Though the second one looks a little wonky.

The real question is, if you're running this from VB.NET, why aren't you loading the data from your code, rather than using the server side BULK INSERT command?

Note that if you are running this from your VB.NET code, that a temporary table will only be available for the length of the transaction, to the loaded data may be gone by the type you're running the insert.

I would post the code you're attempting to use in VB.NET to the VB.NET forum. This is where you are having issues.