|
Bulk load to a staging table, then clean the data when transferring to your production tables. This will allow you to leave the BulkInsert task alone, and expand the tranfer task as changes to the underlying data or destination tables changes. This is why we call it ETL. Extract the data from the source, transform it match your systems and the Load it into your systems.
- Sage
|
|
|
|
|
Excuse me, can you mention more detail and how it to be done such as give some simple example?
Thank you for you response.
|
|
|
|
|
This was thrown together, but should work if you replace the tablenames, columnnames and filenames with values that apply to your application
BULK INSERT DBName.dbo.StagingTable -- StagingTable can be a copy of your destination table
From 'C:\YourFile.txt' -- actual path to your file
With (
FieldTerminator = ',',
RowTerminator = '\n' -- for new line
)
Alter Table StagingTable
ADD SafeToTransfer BIT NOT NULL Default(1)
-- Run some integrity checks prior to attempting to tranfer
if ( select Count(*) From DBName.dbo.StagingTable Where IsNumeric( ColumnA )=0 ) >0
BEGIN
Update DBName.dbo.StagingTable
Set SafeToTransfer = 0
Where IsNumeric( ColumnA )=0
END
if (select Count(*) From DBName.dbo.StagingTable Where isDate( ColumnB )=0 ) >0
BEGIN
Update DBName.dbo.StagingTable
Set SafeToTransfer = 0
Where isDate( ColumnB )=0
END
if ( select Count(*) From DBName.dbo.StagingTable Where isNumeric( ColumnD )=0 ) >0
BEGIN
Update DBName.dbo.StagingTable
Set SafeToTransfer = 0
Where isNumeric( ColumnD )=0
END
Insert Into DBName.dbo.DestinationTable
(
ColumnA, -- an INT Column
ColumnB, -- a datetime column
ColumnC, -- a varchar(100) column
ColumnD, -- a numeric(9,5) column
ColumnE -- another varchar(100) column
)
SELECT
CAST( ColumnA as int), -- an INT Column
CAST( ColumnB as datetime), -- a datetime column
LEFT( ColumnC, 100), -- a varchar(100) column
CONVERT( numeric(9,5), ColumnD), -- a numeric(9,5) column
LEFT( ColumnE, 100) -- another varchar(100) column
From DBName.dbo.StagingTable
Where SafeToTransfer = 1
Alter Table StagingTable
DROP COLUMN SafeToTransfer
|
|
|
|
|
I am looking for a good method to do table lookups in a 2003 SQL database. I am retrieving products from an item table and then want to do value lookups in related tables in the same database like category name, category type, brand name etc. These values are referenced in the item master file by guid links to related tables.I am using a datareader to loop through the Select records and then I am writing results to a tab delimited data file for a data conversion. I am limited to net 1.1 and so can't use the 2.0 executescalar for single lookups.
What would be the more efficienct method to use?
'Dim myItemData As New DataReader
Dim myItemReader As SqlClient.SqlDataReader
Dim myItemCommand As New SqlClient.SqlCommand
Dim myConnection = New SqlClient.SqlConnection("server=sql01.xxx")
myItemCommand.Connection = myConnection
myItemCommand.CommandText = "SELECT ""mf_items"".""item_guid"", ""mf_items"".""item_description"", ""mf_items"".""item_wholesale_price"", ""mf_items"".""item_description_title"",""mf_items"".""item_cd"",""mf_items"".""category_guid"" FROM ""CDB006"".""dbo"".""mf_items"" WHERE ""mf_items"".""item_closed"" = 0 And ""mf_items"".""item_visible"" = 1 AND ""mf_items"".""item_available"" = 1"
myItemCommand.Connection.Open()
myItemReader = myItemCommand.ExecuteReader()
Dim myfileout As StreamWriter
myfileout = File.CreateText(Server.MapPath("out.txt"))
myfileout.WriteLine("link" & vbTab & "title" & vbTab & "description" & vbTab & "price" & vbTab & "image_link" & vbTab & "category" & vbTab & "id")
While myItemReader.Read
If myItemReader.Item(4).ToString() > "" And Val(myItemReader.Item(2).ToString) > 0 Then
'what method to to look up for example the category name using the category_quid referenced in the item master
'Write out record for parts file
Ronhawker
|
|
|
|
|
The simplest way is to use join in sql statement
for i.e.
select i.itemname, i.categorytype from item i
inner join categorytype c on c.categorytype=i.categorytype
pass above statement is sqlcommand object.
Thanks & Regards
Kumar Prabhakar
abc
|
|
|
|
|
I have Error In Visul C++ When Conncet To table
"
OleDbConnection *con;
con=new OleDbConnection();
con->ConnectionString=S"Provider=VFPOLEDB.1;Data Source=c:\1;";
con->Open();
"
Error :
An unhandled exception of type 'System.ArgumentException' occurred in system.data.dll
Additional information: Format of the initialization string does not conform to specification starting at index 34.
but I Connect Complete With Visul Basic.net But I Dont Connect To Visul c++.net
Please Help Me
|
|
|
|
|
C, C++, C#, and all other members of the C family use the \ character as a string escape character. The combination \1 becomes Unicode character U+0001, a control character normally used as Start Of Heading, once the compiler has translated the code.
To prevent this happening, escape the backslash with another backslash:
con->ConnectionString=S"Provider=VFPOLEDB.1;Data Source=c:\\1;";
|
|
|
|
|
Hi,
I have a column in my database that holds a description. I want to be able to search for one word in the description and get a match if the word exists.
I'm using VB.Net and ASP.Net(2005).
Thanks
|
|
|
|
|
If you mean that you want to retrieve records that have a particular word in the description:
SELECT * FROM myTable WHERE myDescriptionField LIKE '%myWord%'
Steve
|
|
|
|
|
Thanks that is what I wanted to know
|
|
|
|
|
I have a datagrid that is populated in two columns with a smalldatetime value. The column is only reflecting the date portion in the datagrid, but the full datetime in the query. How can I format this comlum to reflect just the time portion of the value?
Thanx in advance
Jude
|
|
|
|
|
Create a DataGridTableStyle. Then Create DataGridColumnStyle for each column. In the format property for the column you mention, enter:
"t" - for ShortDateTime
"T" - for LongDateTime
Steve
|
|
|
|
|
Thanx for the reply. Could you give an example of this or a link explaining this?
Thank you
Jude
|
|
|
|
|
Are you using Visual Studio?
If so, you can do this using the properties window in the designer.
Or do you need code?
Steve
|
|
|
|
|
I found it..I'll work on it a little and figure it out.
I have another question regarding datasets and datarows/columns.
I populated a combobox with this:
SqlCommand cmdSelect = sqlConnection1.CreateCommand();
cmdSelect.CommandText = "SELECT EmpID,EmpName FROM Employees";
SqlDataAdapter sdaEmp = new SqlDataAdapter();
sdaEmp.SelectCommand = cmdSelect;
sdaEmp.Fill(dstEmp,"Employees");
cboEmployee.DataSource = dstEmp.Tables["Employees"];
cboEmployee.DisplayMember = "EmpName";
cboEmployee.SelectedIndex = 0;
Now what I want to do when the user changes the 'selected index' of the combo box, to grab the info in the corresponding EmpId column from the datatable above.
What would be the correct porocedure to accomplish this?
Thanx in advance!
Jude
|
|
|
|
|
Greetings:
I am creating an SQL table programmatically using C#. I have set up my SqlCommand and I am using ExecuteNonQuery to execute the following SQL statement:
<br />
CREATE TABLE StationData (StnID INT NOT NULL AUTO_INCREMENT, Name CHAR(50), Province CHAR(20), Latitude FLOAT, Longitude FLOAT);<br />
This statement is throwing an exception and the exception message says: "Incorrect syntax near "AUTO_INCREMENT"".
If I remove the AUTO_INCREMENT specifier from the above statement, then the operation completes successfully and the table and columns are created as expected. It just seems to be that AUTO_INCREMENT specifier.
I have checked many similar example statements from various sources and I don't see anything wrong with what I have done here.
Any suggestions?
Thanks,
Mark
|
|
|
|
|
Try this:
CREATE TABLE StationData (StnID INT NOT NULL <code>IDENTITY </code>, Name CHAR(50), Province CHAR(20), Latitude FLOAT, Longitude FLOAT);
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
OK, thanks. It worked.
Just wondering, however, IDENTITY seems to have the same effect as AUTO_INCREMENT. What then, is the status of the AUTO_INCREMENT specifier? Obsolete? Why was SQL rejecting it?
Thanks,
Mark
|
|
|
|
|
MySQL syntax is AUTO_INCREMENT[^]
SQL Server is IDENTITY [^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I'm going to have to watch out for that. I've seen a lot of web tutorials and references that talk about AUTO_INCREMENT but it turns out that they were all talking about MySQL, not SQL. I had assumed that they would be similar in syntax but this is turning out to be not the case.
Thanks again,
Mark
|
|
|
|
|
I'm working with an existing database so splitting date values into seperate DD, MM & YYYY columns is not ideal. Is there a way to cast smalldatetime to British date format: DD/MM/YYYY ?
Thanks.
|
|
|
|
|
Look up CONVERT in SQL Books Online
CONVERT ( SMALLDATETIME , your_smalldatetime_field , 103 )
|
|
|
|
|
I need to write a stored procedure that will copy rows from one table and insert them an another table based on a unique id. Any Ideas? I am very new to writing stored procedures so any help would be greatly appreciated.
Thank You
|
|
|
|
|
To create stored procedure write following
CREATE PROCEDURE [procedureName]
After that, just write your query.
Example:
CREATE PROCEDURE copyData<br />
@selectID int <br />
<br />
AS <br />
<br />
SELECT ID, Name <br />
INTO newTable<br />
FROM origTable <br />
WHERE origTable.ID = @selectID
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
Try this
Copying a Table into an Existing Table
insert into originaltable select * from mytablecopy where [Condition]
Navi
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
