|
Thanks.....
a data file like this:
124356 1
376622 1
736197 1
............... .
the first 7 characters is one filed and the 8th character is the other filed. Can anyone show me the bulk insert syntax for this transaction, many thanks.....
Assumed destination and from as below:
BULK INSERT AdventureWorks.Sales.SalesOrderDetail
FROM 'f:\orders\lineitem.tbl'
WITH
|
|
|
|
|
|
hi Mika,
so I have to first create a table for this txt.file, and then I should use bcp command to generate a formatfile, right?
there is only one line like this example bcp command:
bcp AdventureWorks.HumanResources.Department format nul -c -f Department-c.fmt -T
how can I define that my first field in data file is 8 characters?
can you be more detailed, thanks very much..
|
|
|
|
|
Yes, the table must exist. Try something like this:
CREATE TABLE BulkTest (
Column1 bigint,
Column2 bigint)
Then create the format file using notepad and save it:
<?xml version="1.0"?>
<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="NativeFixed" LENGTH="7"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Column1" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="2" NAME="Column2" xsi:type="SQLBIGINT"/>
</ROW>
</BCPFORMAT>
And then bulk insert the data:
BULK INSERT BulkTest FROM 'f:\orders\lineitem.tbl' WITH (FORMATFILE='f:\orders\BulkInsertFormat.txt')
Mika
|
|
|
|
|
hi all,
i write the following code for extract the data from a database, data also retrieved but there is a problem ...code is here
OracleConnection dataConnection = new OracleConnection();
ArrayList objList = new ArrayList();
try
{
dataConnection.ConnectionString = "Data Source=orc;Persist Security Info=True;User ID=scott;Password=tiger;Unicode=True";
dataConnection.Open();
OracleCommand datacommand = new OracleCommand("SELECT * FROM DEPT", dataConnection);
OracleDataReader datareader = datacommand.ExecuteReader();
Dept d = new Dept();
while (datareader.Read())
{
d.DNAME = datareader["DNAME"].ToString();
d.DEPTNO = datareader["DEPTNO"].ToString();
d.LOC = datareader["LOC"].ToString();
objList.Add(d);
}
datareader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataConnection.Close();
}
try
{
cmbDname.DataSource = objList;
cmbDname.DisplayMember = "DNAME";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
but i amazed ......all filled data of combobox is same i.e DNAME
like : OPERATIONS (which is last row of DEPT table)
because
objList store only last data (OPERATIONS)........
but when i replace
objList.Add(d); with
objList.Add(d.DNAME); then objList store different DNAME..........but this time objList has only one colum <dname>....
my problem is that how can i store whole row in objList 
|
|
|
|
|
|
i am trying to develop a school fees management system, can any1 help me identifying the database fields required for a school management system. thank u.
|
|
|
|
|
If you're trying to get your homework done by somebody else, nobody here will help you. The question you've asked is so trivial that google will throw up a million results for it. If you'd gone to school you would know most of the required fields by now.
Make an attempt yourself, explain what you've done, and then ask here if you still face any hurdles. We'll be more than willing to help if you've a genuine problem in your hands.
|
|
|
|
|
SimulationofSai wrote: Make an attempt yourself, explain what you've done, and then ask here if you still face any hurdles. We'll be more than willing to help if you've a genuine problem in your hands.
I second that.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Why would you think someone will help you. We dont work for you
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi,
I have an sql query that retrieves data for some ID where this ID has more than one data related to it.
Example:
ID Symbol
01 A
01 B
01 C
of course the primary key is (ID, Symbol).
when you select the ID and Symbol, it retieves the previous three rows.
I need to retrieve these data in one row by concatinating the Symbol to itself; i.e: I need the result to be just like the following:
ID Symbol
01 A and B and C
Is this possible???
Kind Regards
OBarahmeh
|
|
|
|
|
You could do this using a cursor[^] to loop through the rows and build up the string.
I wouldn't advise it for large resultsets as you could end up with performance issues.
|
|
|
|
|
Yes, it is possible 
CREATE TABLE #T (ID varchar(2), Symbol varchar(1));
INSERT INTO #T(ID, Symbol) SELECT '01', 'A'
INSERT INTO #T(ID, Symbol) SELECT '01', 'B'
INSERT INTO #T(ID, Symbol) SELECT '01', 'C'
INSERT INTO #T(ID, Symbol) SELECT '02', 'B'
INSERT INTO #T(ID, Symbol) SELECT '02', 'D'
--
SELECT
ID,
STUFF(
<code>(SELECT ' and ' + Symbol FROM #T WHERE ID = t.ID FOR XML PATH(''))</code>
, 1
, 5
, ''
) AS Symbols
FROM #T AS t
GROUP BY ID
--
DROP TABLE #T
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
|
|
|
|
|
I'm always amazed at the lack of functions in SQL Server.
There should be a JOIN CONCAT aggregate function.
There should also be TRIM (not just LTRIM and RTRIM), and PADding functions.
Ah, well, an opportunity to write my first CLR aggregate function! 
Ha! The example for creating a CLR aggregate is Concatenate! 
Whoo hoo! Copied the example and tweaked it a little.
SELECT ID
,Symbol=STUFF(dbo.Concatenate(' and '+Symbol),1,5,'')
FROM Symbol
GROUP BY ID
ORDER BY ID
,Symbol
modified on Thursday, July 31, 2008 11:33 AM
|
|
|
|
|
I don't know why you people are so scared of XML
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
|
|
|
|
|
Who is?
|
|
|
|
|
SELECT SymbolA.Id
,Symbol=SymbolA.Symbol + ' and ' + SymbolB.Symbol + ' and ' + SymbolC.Symbol
FROM Symbol SymbolA
INNER JOIN Symbol SymbolB
ON SymbolA.ID=SymbolB.ID
AND SymbolB.Symbol='B'
INNER JOIN Symbol SymbolC
ON SymbolA.ID=SymbolC.ID
AND SymbolC.Symbol='C'
WHERE SymbolA.Symbol='A'
|
|
|
|
|
Well... Let's just hope he is not using the whole alphabet for the Symbol field
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
|
|
|
|
|
That's not in the scope of the problem as presented.
|
|
|
|
|
I have a main report which has 2 parameters (int and bit).The report is working fine.
I have another report which is related to the main report and which has the same parameters (int and bit).
I am using the second report as subreport to the main report.
Done with the settings like passing the parameters and giving the name of subreport by right clicking the gray subreport box and selecting the properties.
Yet when I preview the main report I am not able to see the subreport.
what else settings i need to do?
Thanks in advance.
|
|
|
|
|
hii frnds,i am developing a appln based on three tier arch..in the db file i have written the insert,update and delete methods with a same spname (spname="spmanage")now i am unable to write the logic for doing the simultaneous action (insert,update,delete),can any one write the logic for sp in a sigle call based on selection i have to get the operation i want.
I have taken ActionType in basedb...by using enums..and included it in my DB class.
for add=1,update=2,delete=3.
santosh
|
|
|
|
|
Why would wanna Combine Different Operations in one SP?
see this http://www.codeproject.com/KB/cs/N-Tier22.aspx[^]
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Its not good practice to have differing operations in a single stored procedure. The first time it is executed the query plan will be cached., so say you ran an update first the query plan for subsequent delets and inserts would be rubbish.
Use a proc for each function.
santosh042 wrote: can any one write the logic for sp in a sigle call based on selection i have to get the operation i want.
You designed it, you code it!
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have a Clickonce .NET 2.0 winforms application located on a network share within our company intranet.
I'm trying avoid having to deal with other departmens in setting up an AD group, managing users etc. because it is too much of a hassle eventhough I would love to use the integrated windows security... As far as I can tell the only other solution is to create a SQL server user with appropriate permissions on the database my program is connecting to and have my program use that SQL server user account to access the database but if I do that the connection string will be just plain text anyone can read... unless it is encrypted.
Placing the connection string within the code is not acceptable since with the .NET code it would be easily accessible using MSIL or Reflector. Placing the conneciton string within the app.config file and encrypting it (http://msdn.microsoft.com/en-us/library/89211k9b(VS.80).aspx[Securing Connection Strings]) seemed like a good idea but it turns out the connection string must be encrypted/decrypted by the computer that will be running the program (The connection string can only be decrypted on the computer on which it was encrypted.) so it looks like the connection string would have to sit unencrypted on the network share and than my program would encrypt it during installation... having an unencrypted conneciton string on a network share that must be available to eveyrone to read from in order to support the Clickonce deployment doesn't sound like a good idea to me...
Anyway, does anyone know a good solution for this problem other than using integrated securtiy and having to deal with a 72 hour turnaround for any changes to the AD group?
TIA
|
|
|
|
|
It's not necessary that you use the connectionstring element in web.config. You can define your own element and encrypt it yourself using the .NET encryption classes. You could perhaps store the decryption key in a shared location.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
