How do I insert data from a flat file into an existing SQL databas

How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all together.
.
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC"
)
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKSYou can use the command line utility - bcp.exe to load from a flat file.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Bermychild" <Bermychild@.discussions.microsoft.com> wrote in message
news:7BDDC48D-4D33-42E5-B69B-11976FE75CC4@.microsoft.com...
How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all
together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS|||Try to use BCP or BULK INSERT.
BCP can be done from the command line
BULK INSERT would be done from inside Query Analyzer.
For example: Store the following in a .bat file and run...
----
--
bcp [database].[owner].[tablename]
IN "D:\DataFilePath\DataFileName.ext"
-f D:\FormatFilePath\FormatFileName.fmt
-o D:\OutputFilePath\OutputFileName.fmt
-m 10000 -F 1 -c -S [ServerName] -T
----
--
Since this will be the first time you are runnning, remove the line
with
" -f D:\FormatFilePath\FormatFileName.fmt"
The bcp utility will prompt you when u run it and create the Format
File automatically.
The BULK INSERT Format is easier
BULK INSERT
database_name.schema_name.table_name
FROM 'data_file'
WITH
(
, FIELDTERMINATOR = ','
, FIRSTROW = 1
, KEEPNULLS
, MAXERRORS = 10000
, ROWTERMINATOR = '\n'
, TABLOCK
, ERRORFILE = 'file_name'
)
Also, if the flat file data is not in the same format as the
destination table, I recommend creating a TEMP TABLE...
CREATE TABLE ##bulkinsert() to match the specification of the datafile,
BULK INSERT or BCP into that file, and then use a normal SQL Query to
transport data from the temp table to the final table. This also allows
you the opportunity to clean the data in the temp table by removing
quotation marks, and filtering out invalid data prior to moving data to
your production table.s|||If you're using .Net 2.0, you can look at my component I sell,
http://www.csvreader.com , that gives syntax like below that I took off
my code samples page. It basically inserts at DTS speed and doesn't
have some of the issues as BCP and BULK INSERT.
Using loader As CsvDataReader = New CsvDataReader("somefile.csv")
loader.HasHeaders = True
loader.Columns.Add("varchar") ' First
loader.Columns.Add("varchar") ' Last
loader.Columns.Add("datetime") ' First Sale
loader.Columns.Add("money") ' Amount
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy("Data
Source=myServer;Initial Catalog=Test;User ID=sa;Password=")
bulkCopy.DestinationTableName = "Customer"
bulkCopy.WriteToServer(loader)
End Using
End Using
Bruce Dunwiddie
dizzler wrote:
> Try to use BCP or BULK INSERT.
> BCP can be done from the command line
> BULK INSERT would be done from inside Query Analyzer.
> For example: Store the following in a .bat file and run...
> ----
--
> bcp [database].[owner].[tablename]
> IN "D:\DataFilePath\DataFileName.ext"
> -f D:\FormatFilePath\FormatFileName.fmt
> -o D:\OutputFilePath\OutputFileName.fmt
> -m 10000 -F 1 -c -S [ServerName] -T
> ----
--
> Since this will be the first time you are runnning, remove the line
> with
> " -f D:\FormatFilePath\FormatFileName.fmt"
> The bcp utility will prompt you when u run it and create the Format
> File automatically.
>
> The BULK INSERT Format is easier
>
> BULK INSERT
> database_name.schema_name.table_name
> FROM 'data_file'
> WITH
> (
> , FIELDTERMINATOR = ','
> , FIRSTROW = 1
> , KEEPNULLS
> , MAXERRORS = 10000
> , ROWTERMINATOR = '\n'
> , TABLOCK
> , ERRORFILE = 'file_name'
> )
>
> Also, if the flat file data is not in the same format as the
> destination table, I recommend creating a TEMP TABLE...
> CREATE TABLE ##bulkinsert() to match the specification of the datafile,
> BULK INSERT or BCP into that file, and then use a normal SQL Query to
> transport data from the temp table to the final table. This also allows
> you the opportunity to clean the data in the temp table by removing
> quotation marks, and filtering out invalid data prior to moving data to
> your production table.ssql