Batch Imports, Importing the file name

Question

Batch Imports, Importing the file name

Debbie Edwards

SSCrazy Eights

Points: 9217
More actions
September 12, 2006 at 7:25 am

#169680

Hello,
I have just created a stored procedure for batch importing and then used a Format file. I managed to get this working and I added 2 extra columns to the destination table in SQL Server. ImportDate and FileName.
I went into The Table design view and added (GETDATE()) to the ImportDate default value. When I run the stored procedure the Importdate is populated.
Can anyone help me think of a way to grab the actual filename as the files are importing so I know what each row belongs to?
Thanks in advance
Debbie

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Debbie,

Could you post a part of the procedure that gets a file. You should have specified the file name for the source file. Are you providing the file name as a parameter for the stored procedure? In this case you can run an update after the insert from the same or different procedure. One procedure will bulk-insert iusing the format file and the next one will modify FileName for rows just inserted based for example on your ImportDate.

Regards,Yelena Varsha

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 2

Okay. Here is the SP that gets the files....

CREATE procedure usp_ImportMultipleFiles @filepath varchar(500), 'Changes'

@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 '+rtrim(@filepath)+rtrim(@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 = 2, FORMATFILE = ''\\ws-74015\changes\changes.fmt'' )'

--print @query

exec (@query)

insert into logtable (query) select @query

end

drop table #y

GO

And here is where I execute this stored procedure:

Exec usp_ImportMultipleFiles'\\ws-74015\changes\20060825\', '*.csv',

GO

I can do the dateImported with the (Getdate()) as the default value but I have no idea how to do the filename.

Thanks.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Debbie,

such a good job on the SP!

Now I understand that you need to insert the dynamic file name for the rows that were imported with each pass of the While loop. You do have this file name and its path as @Filepath+@Filename on each pass of the loop. I would suggest to do exactly what you do at the bedinning of your code when getting the file names: you imported into the temp table #x an then added the identity by selecting into #y with added identity. You can do the same here: on each pass

Bulk Insert into #TempTable (exec @Query but with #TempTable instead of @Tablename)

and then

Select *,@FileName Into @Tablename from #TempTable

Then empty the temp table for the next pass by truncating.

Let us know if you will have performance isssues with this approach and we will think of something else.

Regards,Yelena Varsha

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 4

Thanks for that.

I think Ill have to get some extra help in as to how to use what you have just told me in the stored procedure. Its all a little beyond me to be honest. Ill see if I can find someone who has more experience in creating stored procedures to see what they make of it. Ive just tried adding your stuff into it but to be honest I wouldnt know where to start.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Debbie,

Sure! somebody who is looking on your stuff locally will be in better position to modify your code, we here can only give a general advice.

one correction: I automatically copied "Select Into" construct, and it is not totally correct. Select... Into selects into the new table which it creates. In your case you will be adding rows to existing table specified by the parameter @Tablename so in your case it will be

Insert @Tablename (and maybe here should follow the field list, it depends)

Select *,@FileName from #TempTable

you do need a person who is good with Insert statement to make sure all fields are inserted in the correct sequence. In any case, you should try it on the test server.

Regards,Yelena Varsha

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 6

Hi again, I have asked around but I cant find anyone who understands where your bits of code go into the SP I created below......

CREATE procedure usp_ImportMultipleFiles @filepath varchar(500), 'Changes'

@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 '+rtrim(@filepath)+rtrim(@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 = 2, FORMATFILE = ''\\ws-74015\changes\changes.fmt'' )'

--print @query

exec (@query)

insert into logtable (query) select @query

end

drop table #y

Is there anyway you could show how the SQL you gave me actually fits into the above?

Debbie

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

Debbie,

I would say it is something like that. I did not run this code, this is just a suggestion. please check for syntax errors. Also '''' means four single quotes and used in the string so it would be one single quote in the composed @Query2 which you can check by uncommenting Print @Query2 statement

Lines from your code:

----------------------

set @filename = (select name from #y where [id] = @count1)

set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'',FIRSTROW = 2, FORMATFILE = ''\\ws-74015\changes\changes.fmt'' )'

insert into logtable (query) select @query

end

-----------------

Suggested code around these lines:

-------------------------------------

DECLARE @Query2

Create Table #MyTempTable (you have to specify table fields here, same as fields in @Tablename tables)

set @Query ='BULK INSERT '+ #MyTempTable + ' FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'',FIRSTROW = 2, FORMATFILE = ''\\ws-74015\changes\changes.fmt'' )'

--print @query

exec (@query)

-- your Bulk Insert inserted into the temp table instead of the permanent table. Now we have to move the result into your @tablename adding @Filepath+@Filename as the last field:

set @query2 ='Insert ' + @tablename +' Select *,' + ''''+ @Filepath+@Filename + '''' + 'from #MyTempTable'

--print @Query2

exec (@query2)

Regards,Yelena Varsha

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 8

Brilliant,

Thanks for that. Ill try that out a little later and let you know how I go!

debbie

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

An easy way to create the temp table would be:

set @Query = 'IF OBJECT_ID(''tempdb..#MyTempTable'') IS NOT NULL DROP TABLE #MyTempTable

SELECT * INTO #MyTempTable FROM ' + @Tablename + ' WHERE 1=0

ALTER TABLE #MyTempTable DROP COLUMN ImportFileName'

exec (@Query)

This assumes that the destination table has a field named ImportFileName, and it has to be removed from the temp table because it will be added as an extra field in the INSERT/SELECT step.

If ImportFileName is not the last column in the table, you have to be careful with the format files because the column ordinal for the fields after ImportFileName will be one less in the temp table than in the destination table.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 10

Scott is right, This is the easy way to create a temp table with the same fields as another table.

Debbie, WHERE 0 = 1 means that since 0 never equals 1 then no rows will be moved by Select ... Into statement, just the table structure will be created at this poit which is exactly what you need. (Y.V - my preferences are "WHERE 1 = 2" )

Regards,Yelena Varsha

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Ninja's_RGR'us

SSC Guru

Points: 294069

September 26, 2006 at 10:42 am

#662526

Why 1 = 2 vs 0 = 1 if I may ask??

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 12

Just Because 🙂

No real difference, this is a matter of preferences.

Regards,Yelena Varsha

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

LOL, this is what you get for not being clear. I was expecting a cute story like a DBA woops but it looks like it'll have to wait.

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 14

I got a bit confused again over the last post and didnt quite know how to use it but I tried to pull everything together using the extra bit ofn script you sent me. Here is the stored procedure as it stands now (Ive made new / amended sections BOLD!)

CREATE procedure usp_ImportMultipleFiles_Leavers_FR @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)

DECLARE @Query2 varchar(1000)

set @count1 =0

create table #x (name varchar(200))

CREATE TABLE #MyTempTable (

UPN nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

Forename nvarchar (255) COLLATE Latin1_General_CI_AS NULL ,

MiddleName nvarchar (255) COLLATE Latin1_General_CI_AS NULL ,

Surname nvarchar (255) COLLATE Latin1_General_CI_AS NULL ,

PreferredNames nvarchar (255) COLLATE Latin1_General_CI_AS NULL ,

DateOfBirth datetime NULL ,

Gender nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

FormerUPN nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

Postcode nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

LEA nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

DFES nvarchar (50) COLLATE Latin1_General_CI_AS NULL ,

EntryDate datetime NULL ,

LeavingDate datetime NULL ,

FRFileName datetime NULL ,

DateImported datetime NULL)

set @query ='master.dbo.xp_cmdshell "dir '+rtrim(@filepath)+rtrim(@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 '+ #MyTempTable + ' FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'',FIRSTROW = 2, FORMATFILE = ''\\ws-74015\changes\changes.fmt'' )'

--print @query

exec (@query)

-- your Bulk Insert inserted into the temp table instead of the permanent table. Now we have to move the result into your @tablename adding @Filepath+@Filename as the last field:

set @query2 ='Insert ' + @tablename +' Select *,' + ''''+ @Filepath+@Filename + '''' + 'from #MyTempTable'

--print @Query2

exec (@query2)

drop table #y

GO

When I try and create this SP I get Line 52: Incorrect syntax near '#y'. When I tried to run it without dropping table #y at the end it says Line 50: Incorrect syntax near ')'.

I cant figure out what the problem is with the code, but its probably something quite simple.

Any help would be greatly appreciated.

Debbie