bulk load data ? - SQL Server Forums

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

bulk load data ?

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-09 : 16:05:22

I have a huge csv file need upload to database. If we run a query, query will be


insert into test1..ddd(col1, col2, insertTime) values("col1","col2",GETDATE())

but my csv file only has two columns, col1, col2. then what about insertTime ? I can't modify table structure.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 16:09:48

Maybe you can post the DDL of the Table...however

Can you creatye a staging table

CREATE Stage_Table(Col1 datatype, Col2 datatype)
GO

Declare @cnd varchar(8000)

SELECT @cmd = 'dbname.dbo.bcp Stage_Table in D:\filepath\filename -c -t"," -Sservername -T'

exec master..xp_cmdshell @cmd

Then do analysis, clean up, whatever, the INSERT INTO From Stage

OR

You can use a format file when you bcp into that table.

Do you know if there are any contraints on your table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 01:08:47

quote:
Originally posted by java148

I have a huge csv file need upload to database. If we run a query, query will be

insert into test1..ddd(col1, col2, insertTime) values("col1","col2",GETDATE())

but my csv file only has two columns, col1, col2. then what about insertTime ? I can't modify table structure.

Thanks

is it one off activity? if yes, you can OPENROWSET for that
datefield put a default constraint to point to GETDATE() and ignore it in insert/select list altogether

http://syntaxhelp.com/SQLServer/OPENROWSET/CSV

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-10 : 09:39:41

quote:
Originally posted by visakh16
is it one off activity?

The BIGGEST Lie in the business

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources