Bulk Insert fails

I am using SQL Server 2005 Management Studio Express.I am trying to insert records in bulk into "students" table.

Students:
uniqueid varchar(9) (primary Key)
lname varchar(35)
fname varchar(35)

My problem is when one record from the bulk record already exists in Students table all the bulk insert fails. I dont want that. I want rest of the records to be inserted smoothly with no error.

How can I do it....Is there any disadvantage if i do this?

You could bulk insert into a staging table and then do the logic to insert only non-duplicated keys. I rarely (if ever) insert directly into a "master table". Once data is in the staging table you can do a simple insert.

Insert into Students
Select uniqueid, lname, fname, ....
from staging_Students
where uniqueid not in (select uniqueid from Students)

Drop the staging table after you are done, or leave it there to determine why you are getting duplicated keys from the insert file...

|||

I am using staging table and inserting using not in condition...but i am trying to see if there is a way around...the reason is bc i think the performance of query will come down if i do again select....will the performance go down?students table will have 60000 records...

|||

You could also use Openrowset with the BULK option (see books online for details). You would have to define a format file to use for the BULK option.

Insert into Students
Select uniqueid, lname, fname, ....
From Openrowset(BULK bulk parameters) as A
Where uniqueid not in (select uniqueid from Students)

|||

Yes, the performance depends on the number of rows in the main table and the inserted rwos. But another option is to remove the primary key on the uniqueid column and create an unique index with the ignore_dup_key option ON. This will result in the duplicate rows from being ignored during BULK INSERT. This will eliminate the additional join using staging table approach. In any case, you should use the staging table if you have more complex data cleaning tasks to perform.

So do the following:

1. Drop primary key on uniqueid column

2. Add unique index like:

create unique index ix_uq_uniqueid on Students(uniqueid) with ignore_dup_key = on

3. Now, bulk insert as before and the duplicate rows will be ignored automatically.

|||

umachandar...students table can have 60000 records. At a time I can insert 300 records into that. This is my scenario...can you tell which approach is better to follow( using ignore_dup_key or use not in condition to check duplicates). Do I need staging table if i user ignore_dup_key option.? I am having clustered index on uniqueid column in students table rite now.

Scenario:

60 users will be using my application. each user can upload a .csv file with 300 records at maximum.

This is what i am doing rite now....when the user uploads a .csv file from front end the .net application will invoke sqlbulkcopy class built into .net 2.0 and does a bulk insert ot 300 records into staging table. After that i invoke a stored procedure which puts the data into 3 different tables. so my procedure will have 3 insert statements(with not in conditions). After successful insertion i delete the data from the staging table for that user. the number of users and records in students table can grow up further in furture...

or should I user the ignore_dup_key option and avoid the staging table?

Thanks...

Built database with SQL Server Management Studio Express, how can I quikly add test data?

I've built my SQL Server Express database with SQL Serevr Management Studio Express, and now I want to enter some seed data to assist in building tha app around it. I cannot find an option to manage the data in SQL SMSX, like I used to with Enterprise Manager.

I don't want to have to write an app just to get test data in. Seems like this should be a common need. Am I missing something obvious here? Can't find any reference to this in a search of the forums.

Please help.

Hi, did you see 'New Query' button in Management Studio? Click it, and a editor window will appear, just like in Query Analyzer.

Or you can press F8 to open 'Object Explorer' (under View menu), then you can manipulate database objects like you do in Enterprise Manager.

Building a report

I have been "Thrown to the wolves" and need to learn Visual Studio and Reporting Services thru the scholl of hard knocks. I have had about 5 years a self teaching on Microsoft Access and 2 months of SQL Server. Not by any means a pro but I'm learning.

I put my SQL in the Data tab. I go to layout. I have made a couple of reports using the table and subreport in the toolbox. However when I try to drag and drop the fields into the body without a table all that previews is the first field.

When I drop the field in the body it comes out=First(Fields!fldname.Value) or=Sum(Fields!fldname2.Value)

I'd like to get this a continuous report(form). I hope I'm explaining this well enough. I'm going top Barnes and Noble today to get a book but in the meantime does anyone understand what I'm looking for?

I tried finding it on BOL but it didn't help me that much.

Hi

you can also use List to place labels inside. List is similar to Table(Grid) as you can place fields inside, but list does not force you to have table layout.

-yuriy

|||

Hi, I am not sure what you are trying to ask, if you are bugged that when you drag/drop a field it comes like =First(Fields!fldname.Value) instead of

=Fields!fldname.Value, then you can edit this directly or right click in textbox ot table cell and go to Expressions and there you can create an expression using different functions,variables, parameters and fields.

I apologise if I am tootally off the track.

|||

No apologise necessary. I can't explain what I'm looking for well.

I grab a field from my fieldlist and drop it in the body. I go to preview and all that comes up is the first record in my result set. How do I make it a continous report so I have the first record, the last record and everything in between.

I'm able to do this if I create a table in the body and drop the fields in the details portion of that table. However that is too structured for some of the reports I'm looking for.

|||

Drag LIST control to your report first. Configure it to repeat records from your source. Then drag fields into the LIST control.

|||

Yes,

That works. Well I haven't tried the configure part yet but I create a list, drag the field in the list and BAM there it is. Great answer thank you.

|||

You can only use the data regions available to build reports, and they are sufficient to build most of the reports, go through the report design basics(http://msdn2.microsoft.com/en-us/library/ms159253.aspx) and Data regions available in reporting services and you should be able to design more complicated reports with a combination of these data regions(http://msdn2.microsoft.com/en-us/library/ms157134.aspx) .

Still if you have some problem designing the report you can post your specific requirement and we will try to help.

Building a Flat File Connector Programmatically

Hi.
After much messing around with the BI studio I've decided that the best way for me to build my FFC is by doing it in code. I've got over 400 columns to define and we already have the position and type information held elsewhere, so it kinda makes sense doing this programmatically.
I've read the "Adding Connections to a Package" article in BOL but it doesn't help with the problem I've got. Basically, all I want to do is add columns to a connector but I have no idea if I'm going about this the right way.
Here's an example of how I'm creating the connection manager and getting access to the columns.

using System;
using System.IO;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts;
using Microsoft.SqlServer.Dts.Runtime;
static void Main(string[] args) {
Package package = new Package();
ConnectionManager cm = package.Connections.Add("FLATFILE");
object comColumns = cm.Properties["Columns"];.GetValue(cm);
}

The variable comColumns returns a IDTSConnectionManagerFlatFileColumns90 COM interface which allows me to add columns using the IDTSConnectionManagerFlatFileColumn90 interface. However, both of these interfaces are infrastructure use only and the IDTSConnectionManagerFlatFileColumn90 interface doesnt contain a name or ID field (like the FlatFileColumn class does) and I will need to complete these.
It actually feels like I should be using the ConnectionManagerFlatFileClass and FlatFileColumn classes but I can't see any way of using them.

Any help would be very much appreciated.
Thanks
Steve

Hi Steve,

here is a little sample that might help you. It is setting one delimited flat file column. Assuming the parameters on the right side are available.

IDTSConnectionManagerFlatFileColumn90 column = columns.Add();

column.DataPrecision = precision;
column.DataScale = scale;
column.DataType = dataType;
column.MaximumWidth = size;
column.ColumnDelimiter = delimiter;
column.ColumnType = "Delimited";
column.ColumnWidth = 0;
column.TextQualified = true;

// Here is how you set the name, do not worry about the ID.
IDTSName90 name = column as IDTSName90;
name.Name = columnName;

You know that some other properties (like ConnectionString, Format, TextQualifier, etc) on the connection have to be set as well, right?

Thanks,

Build fails but no errors are returned

hi,

I moved my ssis solution from on dev machine to another.

When building the solution, visual studio keeps saying that the build failed but no errors are returned which is not really helpful...

any clues would be appreciated.

thanks

I don't believe you need to "build" anything.|||

well, the solution is built before starting the debugger process...

anyway, I've sorted my problem... errors where not displayed in the error list but I've found one returned in the output window.

It was complaining that it could not load the project files (note that I had no errors while loading the solution in VS).

I've deleted the .suo and other user files from the solution and it now works... did not realise that project file paths informations could be in the user files... I was expecting this information to be specific to solution and project files ...

|||I set my projects to never build under Tools-Options. Never had a problem.