|
Hi,
Need to insert only specific columns from a .csv file while importing to SQL Server2005 using bulk import.
My problem is i have 4 columns a,b,c,d in my .csv file,but i need only two columns a and b only to be imported using bulk import.
I was working after seeing this link 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/
Any help is welcome
Thanking you
|
|
|
|
|
new2pgrmg wrote: i need only two columns a and b only to be imported using bulk import
You can achieve this using a Format File. See this article[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
Thanks for the reply.
I have tried it from a similar link from msdn.But i am facing a problem.
I followed this link to the tee.
But i am getting an error;I worked this way
First i created a database by the name "AdventureWorks".
Then i creted a table like ;
USE AdventureWorks;
GO
CREATE TABLE myTestSkipCol
(
Col1 smallint,
Col2 nvarchar(50) NULL,
Col3 nvarchar(50) not NULL
);
GO
I created a .dat file by the name "myTestSkipCol2.dat"
1,DataForColumn3
1,DataForColumn3
1,DataForColumn3
Then i copied the XML file by the name "myTestSkipCol2.Xml" in the C:Drive
<bcpformat xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<record>
<field id="1" xsi:type="CharTerm" terminator="," max_length="7">
<field id="2" xsi:type="CharTerm" terminator="\r\n" max_length="100" collation="SQL_Latin1_General_CP1_CI_AS">
<row>
<column source="1" name="Col1" xsi:type="SQLSMALLINT"> <column source="2" name="Col3" xsi:type="SQLNVARCHAR">
Then i executed the bulk query like;
USE AdventureWorks;
GO
INSERT INTO myTestSkipCol
(Col1,Col3)
SELECT Col1,Col3
FROM OPENROWSET(BULK 'C:\myTestSkipCol2.Dat',
FORMATFILE='C:\myTestSkipCol2.Xml'
) as t1 ;
GO
Here is the problem;I am getting an error "Msg 4820, Level 16, State 1, Line 1
Could not bulk insert. Unknown version of format file 'C:\nm.fmt'.
"
I tried different ways like giving the extension as ".XML" and ".ftm".I tried different permutations and combinations.
Need your help
Many Thanks
|
|
|
|
|
How can you get the error:
new2pgrmg wrote: Could not bulk insert. Unknown version of format file 'C:\nm.fmt'.
if your statement is:
new2pgrmg wrote: INSERT INTO myTestSkipCol
(Col1,Col3)
SELECT Col1,Col3
FROM OPENROWSET(BULK 'C:\myTestSkipCol2.Dat',
FORMATFILE='C:\myTestSkipCol2.Xml'
) as t1 ;
Your query references a format file called myTestSkipCol2.Xml. This isn't consistent with the error message you reported.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Sorry for the mistake but the basic idea is correct.As i told you i was trying different methods and when i found that the posted question had a slight mistake i tried to edit but it was not loading.
The error will be Could not bulk insert. Unknown version of format file 'C:\myTestSkipCol2.Xml'.
Sorry for the mistake
Looking for an answer.
Also can you tell me how i can find the BCP version?
Many Thanks
|
|
|
|
|
Hi all, thanks for looking
I have this 2 queries, they return the same result
I wonder, which one is faster and why?
SELECT dbo.Products.ProductName
FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID
WHERE (dbo.Categories.CategoryName = N'Beverages')
SELECT dbo.Products.ProductName
FROM dbo.Products WHERE dbo.Products.CategoryID IN (SELECT dbo.Categories.CategoryID FROM dbo.Categories WHERE dbo.Categories.CategoryName = N'Beverages')
Thanks
Alexei Rodriguez
|
|
|
|
|
As per my perception is : Inner query is always slower than the join query.
Parwej Ahamad
g.parwez@gmail.com
|
|
|
|
|
in addition to Parwej Ahamad
You can also check Query execution plan, which gives better picture
Regards
KP
|
|
|
|
|
Joins are always faster and efficient, so use joins in place of in conditions.
Do good and have good.
|
|
|
|
|
Hi all, thanks for looking
I have this 2 queries, they return the same result
I wonder, which one is faster and why?
SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Categories.CategoryName
FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID
SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Categories.CategoryName
FROM dbo.Categories, dbo.Products
WHERE dbo.Categories.CategoryID = dbo.Products.CategoryID
Thanks
Alexei Rodriguez
modified on Tuesday, March 4, 2008 11:01 PM
|
|
|
|
|
I beleive there is almost no difference in processing speed.
I ALWAYS use the join method because it is more readable and therefore easier to support.
|
|
|
|
|
This may seem a an odd requirement, it certainly seems to be for me, but can a stored proc extract data from a web service?
We have a centralised management of internal structures and need to have a number of apps pull the data at varying periods (hourly, day, month and adhoc).
I refuse to use linked servers as these present maintenance issues with no dedicated DBA.
Building a web service to supply the data is not a problem and I would like to eliminate the UI for getting the data.
I also can't use a SQL job as these are mostly adhoc extract.
Anyone know if this is possible.
|
|
|
|
|
Hi all,
I am having same challenges too.
I need to call a web service from stored procedure. The web service is actually inserting to a database.
I want to believe it could be done. Is there anyone out there to help.
Thanks.
|
|
|
|
|
Quick question. I have a web app that connects to a database for data entry and reporting. Now, I haven't gone live with it because the client will add more fields to the main database everytime I meet with them to show what I at the time think is a completed web app. I was wondering, can I let them start using the application and still add fields to the database has it becomes necessary without creating an issue for those records that were added before the new fields were created?
I'm still a noob when it comes to SQL, and I don't want to mess anything up.
|
|
|
|
|
When you add new fields, set a default value for the column. If you don't do that, you can't add the column at all, and if you do, then that default value will appear for all existing records. The alternative is to make the columns nullable and then they will default to null, but then you need to deal with null in all your code for new columns, so a default value is better. Null WILL tell you that a column was not there before, if your own code never inserts null, if that is worth the work to make it happen that way.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
tell me how can i add new column in existing table ..i need query of this task brothers... actually i want add new column of datatype of identity...
so plzz tell me query..
thanks 2 all..
|
|
|
|
|
Hi,
To add a column to an existing table, the ALTER TABLE syntax is:
------------------------------------------------------------------
ALTER TABLE table_name
ADD column_name column-definition
For example:
ALTER TABLE NewField
ADD TableName INT IDENTITY(1,1)
------------------------------------------------------------------
This will add a column called NewField to the TableName table.
I hope this helps  .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
thanks brother..
thanks 2 all..
|
|
|
|
|
Hi all,
I need to create a temporary table for the results of my select statement in sql server 2005.
Here is my SQL statement which does the select :
SELECT
DISTINCT
IM.MENU_ID AS 'StructureId',
IM.MENUITEM_ID AS 'CategoryId',
EA.Title AS 'Name'
FROM
HN_IM_JOIN IM
INNER JOIN HNIM_ElementAttributes EA
ON EA.OwnerId = IM.MENUITEM_ID
INNER JOIN HNIM_Element EL
ON EL.ID = IM.MENUITEM_ID
WHERE EA.Lcid = 'en'
AND IM.MENU_ID = 'bd966aba'
ORDER BY EA.Title
but I want to write these to a temporary table.
Any Idea please
|
|
|
|
|
Try:
SELECT DISTINCT IM.MENU_ID AS 'StructureId',
IM.MENUITEM_ID AS 'CategoryId', EA.Title AS 'Name'
INTO #temp1
FROM HN_IM_JOIN IM
INNER JOIN HNIM_ElementAttributes EA
ON EA.OwnerId = IM.MENUITEM_ID
INNER JOIN HNIM_Element EL
ON EL.ID = IM.MENUITEM_ID
WHERE EA.Lcid = 'en'
AND IM.MENU_ID = 'bd966aba'
ORDER BY EA.TitleRegards
Andy
|
|
|
|
|
Which is the best method ( with .net c# vs2005) to develop an application that accesses (read and write) two sql databases stored on different machines (net connection) ?
Thank you
Mauro Savoiardo
|
|
|
|
|
I don't understand what you mean by "best method" in this context. You access a database using a connection. The connection strings will be different. That's the only difference I see between accessing one database and accessing two.
Did you have something specific in mind?
|
|
|
|
|
My problem was about the connection to the database stored on the second machine from an application installed on first one
bye
Mauro Savoiardo
|
|
|
|
|
Member 487672 wrote: My problem was about the connection to the database stored on the second machine from an application installed on first one
Unless I'm missing something I don't see why that is a problem. To connect to the database you use a connection string that tells your .NET application where the database is. It doesn't matter if it is on the local machine or a remote machine, the connection string always needs to say where the database is.
|
|
|
|
|
Hi to everyone!
I have one problem, when I want to make an update to some record in the table, I am entering the data for it, and instead just for one field, the UPdateCommand is putting the same data in all the fields. Example:
I have two fields: ID and Name. And I am making an update of the name, and it's changed all the data for all the fields under Name. I think that I should use some WHERE statement, but I am beginner in SQL and every help will be appreciated. Thx
Example, before update, I had:
ID:1
Name:a
------------
ID:2
Name:b
-----------After update-------------
Update name: "c"
ID:1
Name:c
------
ID:2
Name:c
-------
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
