create table and table columns dynamically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create table and table columns dynamically

Author

Topic

arpana patil
Starting Member

24 Posts

Posted - 2012-09-04 : 08:03:07

How to create table along with columns dynamically in sql,while importing date from csv.

Regards,
Arpana

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-04 : 08:12:14

Depends on how you are importing. For example, if you are using Import/Export wizard, it will by default, create the destination table automatically. To use import/export wizard, right-click on the database name in SSMS object explorer, select Tasks->Import Data and follow through the wizard.

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-09-04 : 08:12:44

Create TestTable

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,......James,.......Smith,.........19750101
2,......Meggie,......Smith,.........19790122
3,......Robert,......Smith,.........20071101
4,......Alex,........Smith,.........20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
collected from, refer:::

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Thanks

arpana patil
Starting Member

24 Posts

Posted - 2012-09-04 : 08:18:16

I am importing through bulk insert statement,i want table to be created dynamically with same column names as in csv and data type.

arpana patil
Starting Member

24 Posts

Posted - 2012-09-04 : 08:19:41

Hi i dont want to create table manually

quote:
Originally posted by anuraag205

Create TestTable

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,......James,.......Smith,.........19750101
2,......Meggie,......Smith,.........19790122
3,......Robert,......Smith,.........20071101
4,......Alex,........Smith,.........20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
collected from, refer:::

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources