Tuesday, March 27, 2012

Auto create & load in SQL table

Hi does anyone know how to create a sql table and then import a list by just clicking on a button to call a procedure?

CREATE TABLE clients(ClientID VARCHAR(5), ClientName VARCHAR(30), PRIMARY KEY (ClientID));

LOAD DATA LOCAL INFILE 'C:/client.csv' INTO TABLE clients
LINES TERMINATED BY '\r\n';

You can create a stored procedure that create the table if it doesn't exist, and use 'BULK INSERT' to import the data from a file into the table, specifying FIELDTERMINATOR and ROWTERMINATOR, for example:

CREATE PROC sp_ImportData @.filename varchar(200)='C:\client.csv'
as

if exists(select * from sysobjects where name='clients')
drop table clients

exec('CREATE TABLE clients(ClientID VARCHAR(5) PRIMARY KEY, ClientName VARCHAR(30))')


exec('bulk insert clients from'''+@.filename+'''
with FIELDTERMINATOR = ''\r'', ROWTERMINATOR = ''\r\n''')

go

No comments:

Post a Comment