Bulk Upload

I need to upload multiple reports produced on another server. Is there a
script or utility that will allow me to do this?
Thanks!On Jul 3, 10:58 am, REI <R...@.discussions.microsoft.com> wrote:
> I need to upload multiple reports produced on another server. Is there a
> script or utility that will allow me to do this?
> Thanks!
There is not a quick way to do it; however, this link might give you
some insight.
http://technet.microsoft.com/en-us/library/ms159720.aspx
Regards,
Enrique Martinez
Sr. Software Consultantsql

Bulk load multiple rows

Hi
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType> </xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when havin
g
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel Nhttp://msdn.microsoft.com/library/d... />
sqlxml.asp|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm" .

Bulk load multiple rows

Hi
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType></xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when having
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel N
http://msdn.microsoft.com/library/de...exchsqlxml.asp
|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm" .

BULK INSERT row terminator

Hello all,
I have a multiple text files with an odd row terminator. If you were to
examine it in VB it would be like a "CrCrLf" instead of just "CrLf". In
HEX it is DDA instead of just DA. When I am trying to import into my
table using BULK INSERT I use "\n" as the row terminator but that is
putting the the previous character into the column and then it signals
a carriage return when I attempt to query the data.

Any suggestions on what I should use as the row terminator? Is it
possible to tell BULK INSERT to use something like "CHAR(10)\n"?

"\n\n" does NOT work.

Thanks in advance.I found it!
"\r\n" works like a charm.. "\r" is for carriage return only.. I'm
surprised I didn't already know that.

I found the answer here:
http://www4.dogus.edu.tr/bim/bil_ka...l65dba/ch16.htm

under table:
Table 16.4. Valid field terminators.
Terminator Type Syntax
tab \t
new line \n
carriage return \r
backslash \\
NULL terminator \0
user-defined terminator character (^, %, *, and so on)sql

Bulk Insert performance

I have a situation where I need to do multiple inserts into the sql mobile db at one time. I am wondering what would be the most efficient method to do this. Right now I am just doing many inserts, but the performance is lacking. I tried to wrap all the inserts into 1 sql command and process it like that, but it does not seem to want to execute. Any help would be appreciated.

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Darren,

What is the best method to import a large amount of data into SQL Mobile? Are you saying that Batching isn't supported? Also, SQL Server will not be installed on our servers because of licensing costs. So this takes out the replication and RDA methods for imports.

In a C# application, I'm reading ASCII files and need to add to three seperate tables. It takes hours to process 100,000+ records. BTW - I need to support over 2 million records. The total file size will be around 1 gig. Since SQL CE supports files up to 4 gig, I'm not expecting this to be a problem. I'm I right here?

I'm creating an Item Lookup function that will run stand alone in our retail chain. The data (SDF file) The data will be refreshed nightly on the server and will be copied down to the device daily. If you have any suggestions you can email me directly @. jhoran@.fheg.follett.com

Here is an example of my code to add to the vendor table. I'm only using 2 fields in this example.

Why is it so slow?

.......

string p1, p2;

// loop through Vendors

int Len = 0,i=0;

try

{

using (StreamReader sr = new StreamReader("VENDOR.EXP"))

{

string line;

Status = "Exporting Vendors";

while ((line = sr.ReadLine()) != null)

{

Len = line.Length;

p1 = line.Substring(0, 9);

p2 = line.Substring(9, (Len - 9));

i = i++;

this.vendorTableAdapter.Insert(p1,p2);

}

catch (System.Exception ex)

{

MessageBox.Show(ex + " Vendor File could not be read");

}

Darren Shaffer wrote:

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Let me see if I can answer your questions:

1. SQL Mobile/Everywhere/Compact Edition databases have a 4GB limit. If you plan to grow one of these databases larger than 128MB (the default maximum), then you need to set the max database size in your connection string to something larger.

2. In terms of your insert statement - using the TableAdapter is equivalent to using one insert statement after the next - you would be able to realize faster insert time (30-50%) with a parameterized insert statement (be sure to call Prepare() on the command one time and then change the parameter values and reuse the command).

3. If you are loading up this large database with vendor lookup data, you might ask yourself if you could perform this load on the desktop versus on device and then deploy the resulting sdf file to device. For databases as large as yours, you will get the sdf file loaded much much faster on the desktop given the CPU and disk speeds available there.

4. With a database of this size on device, be aware that you should have free storage memory in an amount at least equal to the database size to realize an effective tempdb and hence decent performance.

-Darren

|||This was convered a couple of says ago :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923795&SiteID=1

Using the resultset makes no difference to the insert performance either|||

Thanks Darren,

Pardon my ignorance, but I'm new to C#, VS2005 and SQL, but I've been writing code for decades.

2. Can you send me an example or point me to one, of how to avoid using the tableadapter? I was thinking that it was passing parameters.

3. The largest table will be the item table, but I'm using the same technique on it. We have less than 1000 vendors, but we could have 2.8 million items.

I agree about creating the file on the server and sending it to the device. That is in the design. Is activesync the best way for this or would we be better off to "roll our own"?

4. we are planning on at least 2 gig of memory.

p.s. I liked your Webinar on Mobile device development. It gave me a good jump start on this project. I've been working with mobile computing using C++ v1.52 for a long time, and don't expect that it will take all that long to get up to speed with this.

Thanks again for your help!

|||

Jack,

Instead of using the table adapter, try a parameterized insert. Here is the "pseudo code" to do this:

SqlCeCommand cmd = null
SqlCeParameter param = null

try

cmd = _mysqlceconnection.CreateCommand()
cmd.CommandText = "INSERT INTO tablename VALUES (@.Param1, @.Param2, @.Param3)"

param = New SqlCeParameter("@.Param1", SqlDbType.UniqueIdentifier) // set each parameter's type to match the column in the database
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param2", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param3", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)

cmd.Prepare() // this allows SQL CE to predetermine an execution plan for the insert and cache it

// note you do this one time and it is outside the insertion loop below

While ' loop through your input file line by line, one row per line

cmd.Parameters("@.Param1").Value = firstColumnValueInYourInputFile

cmd.Parameters("@.Param2").Value = secondColumnValueInYourInputFile

cmd.Parameters("@.Param3").Value = thirdColumnValueInYourInputFIle

etc until you have set all the params in the INSERT statement

cmd.ExecuteNonQuery()

End While

I recommend that anytime you are inserting or updating data in SQL Server you use parameterized SQL - the first time your data contains an apostrophe, a comma, an ampersand, etc, you'll see why.

-Darren

Bulk Insert performance

I have a situation where I need to do multiple inserts into the sql mobile db at one time. I am wondering what would be the most efficient method to do this. Right now I am just doing many inserts, but the performance is lacking. I tried to wrap all the inserts into 1 sql command and process it like that, but it does not seem to want to execute. Any help would be appreciated.

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Darren,

What is the best method to import a large amount of data into SQL Mobile? Are you saying that Batching isn't supported? Also, SQL Server will not be installed on our servers because of licensing costs. So this takes out the replication and RDA methods for imports.

In a C# application, I'm reading ASCII files and need to add to three seperate tables. It takes hours to process 100,000+ records. BTW - I need to support over 2 million records. The total file size will be around 1 gig. Since SQL CE supports files up to 4 gig, I'm not expecting this to be a problem. I'm I right here?

I'm creating an Item Lookup function that will run stand alone in our retail chain. The data (SDF file) The data will be refreshed nightly on the server and will be copied down to the device daily. If you have any suggestions you can email me directly @. jhoran@.fheg.follett.com

Here is an example of my code to add to the vendor table. I'm only using 2 fields in this example.

Why is it so slow?

.......

string p1, p2;

// loop through Vendors

int Len = 0,i=0;

try

{

using (StreamReader sr = new StreamReader("VENDOR.EXP"))

{

string line;

Status = "Exporting Vendors";

while ((line = sr.ReadLine()) != null)

{

Len = line.Length;

p1 = line.Substring(0, 9);

p2 = line.Substring(9, (Len - 9));

i = i++;

this.vendorTableAdapter.Insert(p1,p2);

}

catch (System.Exception ex)

{

MessageBox.Show(ex + " Vendor File could not be read");

}

Darren Shaffer wrote:

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Let me see if I can answer your questions:

1. SQL Mobile/Everywhere/Compact Edition databases have a 4GB limit. If you plan to grow one of these databases larger than 128MB (the default maximum), then you need to set the max database size in your connection string to something larger.

2. In terms of your insert statement - using the TableAdapter is equivalent to using one insert statement after the next - you would be able to realize faster insert time (30-50%) with a parameterized insert statement (be sure to call Prepare() on the command one time and then change the parameter values and reuse the command).

3. If you are loading up this large database with vendor lookup data, you might ask yourself if you could perform this load on the desktop versus on device and then deploy the resulting sdf file to device. For databases as large as yours, you will get the sdf file loaded much much faster on the desktop given the CPU and disk speeds available there.

4. With a database of this size on device, be aware that you should have free storage memory in an amount at least equal to the database size to realize an effective tempdb and hence decent performance.

-Darren

|||This was convered a couple of says ago :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923795&SiteID=1

Using the resultset makes no difference to the insert performance either|||

Thanks Darren,

Pardon my ignorance, but I'm new to C#, VS2005 and SQL, but I've been writing code for decades.

2. Can you send me an example or point me to one, of how to avoid using the tableadapter? I was thinking that it was passing parameters.

3. The largest table will be the item table, but I'm using the same technique on it. We have less than 1000 vendors, but we could have 2.8 million items.

I agree about creating the file on the server and sending it to the device. That is in the design. Is activesync the best way for this or would we be better off to "roll our own"?

4. we are planning on at least 2 gig of memory.

p.s. I liked your Webinar on Mobile device development. It gave me a good jump start on this project. I've been working with mobile computing using C++ v1.52 for a long time, and don't expect that it will take all that long to get up to speed with this.

Thanks again for your help!

|||

Jack,

Instead of using the table adapter, try a parameterized insert. Here is the "pseudo code" to do this:

SqlCeCommand cmd = null
SqlCeParameter param = null

try

cmd = _mysqlceconnection.CreateCommand()
cmd.CommandText = "INSERT INTO tablename VALUES (@.Param1, @.Param2, @.Param3)"

param = New SqlCeParameter("@.Param1", SqlDbType.UniqueIdentifier) // set each parameter's type to match the column in the database
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param2", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param3", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)

cmd.Prepare() // this allows SQL CE to predetermine an execution plan for the insert and cache it

// note you do this one time and it is outside the insertion loop below

While ' loop through your input file line by line, one row per line

cmd.Parameters("@.Param1").Value = firstColumnValueInYourInputFile

cmd.Parameters("@.Param2").Value = secondColumnValueInYourInputFile

cmd.Parameters("@.Param3").Value = thirdColumnValueInYourInputFIle

etc until you have set all the params in the INSERT statement

cmd.ExecuteNonQuery()

End While

I recommend that anytime you are inserting or updating data in SQL Server you use parameterized SQL - the first time your data contains an apostrophe, a comma, an ampersand, etc, you'll see why.

-Darren

Bulk Insert of multiple, associated tables

Hi, hope this is the right place for me to post this, but I couldn't find a
more appropriate group.
In a C# app, I have a large set of structured data that will map to multiple
tables, associated with foreign keys, similar in structure to:
The xxxID columns are Identity columns
TABLE Item (ItemID )
TABLE Exchange ( ExchangeID )
TABLE Batch( BatchID, fkExchange )
TABLE Group( GroupID, fkBatch )
TABLE Transaction ( TransactionID, fkGroup )
TABLE ItemToTransactionVector ( fkItem, fkTransaction )
'Item' rows already exist, and I'm creating the rest in my app to be added
to the database en-mass. Exchanges have a single Batch;Batches have multipl
e
Groups; Groups have multiple Transactions, etc.
For a single table, I would use the ADODB SqlBulkCopy class, unfortunately,
I need to set the foreign keys between the tables.
At the moment, I have an initial implementation that uses individual
inserts, but this is way too slow, even wrapping them inside a transaction.
My thoughts after a little research were:
-Create matching temporary tables, or permanent staging tables for each of
those to be imported.
-Preset the row data with 'fake' foreign keys in my application, to maintain
the relationship between the rows in different tables.
-Process the data somehow from the temporary tables into the real tables
Is this a ridiculous scheme? How else can I insert all that data and
maintain all the relationships - and do it fast?
Thanks! Ross"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:DE44B64C-5790-4D15-A35F-E82ECA15FF9A@.microsoft.com...
> Hi, hope this is the right place for me to post this, but I couldn't find
> a
> more appropriate group.
> In a C# app, I have a large set of structured data that will map to
> multiple
> tables, associated with foreign keys, similar in structure to:
> The xxxID columns are Identity columns
> TABLE Item (ItemID )
> TABLE Exchange ( ExchangeID )
> TABLE Batch( BatchID, fkExchange )
> TABLE Group( GroupID, fkBatch )
> TABLE Transaction ( TransactionID, fkGroup )
> TABLE ItemToTransactionVector ( fkItem, fkTransaction )
> 'Item' rows already exist, and I'm creating the rest in my app to be added
> to the database en-mass. Exchanges have a single Batch;Batches have
> multiple
> Groups; Groups have multiple Transactions, etc.
> For a single table, I would use the ADODB SqlBulkCopy class,
> unfortunately,
> I need to set the foreign keys between the tables.
> At the moment, I have an initial implementation that uses individual
> inserts, but this is way too slow, even wrapping them inside a
> transaction.
> My thoughts after a little research were:
> -Create matching temporary tables, or permanent staging tables for each of
> those to be imported.
> -Preset the row data with 'fake' foreign keys in my application, to
> maintain
> the relationship between the rows in different tables.
> -Process the data somehow from the temporary tables into the real tables
> Is this a ridiculous scheme? How else can I insert all that data and
> maintain all the relationships - and do it fast?
> Thanks! Ross
I can't help you with the ADO bulk load part but here's an example of how
you can load the data into a table variable and then populate the related
tables. Depending on the size of the tables you may prefer to try a temp
table or a permanent one.
/* Sample tables */
CREATE TABLE T1 (x INT IDENTITY PRIMARY KEY, z VARCHAR(10) NOT NULL UNIQUE);
CREATE TABLE T2 (x INT NOT NULL REFERENCES T1 (x), z1 VARCHAR(10) NOT NULL,
PRIMARY KEY (x,z1));
/* temp table variable */
DECLARE @.t TABLE (z VARCHAR(10) NOT NULL, z1 VARCHAR(10) NOT NULL, PRIMARY
KEY (z,z1));
/* Insert the raw data */
INSERT INTO @.t (z,z1)
SELECT 'XX', 'ABC' UNION ALL
SELECT 'XXX', 'DEF' UNION ALL
SELECT 'XXX', 'GHI' ;
/* Populate the parent table */
INSERT INTO T1 (z)
SELECT DISTINCT z
FROM @.t;
/* Join in the foreign key and populate the referencing table */
INSERT INTO T2 (x,z1)
SELECT T1.x, T.z1
FROM @.t AS T
JOIN T1
ON T.z = T1.z;
/* Result */
SELECT * FROM T1;
SELECT * FROM T2;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for you time David. I'll look at adapting that and see how well it
works for my case.
Regards,
Ross
"David Portas" wrote:

> "Ross" <Ross@.discussions.microsoft.com> wrote in message
...
> I can't help you with the ADO bulk load part but here's an example of how
> you can load the data into a table variable and then populate the related
> tables. Depending on the size of the tables you may prefer to try a temp
> table or a permanent one.
>
...

> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>

Bulk insert from text file in to multiple table

Hi,
I have a requirement, i need to bulk insert the data form the text file.
we need to insert firt 2 values from text file and insert into first
table.
then retrive the primary key value of inserted values then with this
value as Forign key insert next 4 value from the text file.
is there any way we can do it in bulk insert.
thanks,
prabhudeva.K
*** Sent via Developersdex http://www.examnotes.net ***> then retrive the primary key value of inserted values then with this
> value as Forign key insert next 4 value from the text file.
> is there any way we can do it in bulk insert.
No, you can't retrieve data with a bulk insert technique. This is one of
the downsides in using IDENTITY as a surrogate key.
If you must have a surrogate key rather than a natural one, you could use
uniqueidentifier instead and assign the GUID value in application code
before the bulk insert. That way, you'll know the PK value for the related
inserts. The downside is that the storage size is larger and the assigned
values will be basically random, which can reduce insert performance with
large tables. You can address the random issue by using
UuidCreateSequential instead of UuidCreate in app code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Prabhudeva Kenganavara" <rekhaprabhudeva@.gmail.com> wrote in message
news:uhM6rYflGHA.2180@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a requirement, i need to bulk insert the data form the text file.
> we need to insert firt 2 values from text file and insert into first
> table.
> then retrive the primary key value of inserted values then with this
> value as Forign key insert next 4 value from the text file.
> is there any way we can do it in bulk insert.
> thanks,
> prabhudeva.K
> *** Sent via Developersdex http://www.examnotes.net ***

Bulk Insert from CSV - trouble with .FMT

I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS

I have tried '","' (single quote doublequote comma) to no avail.

I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.

Does anyone know how to resolve this?

You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know. |||Hi.
I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer

Bulk Insert from CSV - trouble with .FMT

I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS

I have tried '","' (single quote doublequote comma) to no avail.

I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.

Does anyone know how to resolve this?

You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know. |||Hi.
I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer

Building multiple "temp" tables in SQL....

Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that would
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Mark
Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.com/jg_derived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>
|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:

> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exact
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>
|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place?
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>

Building multiple "temp" tables in SQL....

Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that woul
d
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.c...ived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.c...ived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:

> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exa
ct
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>
>

Building multiple "temp" tables in SQL....

Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that would
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.com/jg_derived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
--
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.com/jg_derived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> Hello,
>> Using SQL 2000 and I want to write a query that creates a different
>> "temp"
>> table every time the query is run so that more than one user cannot be
>> accessing the same temp table at the same time.
>> I see two possible ways of doing this.
>> 1.) My app can take the user ID as an input field, and I suppose that I
>> could use this in the query (as a variable) to generate a separate "temp
>> "
>> table for each user based on their ID.
>> For example, I'm Joe and my user ID is 1.
>> In the app I select my user ID as an input field and I click a button.
>> The
>> button runs the query which needs to take that User ID and create a temp
>> table with the ID as part of the table name so that it is different for
>> each
>> user.
>> 2.) If I can just tell the query to create a differentiated "temp" table
>> every time it is run (say with an increment function or something) that
>> would
>> work as well.
>> My goal is simply to make sure that when the query is run that not more
>> than
>> one person is accessing the same temp table at the same time.
>> The table is then truncated or dropped by another process.
>> Any idea if of how this can be done?
>> Thanks much,
>> Mark
>>
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:
> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exact
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> > hi
> >
> > may be you should spend some time to understand what temp tables are,
> > read BOL and
> >
> > http://www.sqlteam.com/article/temporary-tables
> > http://www.sql-server-performance.com/jg_derived_tables.asp
> >
> >
> >
> > regards
> > VT
> > Knowledge is power, share it...
> > http://oneplace4sql.blogspot.com/
> >
> >
> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> >> Hello,
> >>
> >> Using SQL 2000 and I want to write a query that creates a different
> >> "temp"
> >> table every time the query is run so that more than one user cannot be
> >> accessing the same temp table at the same time.
> >>
> >> I see two possible ways of doing this.
> >>
> >> 1.) My app can take the user ID as an input field, and I suppose that I
> >> could use this in the query (as a variable) to generate a separate "temp
> >> "
> >> table for each user based on their ID.
> >>
> >> For example, I'm Joe and my user ID is 1.
> >>
> >> In the app I select my user ID as an input field and I click a button.
> >> The
> >> button runs the query which needs to take that User ID and create a temp
> >> table with the ID as part of the table name so that it is different for
> >> each
> >> user.
> >>
> >> 2.) If I can just tell the query to create a differentiated "temp" table
> >> every time it is run (say with an increment function or something) that
> >> would
> >> work as well.
> >>
> >> My goal is simply to make sure that when the query is run that not more
> >> than
> >> one person is accessing the same temp table at the same time.
> >>
> >> The table is then truncated or dropped by another process.
> >>
> >> Any idea if of how this can be done?
> >>
> >> Thanks much,
> >>
> >> Mark
> >>
> >>
> >
> >
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
--
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>> Key point: temp tables cannot be access by anyone outside of the current
>> connection (unless they are global temp tables). Thus you can use the
>> exact
>> same name (such as #tmp) for every execution and have no worries that
>> they
>> will step on each other's data.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "vt" <vinu.t.1976@.gmail.com> wrote in message
>> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> > hi
>> >
>> > may be you should spend some time to understand what temp tables are,
>> > read BOL and
>> >
>> > http://www.sqlteam.com/article/temporary-tables
>> > http://www.sql-server-performance.com/jg_derived_tables.asp
>> >
>> >
>> >
>> > regards
>> > VT
>> > Knowledge is power, share it...
>> > http://oneplace4sql.blogspot.com/
>> >
>> >
>> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> >> Hello,
>> >>
>> >> Using SQL 2000 and I want to write a query that creates a different
>> >> "temp"
>> >> table every time the query is run so that more than one user cannot be
>> >> accessing the same temp table at the same time.
>> >>
>> >> I see two possible ways of doing this.
>> >>
>> >> 1.) My app can take the user ID as an input field, and I suppose that
>> >> I
>> >> could use this in the query (as a variable) to generate a separate
>> >> "temp
>> >> "
>> >> table for each user based on their ID.
>> >>
>> >> For example, I'm Joe and my user ID is 1.
>> >>
>> >> In the app I select my user ID as an input field and I click a button.
>> >> The
>> >> button runs the query which needs to take that User ID and create a
>> >> temp
>> >> table with the ID as part of the table name so that it is different
>> >> for
>> >> each
>> >> user.
>> >>
>> >> 2.) If I can just tell the query to create a differentiated "temp"
>> >> table
>> >> every time it is run (say with an increment function or something)
>> >> that
>> >> would
>> >> work as well.
>> >>
>> >> My goal is simply to make sure that when the query is run that not
>> >> more
>> >> than
>> >> one person is accessing the same temp table at the same time.
>> >>
>> >> The table is then truncated or dropped by another process.
>> >>
>> >> Any idea if of how this can be done?
>> >>
>> >> Thanks much,
>> >>
>> >> Mark
>> >>
>> >>
>> >
>> >
>>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>> Key point: temp tables cannot be access by anyone outside of the current
>> connection (unless they are global temp tables). Thus you can use the
>> exact
>> same name (such as #tmp) for every execution and have no worries that
>> they
>> will step on each other's data.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "vt" <vinu.t.1976@.gmail.com> wrote in message
>> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> > hi
>> >
>> > may be you should spend some time to understand what temp tables are,
>> > read BOL and
>> >
>> > http://www.sqlteam.com/article/temporary-tables
>> > http://www.sql-server-performance.com/jg_derived_tables.asp
>> >
>> >
>> >
>> > regards
>> > VT
>> > Knowledge is power, share it...
>> > http://oneplace4sql.blogspot.com/
>> >
>> >
>> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> >> Hello,
>> >>
>> >> Using SQL 2000 and I want to write a query that creates a different
>> >> "temp"
>> >> table every time the query is run so that more than one user cannot be
>> >> accessing the same temp table at the same time.
>> >>
>> >> I see two possible ways of doing this.
>> >>
>> >> 1.) My app can take the user ID as an input field, and I suppose that
>> >> I
>> >> could use this in the query (as a variable) to generate a separate
>> >> "temp
>> >> "
>> >> table for each user based on their ID.
>> >>
>> >> For example, I'm Joe and my user ID is 1.
>> >>
>> >> In the app I select my user ID as an input field and I click a button.
>> >> The
>> >> button runs the query which needs to take that User ID and create a
>> >> temp
>> >> table with the ID as part of the table name so that it is different
>> >> for
>> >> each
>> >> user.
>> >>
>> >> 2.) If I can just tell the query to create a differentiated "temp"
>> >> table
>> >> every time it is run (say with an increment function or something)
>> >> that
>> >> would
>> >> work as well.
>> >>
>> >> My goal is simply to make sure that when the query is run that not
>> >> more
>> >> than
>> >> one person is accessing the same temp table at the same time.
>> >>
>> >> The table is then truncated or dropped by another process.
>> >>
>> >> Any idea if of how this can be done?
>> >>
>> >> Thanks much,
>> >>
>> >> Mark
>> >>
>> >>
>> >
>> >
>>
>

Building Lists

I have a need to build a list of values that get reported on a line from
multiple records. I've tried the following code:
Dim myList as String
Function AddList(myVal as String) as String
If myList <> "" Then
myList = myList + ", "
End If
myList = myList + myVal
Return myList
End Function
Function ClearList() As String
myList = ""
Return myList
End Function
Function GetList() as String
Return myList
End Function
I put ClearList in the group header, BuildList in the details section, and
GetList in the Group footer. When I run the report, the header is blank, the
detail section I see the string building, but in the footer I get a blank.
Is this an evaluation time issue and is there a way to alter that? Or is
there a better solution in RS?I can solve this in SQL Server using a UDF but am still looking for a
Reporting Services Solution.
UDF:
CREATE FUNCTION SubList (@.valueId as INT)
RETURNS varchar(500)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @.list varchar(500)
SELECT @.list = ISNULL(@.list +', ','') + mySubValue FROM dbo.SubValues WHERE
valueId=@.valueId
RETURN @.list
END
Usage:
SELECT valueId, myValue, dbo.SubList(valueId) AS myList
FROM [Values]

Building cube with more than one datasources

How to build a cube with multiple datasources in case we have two separate databases? Is it possible to a build a cube with more than one dataview or datasources ?Yes its possible.

You could create multiple datasources and then add the tables to one or more datasourceviews.
You cold then built a cube based on this scenario.

Hannes

Building an application on multiple data sources

ASP.NET on SQL Server

I've been asked to quote for developing a system to expose data on a
web application. Most of the data will come from SQL Server DBs
located on a single box. However, some of the data will be sourced
from ORACLE which is located on a different box. It may be necessary
to create VIEWS and Stored Procedures joining these DBs

Does anyone have any pointers, clues, hints, tips or pitfalls that I
might consider while making my proposal? What sort of extra
contingency should I allow for the connection to ORACLE? Should I do
all the data retrieval on the DB server, or should I do it on the Web
server?

Any thoughts would be appreciated.

Thanks

William BalmerWilliam wrote:

Quote:

Originally Posted by

ASP.NET on SQL Server
>
I've been asked to quote for developing a system to expose data on a
web application. Most of the data will come from SQL Server DBs
located on a single box. However, some of the data will be sourced
from ORACLE which is located on a different box. It may be necessary
to create VIEWS and Stored Procedures joining these DBs
>
Does anyone have any pointers, clues, hints, tips or pitfalls that I
might consider while making my proposal? What sort of extra
contingency should I allow for the connection to ORACLE? Should I do
all the data retrieval on the DB server, or should I do it on the Web
server?

Look into the Linked Server features of SQL server. You can link to the
Oracle db & run queries against the Oracle db thru SQL server. This may
reduce the maintenance - you'll only be writing in SQL Server syntax, or
calling SQL Server stored procedures that query the Oracle db.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)|||"Linked Server" can be one way to do this. However general network
latency and throughput restrictions due to bandwidth limitations or
network traffic, etc. can completely kill joins across the wire.

Also, be prepared to write views on the Oracle box to convert Oracle
data types to be more compatible with Sql Servers needs even using
linked servers.

Another option to consider it to "replicate" the data from the Oracle
box onto a Sql Server box, avoiding the need for joins across the wire.
This is the option I had to take to get acceptable performance for one
job I did.

William wrote:

Quote:

Originally Posted by

ASP.NET on SQL Server
>
I've been asked to quote for developing a system to expose data on a
web application. Most of the data will come from SQL Server DBs
located on a single box. However, some of the data will be sourced
from ORACLE which is located on a different box. It may be necessary
to create VIEWS and Stored Procedures joining these DBs
>
Does anyone have any pointers, clues, hints, tips or pitfalls that I
might consider while making my proposal? What sort of extra
contingency should I allow for the connection to ORACLE? Should I do
all the data retrieval on the DB server, or should I do it on the Web
server?
>
Any thoughts would be appreciated.
>
Thanks
>
William Balmer

build a query which access to multiple databases

Hi I don't know how to build a query which access to multiple databases.
Example:
On the same server I have DB1 and DB2
I want to SELECT TABLE FROM DB1 INTO CURSOR AND THEN I WANT TO MODIFY A TABLE ON DB2
HELP!!!you use full name when accessing tables, for example:

SELECT * FROM DB1.dbo.Table1

DELETE FROM DB2.dbo.Table2