-
Bulk insert in SQL Server
Hi,
I want to bulk insert every third row of a file into a table.Is it possible to do so in SQL Server?If so How can I do it?
Thanks.
-
1. For importing multiple files
http://www.databasejournal.com/featu...le.php/3325701
2. For getting only 3rd row, add the following options in the procedure
FIRSTROW=3, LASTROW =3
-
-- Updated Example for your case
Alter procedure usp_ImportMultipleFiles @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n",FIRSTROW=3, LASTROW =3)'
--print @query
exec (@query)
insert into logtable (query) select @query
end
drop table #y
-
Hi MAK,
There is only 1 file and I want to insert not just one row but every 3rd row in that file.
Thanks.
-
here you go.
----Read Count
declare @totalcount int
declare @count int
declare @varcount varchar(10)
declare @query varchar(2000)
select @totalcount = count1 from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\MyImport;','select count(*) as count1 from a.csv')
--print @totalcount
set @count =3
while @count <=@totalcount
begin
set @varcount=convert(varchar(10),@count)
set @query ='BULK INSERT Account FROM "C:\MyImport\a.csv" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n",FIRSTROW='+@varcount +' , LASTROW ='+ @varcount + ')'
print @query
exec (@query)
set @count=@count+3
end
-
-
I am also working on something similar , but having problems with the csv file data fields and table fields. The csv file fields DO NOT match exactly with my table .....is there a workaround ??? How can i ensure that the right values are placed in the right columns ... How can i create a "format file" so that the all the table fields get the right values !!
Regards
Monika
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|