May 16, 2013 at 10:51 pm
Hi Professionals.
I have the following procedure which bulk inserts a csv file into the newtable.
Can this procedure be amended so that I can use variables passed in by what the user selects as I am trying to execute this procedure with front end written PHP
so for instance can the bulk insert part be changed
FROM ''C:\inetpub\wwwroot\Synergy_SAR_Raw.csv''
TO ''C:\inetpub\wwwroot\VARIABLE PASSED IN HERE''
--As VARIABLE PASSED IN HERE is where I would want the user to choose a file from the directory in the form of a dropdown box on the front end
here is my code
USE [TestData]
GO
/****** Object: StoredProcedure [dbo].[importSAR] Script Date: 05/17/2013 14:42:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[importSAR]
as
BEGIN
IF EXISTS (
SELECT *
FROM sys.tables
JOIN sys.schemas
ON sys.tables.schema_id = sys.schemas.schema_id
WHERE sys.schemas.name = 'dbo'
AND sys.tables.name = 'newtable'
)
DROP TABLE dbo.newtable
CREATE TABLE dbo.newtable
(
Software_Manufacturer Nvarchar(MAX) null,
Part Nvarchar(MAX) null,
Product_Description Nvarchar(MAX) null,
Edition Nvarchar(MAX) null,
Version Nvarchar(MAX) null,
License_Type Nvarchar(MAX) null,
Active_Qty NVARCHAR(MAX) null,
Maintenance Nvarchar(MAX) null,
Expiry_Date NVARCHAR(max) NULL,
Qty_Used Nvarchar(MAX) null,
Quantity_Pooled Nvarchar(MAX) null,
Serial_1 Nvarchar(MAX) null,
Serial_2 Nvarchar(MAX) null,
Barcode Nvarchar(MAX) null,
Authorisation_Number Nvarchar(MAX) null,
Additional_Licence_Data_1 Nvarchar(MAX) null,
Additional_Licence_Data_2 Nvarchar(MAX) null,
Comments Nvarchar(MAX) NULL,
Business_Unit Nvarchar(MAX) NULL,
Cost_Centre Nvarchar(max) NULL,
Purchase_Order nvarchar(MAX) null,
Date_Of_Purchase NVARCHAR(max) NULL,
Date_Of_Delivery NVARCHAR(max) NULL,
Invoice Nvarchar(MAX) null,
Supplier Nvarchar(MAX) null,
Purchasing_Officer Nvarchar(MAX) NULL,
actual_image Nvarchar(MAX) null
)
DECLARE @cmd nvarchar(max)
SET @cmd = 'BULK INSERT newtable
FROM ''C:\inetpub\wwwroot\Synergy_SAR_Raw.csv''
WITH ( FIRSTROW = 4,
FIELDTERMINATOR = '','',
ROWTERMINATOR = '''')'
EXEC(@cmd)
end
May 16, 2013 at 11:13 pm
Yes, add a parameter to the sp of the Filename and adjust the sql query that is run.
There are many resources for sp and parameters on the web. Here is one: http://www.mssqltips.com/sqlservertutorial/162/how-to-create-a-sql-server-stored-procedure-with-parameters/
Also, you could truncate the table instead of dropping and recreating it as well.
May 16, 2013 at 11:17 pm
I have an update but still stuck around the bulk insert quotes part
i have passed in a variable
ALTER procedure [dbo].[importSAR]
@importedfile nvarchar(50)
and tried this
BULK INSERT newtable
FROM ''C:\inetpub\wwwroot\'' ''@importedfile''
WITH ( FIRSTROW = 4,
FIELDTERMINATOR = '','',
ROWTERMINATOR = '''')'
but when i try to execute it like so
EXEC dbo.importSAR @importedfile = 'Synergy_SAR_Raw.csv'
I get the error
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I looks like it is the part around the bulk insert that is causing the error it maybe the quotes around the variable or something
FROM ''C:\inetpub\wwwroot\'' ''@importedfile''
May 16, 2013 at 11:21 pm
May 16, 2013 at 11:24 pm
I have done that although this looks a little closer it comes up with the error
Incorrect syntax near 'Synergy_SAR_Raw'.
May 16, 2013 at 11:40 pm
Sorry, I am somewhat tired and not near a pc.
Try:
'BULK INSERT newtable
FROM ''C:\inetpub\wwwroot\'+ @importedfile +
''' WITH ( FIRSTROW = 4,
FIELDTERMINATOR = '','',
ROWTERMINATOR = '''')'
Also, might want to review http://msdn.microsoft.com/en-us/library/ms188365(v=sql.105).aspx and row terminators as well.
May 16, 2013 at 11:53 pm
that works thanks very much for your prompt response
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply