June 13, 2013 at 4:29 pm
Hi Professionals
I am creating a procedure in SQL to create a table which i then bulk insert the spreadsheet into the table, the problem I have is that the first 3 columns will always be the same. Software_manufacturer,Product_name and Product_version but the 4th column onwards is unknown. In other words there could be 4 columns or there could be 10 columns. It does not matter what these are called but I need to capture the rest of the columns relating to the spreadsheet that is going to be imported.
Is there a way round this, I am writing this in PHP SQL and HTML with a bit of javascript
here is my code
USE [TestData]
GO
/****** Object: StoredProcedure [dbo].[importspreadsheet] Script Date: 06/13/2013 13:38:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[importspreadsheet]
@importedfile nvarchar(50),
@table_name nvarchar(100)
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'
)
declare @sql nvarchar(1000),
@sqldrop nvarchar(1000)
set @sqldrop = 'DROP TABLE dbo.' + quotename(@table_name);
set @sql = 'create table dbo.' + quotename(@table_name, '[')
+ '(software_manufacturer nvarchar(max) null,
product_name nvarchar(max) null,
product_version nvarchar(max) null,
col4 nvarchar(max) null);';
exec (@sqldrop)
exec (@sql)
print (@sql)
DECLARE @cmd nvarchar(max)
SET @cmd = 'BULK INSERT newtable
FROM ''C:\inetpub\wwwroot\uploads\'+ @importedfile +
''' WITH ( FIRSTROW = 4,
FIELDTERMINATOR = '','',
ROWTERMINATOR = '''')'
--PRINT @cmd
EXEC(@cmd)
END
June 13, 2013 at 6:34 pm
HI ADMIN
CAN YOU CLOSE THIS TOPIC AS I HAVE FIGURED IT OUT AND DONT KNOW HOW TO MARK AS SOLVED OR CLOSE IT
THANKS
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply