Best solution

Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.

ThanksNope, it's not making sense :-) Can you provide a small example with data to illustrate what you are trying to do?

Terri|||If I understand correctly, can you do several UNIONs and get them in turn?

Table1 join table 2 on col2 UNION
Table1 join table 2 on col3 UNION
Table1 join table 2 on col4 UNION
etc.

What it sounds like is that you should have a third table that contains a record for each possible combination of keys between table1 and table2. It sounds correcting the database structure is the best bet if you are able to do that.|||okay let me try :)

Let say I have a row that consist of the following:

TABLE 1:
key|ele1|ele2|ele3|ele4|ele5
1 6 2 5 null null

key column contains the rowID

ele1 - ele5 columns contain row IDs from the same table. ele1 is not nullable but the rest is nullable. I think if I use JOINS I will get an "ambigious error."

Table 2 ( ele ):
key|name |value
1 | "first" | 1
2 | "second" | 2
3 | "third" | 3 and so on.|||You should be able to accomplish what you need using JOINs with aliases.

SELECT
 table1.key,
 table2key.name,
 table2key.value,
 table1.ele1,
 table2ele1.name,
 table2ele1.value,
 table1.ele2,
 table2ele2.name,
 table2ele2.value,
 table1.ele3,
 table2ele3.name,
 table2ele3.value,
 table1.ele4,
 table2ele4.name,
 table2ele4.value,
 table1.ele5,
 table2ele5.name,
 table2ele5.value
FROM
 table1
LEFT OUTER JOIN
 table2 AS table2key ON table1.key = table2key.key
LEFT OUTER JOIN
 table2 AS table2ele1 ON table1.ele1 = table2ele1.key
LEFT OUTER JOIN
 table2 AS table2ele2 ON table1.ele2 = table2ele2.key
LEFT OUTER JOIN
 table2 AS table2ele3 ON table1.ele3 = table2ele3.key
LEFT OUTER JOIN
 table2 AS table2ele4 ON table1.ele4 = table2ele4.key
LEFT OUTER JOIN
 table2 AS table2ele5 ON table1.ele5 = table2ele5.key

Terri|||Thanks so much for all your help Terri!

Best Real Datatype

Hi all,
I have several columns which store currency values (typically up to 4
integer values, plus two decimal places)
Using Enterprise Manager I can set a column as decimal type, but it doesn't
allow me to specify precision) and any values show as the integer amount plu
s
.00 (ie 123.45 shows as 123.00). I converted these fields to money, but
several stored procedures showed a slow-down.
What is the most efficient datatype for storing very low precision real
numbers? What went wrong with my decimal datatype?
Many thanks in advance!Within EM look at the bottom half of the window. You will see a precision
and scale attribute there.
Keith Kratochvil
"GeorgeBR" <GeorgeBR@.discussions.microsoft.com> wrote in message
news:B8499051-76E7-4F2E-8650-971709333F6D@.microsoft.com...
> Hi all,
> I have several columns which store currency values (typically up to 4
> integer values, plus two decimal places)
> Using Enterprise Manager I can set a column as decimal type, but it
> doesn't
> allow me to specify precision) and any values show as the integer amount
> plus
> .00 (ie 123.45 shows as 123.00). I converted these fields to money, but
> several stored procedures showed a slow-down.
> What is the most efficient datatype for storing very low precision real
> numbers? What went wrong with my decimal datatype?
> Many thanks in advance!|||Also, don't use the money type. In addition to the performance issues you
are seeing, you will get rounding errors with money.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e$STqrafGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Within EM look at the bottom half of the window. You will see a precision
> and scale attribute there.
>
> --
> Keith Kratochvil
>
> "GeorgeBR" <GeorgeBR@.discussions.microsoft.com> wrote in message
> news:B8499051-76E7-4F2E-8650-971709333F6D@.microsoft.com...
>

Best practise/architecture question

I need to load a lot of Excel, CSV, ... etc. files. These files have hundreds of columns and I need to validate the data. Some are simple range type checking, some are more complex checking involve multiple columns.

There may have several hundreds of such rules. And I may need to let the program to automatically correct some invalid data in the future.

Where to implement it in SSIS?
Or just load the files without any checking (all type to text), and checking using T-SQL?

(BTW, I don't have biztalk server).

Thanks in advance.

Read more >> Options >>

SSIS has seevral components in the data flow that can help you in the data cleansing/transformation: Derived column, script tasks, etc; so if you have to meve the data from point a to point B; you could apply all the transformation rules as a part of that process.

Based on the litle information I got, I would try with first with SSIS.

|||

I will use SSIS problem. However, I don't want to "hard code" all these rules using SSIS component because

1. There are so many rules. And rules may need to be updated.

2. User will manage the rules. It's not possible to teach them to use VS.Net to update the SSIS package

Maybe a script component to call a C# assembly, which maintain parse rules in a, for example, XML file....

|||

We have a similar issue - we have about 6-8 flat file types, with 50-80 columns each and one-lots of rules for each column. The approach I'm taking is a hybrid. I'm first loading into a catch-all (all characters) staging database, and then using isdate, isnumeric, etc. in TSQL to then pull the data back out into SSIS to do some of the things that SSIS is good at. Where isdate & isnumeric fails on the extract, I'm putting a generic error value in using a CASE in my select (either -1, or '9999-12-31' for dates) so I can at least get the data cleaned before it heads back into SSIS & the production database. From there I'm doing my multiple column validation, or other "softer" error handling. My guess is you will not be able to give the users something to configure absolutely every validation. For us it's going to be a trade-off - I'm going to enforce the high-level/blatant validations up front and do things that aren't going to change often, while giving the user the ability to configure range validations, etc.

|||

Yes, I was thinking doing the same thing. However, it sounds it ruins the purpose of using SSIS as an ETL tool.

But it sounds not easy to use SSIS as a configurable ETL tool.

|||

ydbn wrote:

Yes, I was thinking doing the same thing. However, it sounds it ruins the purpose of using SSIS as an ETL tool.

But it sounds not easy to use SSIS as a configurable ETL tool.

I think your scenario represent the same challenge regardless the ETL tool you choose. Do you have an example of your ideal solution using a different tool? If you shared that with us, I am pretty sure we could help in getting something similar in SSIS terms.

BTW, what about creating a custom task that gets the rules from a table per every file type? You could store the transformations rules in a table where they can be easily updated.

There are more than one way to tackle this problem; but flexibility won't come for free; if you want something robust an reusable, get ready to spend some time in the design table.

sql

Best practice for handling XML schema hierarchies?

I have a number of tables with columns of xml datatype. Each of these columns are typed against a different XML schema collection. However, each of the XML schema collections contain a hierarchy of schema definitions - and the schemas towards the top of the hierarchy are used by a number of different XML schema collections. I want to define the schemas in such a way that if I need to change a schema towards the top of the hierarchy, I only need to change it in one place.

I understand that it is not possible to reference a schema in one collection from another - is my understanding correct? (If I am wrong, then please disregard the following)

If the schemas must be duplicated in each xml schema collection that needs them, then I am considering the following approach. Are there any better methods available?

- Create a 'reference' xml schema collection that contains all the schemas
- Create a table that relates a schema to all the collections that need it
- Write a stored proc that updates all the individual collections appropriately when the reference collection is updated

Using this method, I would anticipate updating the 'reference' schema and running the stored procedure at a quiet time

I could just use a single collection for everything - but although it would ensure that the contents of a column satisfied a schema - it wouldn't check that it satisfied the correct schema. I guess I could put separate validation on the column to ensure that the right contents had been added, but this seems to run counter to the whole idea of using the XML collections

Any thoughts?

You are correct that you cannot refer to schemas from other schema collections.

You approach of using a master schema collection sounds ok. Alternatively, you could use a special table that contains each one of the schemas in an XML datatype column. That way, you could even perform updates on the schemas programmatically, and you would preserve annotations and comments in the schema as an added bonus.

You then could still do the stored procs.

Note however, that you have to be careful with evolving your schemas in that they should only be gaining new elements and types. Otherwise the schema collection will disallow such updates since the cost of revalidation and potential validation failure of old data was too high for be done implicitly...

Best regards

Michael

|||Thanks Michael - will give it a go

Beginner Help

Very stupid question but I am pressed for time
I have a 200 mb file that has three columns of data.
Latitude, Longitude, and Altitude
I want to sort by altitude.
I have downloded the SQL Server and am trying to sort the data in Sql sever
Analyzer. There is no where to find instructions on how to input the sql
anywhere and I dont have enough time to understand sql server to figure out
how to do this.
Is there Access like function where you could visually do this?
If not , what is the easiest way of doing this in sql server?
please tell me how you would solve this problem.
Thanks in advance.Use Query Analyzer to run the query:
select
*
from
MyTable
order by
Altitude
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"t" <t@.discussions.microsoft.com> wrote in message
news:ACB53185-1A1A-4641-BA32-D04F369F34E2@.microsoft.com...
Very stupid question but I am pressed for time
I have a 200 mb file that has three columns of data.
Latitude, Longitude, and Altitude
I want to sort by altitude.
I have downloded the SQL Server and am trying to sort the data in Sql sever
Analyzer. There is no where to find instructions on how to input the sql
anywhere and I dont have enough time to understand sql server to figure out
how to do this.
Is there Access like function where you could visually do this?
If not , what is the easiest way of doing this in sql server?
please tell me how you would solve this problem.
Thanks in advance.|||My problem is where do I enter that.
i have opened the txt db file in query analyzer and can not figure out how
to implement it. My goal is to break up the db by altitude. what do people
who use this or other dabase use to accomplish this kind of request.
Is sql server too big for it? Problem is I can not do it in access or
excel(over 100mb).
Please advise
"Tom Moreau" wrote:
> Use Query Analyzer to run the query:
> select
> *
> from
> MyTable
> order by
> Altitude
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "t" <t@.discussions.microsoft.com> wrote in message
> news:ACB53185-1A1A-4641-BA32-D04F369F34E2@.microsoft.com...
> Very stupid question but I am pressed for time
> I have a 200 mb file that has three columns of data.
> Latitude, Longitude, and Altitude
> I want to sort by altitude.
> I have downloded the SQL Server and am trying to sort the data in Sql sever
> Analyzer. There is no where to find instructions on how to input the sql
> anywhere and I dont have enough time to understand sql server to figure out
> how to do this.
> Is there Access like function where you could visually do this?
> If not , what is the easiest way of doing this in sql server?
> please tell me how you would solve this problem.
> Thanks in advance.
>|||1. Create a Database using Enterprise manager.
2. Right Click on said new Database and Select All Tasks>Import Data
3. Select the datasource (excel? Textfile?)
4. Step through the wizard
5. Using Query analyser or indeed create a view run the code select * from
table order by Altitude
6. tada
"t" <t@.discussions.microsoft.com> wrote in message
news:ACB53185-1A1A-4641-BA32-D04F369F34E2@.microsoft.com...
> Very stupid question but I am pressed for time
> I have a 200 mb file that has three columns of data.
> Latitude, Longitude, and Altitude
> I want to sort by altitude.
> I have downloded the SQL Server and am trying to sort the data in Sql
sever
> Analyzer. There is no where to find instructions on how to input the sql
> anywhere and I dont have enough time to understand sql server to figure
out
> how to do this.
> Is there Access like function where you could visually do this?
> If not , what is the easiest way of doing this in sql server?
> please tell me how you would solve this problem.
> Thanks in advance.

Beginner Help

Very stupid question but I am pressed for time
I have a 200 mb file that has three columns of data.
Latitude, Longitude, and Altitude
I want to sort by altitude.
I have downloded the SQL Server and am trying to sort the data in Sql sever
Analyzer. There is no where to find instructions on how to input the sql
anywhere and I dont have enough time to understand sql server to figure out
how to do this.
Is there Access like function where you could visually do this?
If not , what is the easiest way of doing this in sql server?
please tell me how you would solve this problem.
Thanks in advance.
Use Query Analyzer to run the query:
select
*
from
MyTable
order by
Altitude
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"t" <t@.discussions.microsoft.com> wrote in message
news:ACB53185-1A1A-4641-BA32-D04F369F34E2@.microsoft.com...
Very stupid question but I am pressed for time
I have a 200 mb file that has three columns of data.
Latitude, Longitude, and Altitude
I want to sort by altitude.
I have downloded the SQL Server and am trying to sort the data in Sql sever
Analyzer. There is no where to find instructions on how to input the sql
anywhere and I dont have enough time to understand sql server to figure out
how to do this.
Is there Access like function where you could visually do this?
If not , what is the easiest way of doing this in sql server?
please tell me how you would solve this problem.
Thanks in advance.
|||My problem is where do I enter that.
i have opened the txt db file in query analyzer and can not figure out how
to implement it. My goal is to break up the db by altitude. what do people
who use this or other dabase use to accomplish this kind of request.
Is sql server too big for it? Problem is I can not do it in access or
excel(over 100mb).
Please advise
"Tom Moreau" wrote:

> Use Query Analyzer to run the query:
> select
> *
> from
> MyTable
> order by
> Altitude
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "t" <t@.discussions.microsoft.com> wrote in message
> news:ACB53185-1A1A-4641-BA32-D04F369F34E2@.microsoft.com...
> Very stupid question but I am pressed for time
> I have a 200 mb file that has three columns of data.
> Latitude, Longitude, and Altitude
> I want to sort by altitude.
> I have downloded the SQL Server and am trying to sort the data in Sql sever
> Analyzer. There is no where to find instructions on how to input the sql
> anywhere and I dont have enough time to understand sql server to figure out
> how to do this.
> Is there Access like function where you could visually do this?
> If not , what is the easiest way of doing this in sql server?
> please tell me how you would solve this problem.
> Thanks in advance.
>
|||1. Create a Database using Enterprise manager.
2. Right Click on said new Database and Select All Tasks>Import Data
3. Select the datasource (excel? Textfile?)
4. Step through the wizard
5. Using Query analyser or indeed create a view run the code select * from
table order by Altitude
6. tada
"t" <t@.discussions.microsoft.com> wrote in message
news:ACB53185-1A1A-4641-BA32-D04F369F34E2@.microsoft.com...
> Very stupid question but I am pressed for time
> I have a 200 mb file that has three columns of data.
> Latitude, Longitude, and Altitude
> I want to sort by altitude.
> I have downloded the SQL Server and am trying to sort the data in Sql
sever
> Analyzer. There is no where to find instructions on how to input the sql
> anywhere and I dont have enough time to understand sql server to figure
out
> how to do this.
> Is there Access like function where you could visually do this?
> If not , what is the easiest way of doing this in sql server?
> please tell me how you would solve this problem.
> Thanks in advance.

Beginner Help

Very stupid question but I am pressed for time
I have a 200 mb file that has three columns of data.
Latitude, Longitude, and Altitude
I want to sort by altitude.
I have downloded the SQL Server and am trying to sort the data in Sql sever
Analyzer. There is no where to find instructions on how to input the sql
anywhere and I dont have enough time to understand sql server to figure out
how to do this.
Is there Access like function where you could visually do this?
If not , what is the easiest way of doing this in sql server?
please tell me how you would solve this problem.
Thanks in advance.Use Query Analyzer to run the query:
select
*
from
MyTable
order by
Altitude
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"t" <t@.discussions.microsoft.com> wrote in message
news:ACB53185-1A1A-4641-BA32-D04F369F34E2@.microsoft.com...
Very stupid question but I am pressed for time
I have a 200 mb file that has three columns of data.
Latitude, Longitude, and Altitude
I want to sort by altitude.
I have downloded the SQL Server and am trying to sort the data in Sql sever
Analyzer. There is no where to find instructions on how to input the sql
anywhere and I dont have enough time to understand sql server to figure out
how to do this.
Is there Access like function where you could visually do this?
If not , what is the easiest way of doing this in sql server?
please tell me how you would solve this problem.
Thanks in advance.|||My problem is where do I enter that.
i have opened the txt db file in query analyzer and can not figure out how
to implement it. My goal is to break up the db by altitude. what do people
who use this or other dabase use to accomplish this kind of request.
Is sql server too big for it? Problem is I can not do it in access or
excel(over 100mb).
Please advise
"Tom Moreau" wrote:

> Use Query Analyzer to run the query:
> select
> *
> from
> MyTable
> order by
> Altitude
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "t" <t@.discussions.microsoft.com> wrote in message
> news:ACB53185-1A1A-4641-BA32-D04F369F34E2@.microsoft.com...
> Very stupid question but I am pressed for time
> I have a 200 mb file that has three columns of data.
> Latitude, Longitude, and Altitude
> I want to sort by altitude.
> I have downloded the SQL Server and am trying to sort the data in Sql seve
r
> Analyzer. There is no where to find instructions on how to input the sql
> anywhere and I dont have enough time to understand sql server to figure ou
t
> how to do this.
> Is there Access like function where you could visually do this?
> If not , what is the easiest way of doing this in sql server?
> please tell me how you would solve this problem.
> Thanks in advance.
>|||1. Create a Database using Enterprise manager.
2. Right Click on said new Database and Select All Tasks>Import Data
3. Select the datasource (excel? Textfile?)
4. Step through the wizard
5. Using Query analyser or indeed create a view run the code select * from
table order by Altitude
6. tada
"t" <t@.discussions.microsoft.com> wrote in message
news:ACB53185-1A1A-4641-BA32-D04F369F34E2@.microsoft.com...
> Very stupid question but I am pressed for time
> I have a 200 mb file that has three columns of data.
> Latitude, Longitude, and Altitude
> I want to sort by altitude.
> I have downloded the SQL Server and am trying to sort the data in Sql
sever
> Analyzer. There is no where to find instructions on how to input the sql
> anywhere and I dont have enough time to understand sql server to figure
out
> how to do this.
> Is there Access like function where you could visually do this?
> If not , what is the easiest way of doing this in sql server?
> please tell me how you would solve this problem.
> Thanks in advance.

Begining Queries

I am a novice SQL guy so you all take it easy on me.
I am trying to run a query and one of the columns of my has values that is
referenced in another table, i.e. for EmployedStatus I have a bunch a
numbers from aother table. I go to that table to see what the numbers mean
and there you go. What I would like to do is, say I am working with person
profile table and instead of showing the column "EmployedStatus" as a 63
(for example) for a record, I would like it to show what that 63 means from
the "desription" column of the table that the 63 is referenced to. I hope
that make sense and I would appreciate any help anyone can stand to stomach!
Thanks,
Aaron
It's ok Aaron, we all started out somewhere.
For reference, look in Books Online for the topic JOIN TABLES -its a complex set of topics and takes most of us a lot of practice to get it right.
Now about your problem. I'll create an example. (You can copy and paste this code into Query Analyzer and run it.)
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)
CREATE TABLE TableB
( Status int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status
--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
>I am a novice SQL guy so you all take it easy on me.
> I am trying to run a query and one of the columns of my has values that is
> referenced in another table, i.e. for EmployedStatus I have a bunch a
> numbers from aother table. I go to that table to see what the numbers mean
> and there you go. What I would like to do is, say I am working with person
> profile table and instead of showing the column "EmployedStatus" as a 63
> (for example) for a record, I would like it to show what that 63 means from
> the "desription" column of the table that the 63 is referenced to. I hope
> that make sense and I would appreciate any help anyone can stand to stomach!
> Thanks,
> Aaron
>
|||Thanks Arnie for the info, worked great.
If I could ask one more; How would I modify this code to to be able to
reference more than one field that uses the same table? For example to
continue with our example, I have a an EmployedStatusID that is referenced
by a number in an ID Table and it corresponding Description of Full-Time,
Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
in the same ID table with it's corresponding Description as Full-Time,
Half-Time, etc.. I tried to use the AND along with the Join and when I
added more than one, no data was displayed. If I run separate queries on
both, they show up correctly. Any suggestions?
Thanks again,
Aaron
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23JTR$oK$GHA.3584@.TK2MSFTNGP05.phx.gbl...
It's ok Aaron, we all started out somewhere.
For reference, look in Books Online for the topic JOIN TABLES -its a complex
set of topics and takes most of us a lot of practice to get it right.
Now about your problem. I'll create an example. (You can copy and paste this
code into Query Analyzer and run it.)
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)
CREATE TABLE TableB
( Status int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status
--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message
news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
>I am a novice SQL guy so you all take it easy on me.
> I am trying to run a query and one of the columns of my has values that is
> referenced in another table, i.e. for EmployedStatus I have a bunch a
> numbers from aother table. I go to that table to see what the numbers
> mean
> and there you go. What I would like to do is, say I am working with
> person
> profile table and instead of showing the column "EmployedStatus" as a 63
> (for example) for a record, I would like it to show what that 63 means
> from
> the "desription" column of the table that the 63 is referenced to. I hope
> that make sense and I would appreciate any help anyone can stand to
> stomach!
> Thanks,
> Aaron
>
|||Aaron,
As you noticed, trying to retrieve fields from a JOIN table where the JOIN criteria is different doesn't work.
So there are a couple of different ways to make this work. One involves using a JOIN with a second virtual copy of the table, and the other involves using a sub-SELECT.
First, the JOIN using a second copy of the same table, and then the sub-select.
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int,
StudentStatus int
)
CREATE TABLE Status
( StatusID int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63, 23 )
INSERT INTO Status VALUES ( 63, 'On Leave' )
INSERT INTO Status VALUES ( 21, 'Full Time' )
INSERT INTO Status VALUES ( 21, 'Half Time' )
INSERT INTO Status VALUES ( 23, 'Part Time' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = s1.Description,
StudentStatus = s2.Description
FROM PersonProfile p
JOIN Status s1
ON p.EmployedStatus = s1.StatusID
JOIN Status s2
ON p.StudentStatus = s2.StatusID
--Use a Sub-SELECT
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.EmployedStatus
),
StudentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.StudentStatus
)
FROM PersonProfile p
--Clean up
DROP TABLE PersonProfile
DROP TABLE Status
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message news:129A1BD8-33A4-40D8-8B41-BC3A33BE3AEC@.microsoft.com...[vbcol=seagreen]
> Thanks Arnie for the info, worked great.
> If I could ask one more; How would I modify this code to to be able to
> reference more than one field that uses the same table? For example to
> continue with our example, I have a an EmployedStatusID that is referenced
> by a number in an ID Table and it corresponding Description of Full-Time,
> Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
> in the same ID table with it's corresponding Description as Full-Time,
> Half-Time, etc.. I tried to use the AND along with the Join and when I
> added more than one, no data was displayed. If I run separate queries on
> both, they show up correctly. Any suggestions?
> Thanks again,
> Aaron
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23JTR$oK$GHA.3584@.TK2MSFTNGP05.phx.gbl...
> It's ok Aaron, we all started out somewhere.
> For reference, look in Books Online for the topic JOIN TABLES -its a complex
> set of topics and takes most of us a lot of practice to get it right.
> Now about your problem. I'll create an example. (You can copy and paste this
> code into Query Analyzer and run it.)
> SET NOCOUNT ON
> CREATE TABLE PersonProfile
> ( EmployeeID int IDENTITY,
> LastName varchar(20),
> FirstName varchar(20),
> EmployedStatus int
> )
> CREATE TABLE TableB
> ( Status int,
> Description varchar(50)
> )
> INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
> INSERT INTO TableB VALUES ( 63, 'On Leave' )
> --Join the two tables
> SELECT
> p.EmployeeID,
> p.LastName,
> p.FirstName,
> CurrentStatus = b.Description
> FROM PersonProfile p
> JOIN TableB b
> ON p.EmployedStatus = b.Status
> --Clean up
> DROP TABLE PersonProfile
> DROP TABLE TableB
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Aaron" <support@.pinetreeit.com> wrote in message
> news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...

BCP's /F switch

We have a text file in which the first row has four fields and it needs
to get BCP'ed into a table with four columns. All remaining rows are
much longer with 75 fields and get BCP'ed into a table with 75 columns.
I am doing this second BCP execution with the /F2 option to indicate
that the first row should be row 2. Yet when I run it, it skips row 2
(the first of the longer rows) and starts grabbing at row 3. I have a
feeling BCP gets over that first short row and starts grabbing
at the second longer row (really row 3).
Any thoughts about what I could do? Thanks much.BCP is certainly going to have problems with that. We tend to think
of BCP first breaking the data into lines, then the lines into fields.
That isn't what it does, however. BCP goes field by field, and what
we call the line terminator is really just the field terminator of the
last field on the line. When you tell it to skip the first line, it
actually skips the first 75 fields. This takes it well into the
second line, and the 75th field will include everything to the end of
the second line.
I would start by looking for some string in the first line that does
not appear in any of the other lines, or which appears in all the
other lines that does not appear in the first. If something like that
can be found it may be possible to pre-process the data into two files
using the DOS (well not really DOS, the command line that looks like
DOS to us old fogeys) command FIND, and redirection. Something like:
find "some string" input.txt > withstring.txt
find /V "some string" input.txt > withOUTstring.txt
Otherwise, I would write a very simple program to read the file and
write it out to two files as you need. Or perhaps see if DTS parses
lines on a line-then-field basis.
Roy Harvey
Beacon Falls, CT
On Mon, 22 May 2006 17:17:53 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:

>We have a text file in which the first row has four fields and it needs
>to get BCP'ed into a table with four columns. All remaining rows are
>much longer with 75 fields and get BCP'ed into a table with 75 columns.
>I am doing this second BCP execution with the /F2 option to indicate
>that the first row should be row 2. Yet when I run it, it skips row 2
>(the first of the longer rows) and starts grabbing at row 3. I have a
>feeling BCP gets over that first short row and starts grabbing
>at the second longer row (really row 3).
>Any thoughts about what I could do? Thanks much.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> We have a text file in which the first row has four fields and it needs
> to get BCP'ed into a table with four columns. All remaining rows are
> much longer with 75 fields and get BCP'ed into a table with 75 columns.
> I am doing this second BCP execution with the /F2 option to indicate
> that the first row should be row 2. Yet when I run it, it skips row 2
> (the first of the longer rows) and starts grabbing at row 3. I have a
> feeling BCP gets over that first short row and starts grabbing
> at the second longer row (really row 3).
BCP ? BCP never gets , but users of BCP often gets
over it!
I think the best description of BCP is that it reads a *binary* file.
The file may look like a text file to you and me, for BCP it's binary.
When BCP reads the file, it looks the format definition of the first
field as specified by the format file or command-line switches. When
it has found the end of that column, it goes for the next etc. The
row terminator is just the terminator for the last field.
When you say -F2, that is not the second line in the file. It's the second
record in the file according to the format specification. Given your
description, I can guess that will be line 3 in the file. That first
skipped record has an embedded newline in field 4, and some embedded
field terminators in field 75.
As for what you want to do, you will have to split the file. BCP
is not smart enough to handle two tables or two different formats.
Or, hm, maybe you can get away with a single file - but requires
quite some luck. Getting that single line into a table should be
simple. Define a format file for those four fields, and then specify
-L 1, to get only the first row.
Skipping that first line may be more problematic. If you can post a
sample, I might be able to come with something. But no promises.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?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="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many Thanks
On Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?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="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed
|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegro ups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?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="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many ThanksOn Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?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="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegroups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?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="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many ThanksOn Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?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="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegroups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx