Need to bulk insert quickly with .NET

Hi all, I hope this is the correct place for this post.
I need to write a service that will bulk insert 3 million+ records from a
fixed width formatted text file to an SQL Server table. When I started
importing these records I used Access to put the records into a table and
DTS to get the table into SQL server. This process took maybe 1/2 hour.
I then tried using .NET to import the data by grabbing the textfile into a
datatable in batches of 250 records and then updating to the SQL server
database with a DataAdaptor. This process took about 5 hours which is kind
of unacceptable.
Is there anyway to speed up this process? Can .NET access any bulk insert
functionality? I need to automate this process into a service and would
like to get it to run on the same order as the manual process, and I would
like to use a service programmed in .NET. If I could even call SQL to acess
bulk insert functionality on SQL Server that would be fine, but SQL Server
does not seem to support fixed width text files.ADO.NET doesn't have a built-in bulk insert capability. As I see it you have
several options to do this. But the best bet is to use the SQLCommand object
to call the T-SQL BULK INSERT statement. Other options include creating a
bcp format file and calling that from the .NET program. Likewise, you could
create a DTS package and call that use the .NET COM interop classes or you
could use DMO's BulkInsert object.
Michael O.
"Todd Burry" <tburry@.nospam.com> wrote in message
news:Xns94CAA6111D5C1mythstoddburrycom@.2
07.46.248.16...
> Hi all, I hope this is the correct place for this post.
> I need to write a service that will bulk insert 3 million+ records from a
> fixed width formatted text file to an SQL Server table. When I started
> importing these records I used Access to put the records into a table and
> DTS to get the table into SQL server. This process took maybe 1/2 hour.
> I then tried using .NET to import the data by grabbing the textfile into a
> datatable in batches of 250 records and then updating to the SQL server
> database with a DataAdaptor. This process took about 5 hours which is kind
> of unacceptable.
> Is there anyway to speed up this process? Can .NET access any bulk insert
> functionality? I need to automate this process into a service and would
> like to get it to run on the same order as the manual process, and I would
> like to use a service programmed in .NET. If I could even call SQL to
acess
> bulk insert functionality on SQL Server that would be fine, but SQL Server
> does not seem to support fixed width text files.|||Thanks for the help. I didn't realize that the BULK INSERT supports fixed
width files. That will work just fine.

Need to bulk insert quickly with .NET

Hi all, I hope this is the correct place for this post.
I need to write a service that will bulk insert 3 million+ records from a
fixed width formatted text file to an SQL Server table. When I started
importing these records I used Access to put the records into a table and
DTS to get the table into SQL server. This process took maybe 1/2 hour.
I then tried using .NET to import the data by grabbing the textfile into a
datatable in batches of 250 records and then updating to the SQL server
database with a DataAdaptor. This process took about 5 hours which is kind
of unacceptable.
Is there anyway to speed up this process? Can .NET access any bulk insert
functionality? I need to automate this process into a service and would
like to get it to run on the same order as the manual process, and I would
like to use a service programmed in .NET. If I could even call SQL to acess
bulk insert functionality on SQL Server that would be fine, but SQL Server
does not seem to support fixed width text files.
ADO.NET doesn't have a built-in bulk insert capability. As I see it you have
several options to do this. But the best bet is to use the SQLCommand object
to call the T-SQL BULK INSERT statement. Other options include creating a
bcp format file and calling that from the .NET program. Likewise, you could
create a DTS package and call that use the .NET COM interop classes or you
could use DMO's BulkInsert object.
Michael O.
"Todd Burry" <tburry@.nospam.com> wrote in message
news:Xns94CAA6111D5C1mythstoddburrycom@.207.46.248. 16...
> Hi all, I hope this is the correct place for this post.
> I need to write a service that will bulk insert 3 million+ records from a
> fixed width formatted text file to an SQL Server table. When I started
> importing these records I used Access to put the records into a table and
> DTS to get the table into SQL server. This process took maybe 1/2 hour.
> I then tried using .NET to import the data by grabbing the textfile into a
> datatable in batches of 250 records and then updating to the SQL server
> database with a DataAdaptor. This process took about 5 hours which is kind
> of unacceptable.
> Is there anyway to speed up this process? Can .NET access any bulk insert
> functionality? I need to automate this process into a service and would
> like to get it to run on the same order as the manual process, and I would
> like to use a service programmed in .NET. If I could even call SQL to
acess
> bulk insert functionality on SQL Server that would be fine, but SQL Server
> does not seem to support fixed width text files.
|||Thanks for the help. I didn't realize that the BULK INSERT supports fixed
width files. That will work just fine.

Need to build a query

Hey. It's probably a very simple query but I'm not at all good in writing
T-SQL. Anyways, My environment is SQL 2005.
I need to write a stored proc which returns 4 columns to a C# app. All the
data comes from one table only.
The first column will just have 'Name of months'
The second column will have a sum of amount
(-sum of amount in jan in first row,
-sum of amount in feb in row 2,
-sum of amount in feb in row 3,
-sum of amount in feb in row 4)
The third row will have a sum of won per month for this year(just like 2nd
column),
The fourth row will have a sum of Lost per month for this year(just like 2nd
column
I'm very new to SQL 2005.
Thank you.Before we can help you, please help us:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||It sounds like you are trying to get 4 different datasets returned as 4
distinct rows in a single query, simply so you can display them as one
dataset. While this is possible, it is not advisable.
Your best bet is to select whatever the core data is that you will be basing
your calculations on and then process it in your application.
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:5BE2F352-33C6-4A02-88B1-9EC75F843B96@.microsoft.com...
> Hey. It's probably a very simple query but I'm not at all good in writing
> T-SQL. Anyways, My environment is SQL 2005.
> I need to write a stored proc which returns 4 columns to a C# app. All the
> data comes from one table only.
> The first column will just have 'Name of months'
> The second column will have a sum of amount
> (-sum of amount in jan in first row,
> -sum of amount in feb in row 2,
> -sum of amount in feb in row 3,
> -sum of amount in feb in row 4)
> The third row will have a sum of won per month for this year(just like 2nd
> column),
> The fourth row will have a sum of Lost per month for this year(just like
2nd
> column
> I'm very new to SQL 2005.
> Thank you.|||Never mind my last post. In your other post you explained clearly what you
were trying to do. I misunderstood your goals entirely.
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:5BE2F352-33C6-4A02-88B1-9EC75F843B96@.microsoft.com...
> Hey. It's probably a very simple query but I'm not at all good in writing
> T-SQL. Anyways, My environment is SQL 2005.
> I need to write a stored proc which returns 4 columns to a C# app. All the
> data comes from one table only.
> The first column will just have 'Name of months'
> The second column will have a sum of amount
> (-sum of amount in jan in first row,
> -sum of amount in feb in row 2,
> -sum of amount in feb in row 3,
> -sum of amount in feb in row 4)
> The third row will have a sum of won per month for this year(just like 2nd
> column),
> The fourth row will have a sum of Lost per month for this year(just like
2nd
> column
> I'm very new to SQL 2005.
> Thank you.sql

Need to alter a DML Trigger

Hi

I'm trying to write a trigger to insert data into an archive file. I added a new trigger using database explorer, wrote the trigger and then saved it. The trigger has an error in it and I need to alter it. Can you tell me how to access the trigger ?

Many thanks

Chris

Hi,

Have u tried looking under the DataBase\Programmability\DataBaseTriggers .. your trigger will show under this node

Also see this video from Microsoft

SQL Server 2005 Video Presentation: Enterprise Data Management, DDL Triggers

Hope this will help

Satya

|||

Satya

Thanks for that. I don't usually use Management Studio Express, I just use VWD express. However, I looked under DataBase\Programmability\DatabaseTriggers and the trigger is not there. I've run the INSERT routine in my application and it is still failing because of the trigger I wrote. So I still have the problem of finding the triggert to correct. (I'm a bit puzzled by this, if you write a stored procedure using VWD express you can subsequently view it and alter it in the VWD express environment).

I would be grateful for any further suggestions.

Chris

|||

Well I've found the trigger under DataBase \ Tables \ 'tableName' \ Triggers.

Without any answers to my previous post, I guess I just have to assume that DML Triggers can be created using VWDexpress but not altered.

Anyway, Management Studio Express is quite cool.

Chris

|||

Hello Chris

If you need more info about trigger then you may read this article

http://www.sqlteam.com/item.asp?ItemID=3850

|||

Mehedi

Thanks for that.

Chris

Need to access SQL Server 2000 tables

Hello,

I am trying to write a java program that will connect to a SQL Server DB, run some queries, pull some data and try to find some trends in the data.

The problem is that I have a copy of the Database, but I don't have SQL Server. I have been given a backup copy of the database though (SQL Server 2000). I know that in addition to the JDBC driver for SQL Server 2000 I will need something else to be able to access the DB. They have stopped selling SQL Server 2000, also the software tends to be a little expensive.

Is there anything else that I can use that will allow me the functionality of being able to run queries and pull data from the DB using JDBC? Will installing MSDE allow me to do that? Or is there someother software that will allow me to do that? Or do I have no other option except installing SQL Server 2000?

Any help is greatly appreciated.

ThanksHi there,

Will you only be accessing the database for the purposes of reporting (i.e. you won't be changing data and then merging these changes into a master database from which the backup was taken)?

What I would recommend is that you don't use MSDE but you use SQL Server 2005 Express Edition. You can download it here:

Download SQL Server 2005 Express

NB. The download could be quite hefty as you might need to download the .NET Framework 2.0, SQL Server 2005 Express Edition database engine and the Management Studio.

You are able to restore SQL Server 2000 database backups to SQL Server 2005 so what you would do is:

1) Install SQL Server 2005 Express & Management Studio Express (see previous link)

2) Use Management Studio Express to restore the SQL Server 2000 backup (you can do this by creating a new database and then restoring your backup over the top)

3) Download and install the JDBC driver for SQL Server 2005 here:
Download JDBC Driver

4) Write your Java program as required

Well, OK, you don't have to use the JDBC driver as you could also use the JDBC-ODBC bridge which already comes with Java but I'd recommend you use the JDBC driver.

Hope that helps a bit, but sorry if it doesn't

|||Thanks a lot NateV,

I will be using the DB for reporting purposes. I won't be chaning any data on the backend ( no inserts or updates, just selects). I am going to try using SQL Server Express 05 and let you know if it worked.

Thanks Again

need stored procedure

Hi,
my table : tab1
id int
name varchar(50)
i want to write a stored procedure that returns the max(id) from the table.
how to create it.
thanks in advance
dnkHi
Use MAX() function
"DNKMCA" <dnk@.msn.com> wrote in message
news:exf%23s%23wnFHA.3564@.tk2msftngp13.phx.gbl...
> Hi,
> my table : tab1
> id int
> name varchar(50)
> i want to write a stored procedure that returns the max(id) from the
> table.
> how to create it.
> thanks in advance
> dnk
>sql

Need SQL Support

Dear friends

I am a new developer. I need some SQL Language examples to write some quaries.

Ex.

how you collect last record that relevent for the last record updated for a relevent employee in a record table which includes RecordNo and EmployeeNo, salses

Thanks

Any help is greatly apprciated

Amila

Hi,

e.g.

SELECT TOP 1
RecordNo, EmployeeNo, salses
From SomeTable
WHERE EmployeeNo = 'SomeValue'
ORDER BY TheColumnWheretheSalesDateisStored DESC

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Need SQL Help - Trim parentheses in column of data

Hi All, I am sure someone has done this before. Trying to write a SQL statement to UPDATE a column named Prod_Model. The table name is tbl_MASTER. Wanting to trim the parentheses out of the data and update back to the table. Any help on the SQL statement?

Trim or remove? Trim usually means only leading and/or trailing, and remove means to get rid of all.

Here is how to remove:

UPDATE Table

SET field=REPLACE(REPLACE(field,')',''),'(','')

WHERE field LIKE '%)%' OR field LIKE '%(%'

Here is how to trim:

UPDATE Table

SET field=REVERSE(SUBSTRING(REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1)),PATINDEX('%[^()]%',REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1))),LEN(REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1)))-PATINDEX('%[^()]%',REVERSE(SUBSTRING(field,PATINDEX('%[^()]%',field),LEN(field)-PATINDEX('%[^()]%',field)+1)))+1))

WHERE field LIKE '(%' or field LIKE ')%' or field LIKE '%)' or field LIKE '%('

There is a much shorter way using STUFF, REVERSE, and PATINDEX, but I thought of this way first.

|||

The remove method did the ticket. Thank you so much.

sql

Need MIN date

Hi
I would like to write query to get minimun data from the list of dates. Ie. i have a coumn in the table that conatins all dates from which i need to get minimum date. Please help me.You would like to

select the minimum date from a table

Not certain how to accomplish this

Please post the text book and the page that this question is from so I can better help you|||mark a cross for these dates in a calendar and the first crossed date is the minimum date.|||use min() function|||to use min() function correctly the datatype should be date, if the date is stored in a char field then use convert to make it of date datatype

Need help writing stored procedure involving dates

I am trying to write a stored procedure which would execute following logic:

- The stored procedure takes 2 optional parameters @.StartDate and @.EndDate

@.StartDate Datetime = null
@.EndDate Datetime = null

- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @.StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @.EndDate as the ED and MIN() of the Date field as SD

I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below

CREATE PROCEDURE SearchDocumentTable

@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null


AS
IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document

Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'

From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND LName LIKE ISNULL(@.LName,'%' + NULL)
AND ID LIKE ISNULL(@.ID,'%' + NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

GO

Any help would be appreciated
Thanks in advance :)I'm a little confused. What is your question ?

did you mean except for incoporating NULLS ?

Cheers,
-Kilka|||I am sorry wasnt thinking right when I posted that to the forum in the morning. Let me try to explain my problem with an example.
To make it simple lets say I have a simple table with 3 columns: FName, LName and Date

FName LName Date
Aab 02/05/2005
Abc Bb 02/06/2005
Aaaa Bbb
02/07/2005
Baaaa Bbb
Baaca 02/07/2005
Caa Bbbb 02/07/2005

As can be seen that FName, LName and Date columns accept NULL values.
Now I want to write a stored procedure which takes 4 parameters ( all of them are optional) @.FName, @.LName, @.StartDate and @.EndDate and do a search on this table.
I am having trouble handling these NULL fields.

When I execute the stored procedure, I should get results as follows:
- If user enters @.FName LIKE 'A%', sp should return only the rows where first name matches that format ( no null first name or null last names or null dates should be returned). Result should be first 3 rows with first names: Aab, Abc, Aaaa
- Similarly when searching for start date and end date, procedure should return only the rows which match the date criteria ignoring null first name and last name fields

I hope this example would prove helpful, since everytime I try to write a query I always end up getting rows with null fields.
Thanks again for your time :)|||Do you really have '%' + NULL in the code? I would think you just want '%'.

Are you getting any data back from this query when you have data that looks like your example?|||You guys are good sorry forgot to update the stored procedure.
No, '%' + NULL doesnot return any records. So I changed the stored procedure and use only '%' for all null parameters and adds OR FIELDNAME IS NULL at the end:

CREATE PROCEDURE SearchDocumentTable

@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null

AS

IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document

Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'

From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND (LName LIKE ISNULL(@.LName,'%') OR LName IS NULL)
AND (ID LIKE ISNULL(@.ID,'%') OR ID IS NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

GO

This is the stored procedure and as can be seen it will return null fields when I pass last name, ID or date as the parameter.
Any ideas how can I modify the stored procedure to avoid returning null fields.

Thank you again guys, I really appreciate your help|||Do you get the correct results if you take out OR LName IS NULL and
OR ID IS NULL and OR DateInputted IS NULL?

If this is not the case then please type out using the example data you used above the exact results you would like to see if all the input fields are NULL.|||First, decide on precedence. What if the user passes all three parameters?

Assuming the precedence is LastName, FirstName, Date:Declare @.t Table(Table_Pk int identity(1,1), FirstName varchar(3) Null, LastName varchar(3) Null, EndDate datetime Null)

Insert @.t (FirstName, LastName, EndDate)
Select 'Kim', 'Cat', GetDate()
Union
Select 'Pat', 'Dog', Null
Union
Select 'Ted', Null, GetDate()
Union
Select 'Jim', Null, Null
Union
Select Null, 'Fox', GetDate()
Union
Select Null, 'Fox', Null
Union
Select Null, Null, GetDate()
Union
Select Null, Null, Null

Select * From @.t

Declare @.FirstName varchar(3)
, @.LastName varchar(3)
, @.EndDate datetime

Select @.LastName = 'F'

Select *
From @.t
Where (@.LastName Is Not Null And LastName Like @.LastName + '%')
Or
(@.LastName Is Null And @.FirstName Is Not Null And FirstName Like @.FirstName + '%')
Or
(@.LastName Is Null And @.FirstName Is Null And EndDate > = Coalesce(@.EndDate, '01/01/1950') )|||FYI, if you test for the IF statements and, inside the stored procedure, call a different function for each test to return the final result set, you will have no recompiles. This will make the overall execution of the stored proc much faster when you get larger recordsets. You also might want to consider having the developers use a checkbox for exact match. That sounds stupid, but the users will learn to love it if this table gets extremely large.

Need help writing search query

I am not very familiar with the syntax of MS SQL and I am trying to write a stored procedure which would do a search and return matching records.
This is what I need to achieve:
for instance I create a form with 4 text fields
- First Name
- Last Name
- Employee ID
- Date

I am interested in writing a stored procedure that would run a select query based on the input in the text fields
e.g.
- if the user enters First Name and the Last Name (leaving Employee ID and Date fields blank) query should be something like
select * from Employee where FirstName like @.FirstName and LastName like @.LastName
- or if the user enters only the Employee ID stored procedure should run a query similar to
select * from Employee where EmployeeID like @.EmployeeIDselect *
from Employee
where (FirstName like @.FirstName and LastName like @.LastName)
or
(EmployeeID like @.EmployeeID)|||Thanks for the your time blindman .. that was absolutely brilliant, shows you know your SQL :cool:
This query would sure work for the example I posted, but I was just wondering if I can give user more flexibility and allow him to enter lets say [partial first name (some string with wildcard characters) and partial ID] or [partial first name, last name and ID] or some such wierd combination. The query should adapt to the input and return result accordingly.
What is the best way to go about doing this, again thanks for any help I can get.
Thank you.
Have a great day.|||Yes.

You will need to add more complexity to your WHERE clause to accomplish the logic you want.

You will also need to use the LIKE operator if you want to allow wildcards.

You will also need to expect this query not to run very fast, if you include lots of logical operators and LIKE comparisons in your criteria...|||Since this is in a stored procedure you might use dynamic SQL to create the query on the fly and then execute it.

eg.

create x @.empid int, ...

declare @.sql varchar(200)

set @.sql = 'select * from Employee where '

if empid is not null set @.sql = @.sql & 'EmployeeID =' & @.empid

... etc etc

exec (@.sql)

...|||Thanks ejustuss and blindman, you guys were a tremendous help.
Since I am not very comfortable writing stored procedures this was my solution to the problem .. nothing ingenious but hey as long it works thats all I care about :D.
So I wrote this insanely strict stored procedure which would except user to input all the parameters (first name, last name, Employee ID, Date ... everything) and wrote a query something similar to this:
select * from Employee where FirstName like @.FirstName and LastName like @.LastName and EmployeeID like @.EmployeeID and ....
Since my stored procedure is not giving any flexibility to the user, I added flexibility on the client side in the web form code by replacing all fields left blank by the user with wildcard '%'. So if a user wanted to search by employee's first name, he would just enter the first name leaving other field blanks. These blank fields will be replaced with % and passed to the stored procedure.|||Not sure how effecient this is :

SELECT * FROM Employee
WHERE EmployeeID LIKE ISNULL('%' + @.EmployeeID + '%', EmployeeID)
AND FirstName LIKE ISNULL('%' + @.FirstName + '%', FirstName)
AND LAstName LIKE ISNULL('%' + @.LastName + '%', LastName)
AND DATEDIFF(Day, ISNULL(@.Date, Date), Date) = 0;|||Hey afx thanks for posting your version of the solution. I am sorry but I really did not understand the code you posted. Though your input was definetely useful since using ISNULL is by far a way better more efficient option :cool:
This is how I would use ISNULL

SELECT * FROM Employee
WHERE EmployeeID LIKE ISNULL( @.EmployeeID, '%')
AND FirstName LIKE ISNULL( @.FirstName, '%')
AND LAstName LIKE ISNULL( @.LastName, '%')

Thank you again for the input and effort :) :)|||Hi all,

I need help on building query statement. below is my table called inventory.

idx fabidx coloridx qty isReserved
1 1 1 15 Y
2 1 2 20
3 1 1 10
4 1 1 25 Y
5 1 2 23 Y
6 1 3 26
This is the output that i'm expecting.
fabidx coloridx qty isReserved
1 1 50 2
1 2 43 1
1 3 26 0
I need to get all distinct fabidx and coloridx, i need to get the sum of the distinct fabidx and coloridx, and i need to get the count of "Y" in the isReserved column of the distinct fabidx and coloridx.

Please help. Thanks.|||A simple aggregation query. Look up aggregation function in Books Online, and then post this as a new thread if you still need help.

Need help writing query/ stored proc

I need to write query or stored proc that is going to be used to gen. a
report
This is a structure of my table
Id Date ItemType OrdersPlaced
1 08/21 1 100
1 08/21 2 500
1 08/21 3 200
1 08/21 4 150
2 ... ... ...
2 ... ... ...
3 ... ... ...
4 ... ... ...
4 ... ... ...
The report is going to take Id and Date as inputs and report generated
is in following format
Id: 1
Date: 08/21
ItemType 1 ItemType 2 ItemType 3 ItemType 4
100 500 200 150
This can definetely be done creating a temp table and writing to this
temp table thru multiple selects for each itemtype.
Is there any better way'
ThanksHave a look at the PIVOT function in SQL 2005 Books online.
Or this example on SQL 2000 will get you on the way
http://www.dandyman.net/sql/samples/pivottable.txt
__________________________________________________
Dandy Weyn - Dandyman (r)
MCSE-MCSA-MCDBA-MCDST-MCT Community Leader
MCTS SQL 2005- MCITP Database Administrator
Author of Sybex Exam Study Guide MCTS SQL 2005
http://www.dandyman.net
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
[Id], [Date]. The sideways generation you're looking for is a pivot table
type query, which can be done with a "monster" CASE statement in SQL 2000 or
the PIVOT operator in SQL 2005. Either method would require you to know,
and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
advance. If you don't know them in advance you can use dynamic SQL to work
it out.
Personally I'd recommend doing the SUM()/GROUP BY and transferring the
results to a front-end app and format it there.
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||Thanks y'all I am using SQL 2000 so pivot operator is not an option
open for me. I like the idea of using the "monster" CASE statement ...
and it almost returns the kind of report that I need ... just one snag
apart from the above fields there is one more calculated field that
references another table
e.g.
Table B
Id ItemType EstimatedTotal
1 1 200
1 2 800
1 3 200
1 4 200
The new field is a sum of EstimatedTotal for all ItemType and is used
for comparison to the actual total. The new report looks something like
this:
> > Id: 1
> > Date: 08/21
> > ItemType 1 ItemType 2 ItemType 3 ItemType 4 ActualTotal EstimatedTotal
> > 100 500 200 150 950 1400
going forward with the logic of using CASE and GROUP BY, can I get this
EstimatedTotal of 1400 returned by same query without using cursors or
temp tables
Any thoughts!!
Mike C# wrote:
> You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
> [Id], [Date]. The sideways generation you're looking for is a pivot table
> type query, which can be done with a "monster" CASE statement in SQL 2000 or
> the PIVOT operator in SQL 2005. Either method would require you to know,
> and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
> advance. If you don't know them in advance you can use dynamic SQL to work
> it out.
> Personally I'd recommend doing the SUM()/GROUP BY and transferring the
> results to a front-end app and format it there.
> "absoft" <arpit.00@.gmail.com> wrote in message
> news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
> >I need to write query or stored proc that is going to be used to gen. a
> > report
> > This is a structure of my table
> >
> > Id Date ItemType OrdersPlaced
> > 1 08/21 1 100
> > 1 08/21 2 500
> > 1 08/21 3 200
> > 1 08/21 4 150
> > 2 ... ... ...
> > 2 ... ... ...
> > 3 ... ... ...
> > 4 ... ... ...
> > 4 ... ... ...
> >
> > The report is going to take Id and Date as inputs and report generated
> > is in following format
> >
> > Id: 1
> > Date: 08/21
> > ItemType 1 ItemType 2 ItemType 3 ItemType 4
> > 100 500 200 150
> >
> > This can definetely be done creating a temp table and writing to this
> > temp table thru multiple selects for each itemtype.
> > Is there any better way'
> >
> > Thanks
> >

Need help writing query/ stored proc

I need to write query or stored proc that is going to be used to gen. a
report
This is a structure of my table
Id Date ItemType OrdersPlaced
1 08/21 1 100
1 08/21 2 500
1 08/21 3 200
1 08/21 4 150
2 ... ... ...
2 ... ... ...
3 ... ... ...
4 ... ... ...
4 ... ... ...
The report is going to take Id and Date as inputs and report generated
is in following format
Id: 1
Date: 08/21
ItemType 1 ItemType 2 ItemType 3 ItemType 4
100 500 200 150
This can definetely be done creating a temp table and writing to this
temp table thru multiple selects for each itemtype.
Is there any better way'
ThanksHave a look at the PIVOT function in SQL 2005 Books online.
Or this example on SQL 2000 will get you on the way
http://www.dandyman.net/sql/samples/pivottable.txt
________________________________________
__________
Dandy Weyn - Dandyman (r)
MCSE-MCSA-MCDBA-MCDST-MCT Community Leader
MCTS SQL 2005- MCITP Database Administrator
Author of Sybex Exam Study Guide MCTS SQL 2005
http://www.dandyman.net
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
[Id], [Date]. The sideways generation you're looking for is a pivot
table
type query, which can be done with a "monster" CASE statement in SQL 2000 or
the PIVOT operator in SQL 2005. Either method would require you to know,
and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
advance. If you don't know them in advance you can use dynamic SQL to work
it out.
Personally I'd recommend doing the SUM()/GROUP BY and transferring the
results to a front-end app and format it there.
"absoft" <arpit.00@.gmail.com> wrote in message
news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...
>I need to write query or stored proc that is going to be used to gen. a
> report
> This is a structure of my table
> Id Date ItemType OrdersPlaced
> 1 08/21 1 100
> 1 08/21 2 500
> 1 08/21 3 200
> 1 08/21 4 150
> 2 ... ... ...
> 2 ... ... ...
> 3 ... ... ...
> 4 ... ... ...
> 4 ... ... ...
> The report is going to take Id and Date as inputs and report generated
> is in following format
> Id: 1
> Date: 08/21
> ItemType 1 ItemType 2 ItemType 3 ItemType 4
> 100 500 200 150
> This can definetely be done creating a temp table and writing to this
> temp table thru multiple selects for each itemtype.
> Is there any better way'
> Thanks
>|||Thanks y'all I am using SQL 2000 so pivot operator is not an option
open for me. I like the idea of using the "monster" CASE statement ...
and it almost returns the kind of report that I need ... just one snag
apart from the above fields there is one more calculated field that
references another table
e.g.
Table B
Id ItemType EstimatedTotal
1 1 200
1 2 800
1 3 200
1 4 200
The new field is a sum of EstimatedTotal for all ItemType and is used
for comparison to the actual total. The new report looks something like
this:
[vbcol=seagreen]
going forward with the logic of using CASE and GROUP BY, can I get this
EstimatedTotal of 1400 returned by same query without using cursors or
temp tables
Any thoughts!!
Mike C# wrote:[vbcol=seagreen]
> You can generate a summarized query using SUM(OrdersPlaced) and GROUP BY
> [Id], [Date]. The sideways generation you're looking for is a piv
ot table
> type query, which can be done with a "monster" CASE statement in SQL 2000
or
> the PIVOT operator in SQL 2005. Either method would require you to know,
> and hard-code, your column "headings" (Item Type 1, Item Type 2, etc.) in
> advance. If you don't know them in advance you can use dynamic SQL to wor
k
> it out.
> Personally I'd recommend doing the SUM()/GROUP BY and transferring the
> results to a front-end app and format it there.
> "absoft" <arpit.00@.gmail.com> wrote in message
> news:1156180618.673896.29220@.p79g2000cwp.googlegroups.com...

need help writing batch without cursor

i'm trying to write a batch that will perform a complex task using
set-based selects instead of a row-based cursor. let me know if you can
help me figure out how.
description of what i'm trying to do:
there is TABLE1, TABLE2, and TABLE3
i want to select each row from TABLE1, do some analysis on the data of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3
how do i do this in a T-SQL batch?
thanks in advance!What kind of analysis?
AMB
"iaesun@.yahoo.com" wrote:

> i'm trying to write a batch that will perform a complex task using
> set-based selects instead of a row-based cursor. let me know if you can
> help me figure out how.
> description of what i'm trying to do:
> there is TABLE1, TABLE2, and TABLE3
> i want to select each row from TABLE1, do some analysis on the data of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3
> how do i do this in a T-SQL batch?
> thanks in advance!
>|||i'd be curious how to do it even if there were no analysis, since it's
the row-by-row part that i'm not sure how to do in a set-based
solution.
but! in case it helps, here's the analysis i was thinking of (pardon
the psuedo-code for the row-by-row portion)
for each ROW in TABLE1
{
if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
begin
insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)
end
set @.table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @.table2id)
}|||>> i want to select each row from TABLE1, do some analysis on the data
of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3 <<
Read what you wrote! What kind of spec is that? How do we debug code
which is not here. Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity,
datatypes, etc. in your schema are. Sample data is also a good idea,
along with clear specifications.
Frankly, it sounds likeyou are splitting this vague Table1 into two
tables when you should be using a VIEW or a column with whatever the
criteria for this split is. The whole idea of databases was to avoid
redundant data.|||You don't know Joe.
Maybe he's actually normalizing table1.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112986095.913817.293370@.g14g2000cwa.googlegroups.com...
> of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3 <<
> Read what you wrote! What kind of spec is that? How do we debug code
> which is not here. Please post DDL, so that people do not have to guess
> what the keys, constraints, Declarative Referential Integrity,
> datatypes, etc. in your schema are. Sample data is also a good idea,
> along with clear specifications.
> Frankly, it sounds likeyou are splitting this vague Table1 into two
> tables when you should be using a VIEW or a column with whatever the
> criteria for this split is. The whole idea of databases was to avoid
> redundant data.
>|||Try,
insert into t2 (colA)
select col1
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
insert into t3 (colB, colC)
select col2, col3
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
AMB
"iaesun@.yahoo.com" wrote:

> i'd be curious how to do it even if there were no analysis, since it's
> the row-by-row part that i'm not sure how to do in a set-based
> solution.
> but! in case it helps, here's the analysis i was thinking of (pardon
> the psuedo-code for the row-by-row portion)
> for each ROW in TABLE1
> {
> if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
> begin
> insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)
> end
> set @.table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
> insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @.table2id)
> }
>|||Correction,
Swith the order of the statements.
insert into t3 (colB, colC)
select col2, col3
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
insert into t2 (colA)
select col1
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
AMB
"Alejandro Mesa" wrote:
> Try,
> insert into t2 (colA)
> select col1
> from t1
> where not exists (select * from t2 where t2.colA = t1.col1)
> insert into t3 (colB, colC)
> select col2, col3
> from t1
> where not exists (select * from t2 where t2.colA = t1.col1)
>
> AMB
> "iaesun@.yahoo.com" wrote:
>|||well, i'm trying to keep the topic abstract, because i was hoping for a
more general description of how to do row-by-row processing in a
set-based solution. but, if such details are needed in this case, then
let me try to invent some. regarding the latter portion of your
message: this is, in a manner of speaking, splitting table1 into two
tables. however, it is more of a complex tranformation, not redundant
information. table1 is a staging table, and will be dropped after this
process is complete.
first, the three table definitions:
CREATE TABLE [sourcetable] (
[ID] [int] NOT NULL,
[column1] [int] NULL,
[column2] [int] NULL,
) ON [primary]
GO
CREATE TABLE [destinationtable1] (
[ID] [int] NOT NULL,
[columnA] [int] NULL,
[columnB] [int] NULL,
) ON [primary]
GO
CREATE TABLE [destinationtable2] (
[ID] [int] NOT NULL,
[columnY] [int] NULL,
[columnZ] [int] NULL,
) ON [primary]
GO
what i would like to do, read [sourcetable] row-by-row. for each row, i
would like to perform the following batch:
IF NOT EXISTS (SELECT * FROM destinationtable1 WHERE
destinationtable1.columnA = sourcetable.column1)
begin
INSERT INTO destinationtable1 (columnA) VALUES
(sourcetable.column1)
end
SELECT @.idvariable = ID FROM destinationtable1 WHERE
destinationtable1.columnA = sourcetable.column1
INSERT INTO destinationtable2 (columnY, columnZ) VALUES
(sourcetable.column2, @.table2id)
is that any clearer?|||yes, that is precisely the nature of this task. table1 is an imported
table from an outside system. i'm just splicing it into its logical,
normalized form.|||> what i would like to do, read [sourcetable] row-by-row. for each row,
i
would like to perform the following batch
Wrong. The idea is precisely to AVOID processing anything row-by-row.
Try this:
INSERT INTO destinationtable1 (columna)
SELECT DISTINCT column1
FROM sourcetable
WHERE NOT EXISTS
(SELECT *
FROM destinationtable1
WHERE columna = sourcetable.column1)
INSERT INTO destinationtable2 (columny, columnz)
SELECT S.column2, D.id
FROM sourcetable AS S
JOIN destinationtable1 AS D
ON S.column1 = D.columna
David Portas
SQL Server MVP
--

Need help writing a SQL Server Query

Could someone please help me out? I need to write a sql stored proc to query the following table.
My SQL experience is very week. If someone can help me with this, I will be happy to pay you$40 for
your help.

I need the proc to do the following:
1.) For every Superintendent in a region, country state and county; return the state name, superintendent name,
the county name and and a string which is a comma delimited list of schools they supervise. See the sample output italicised and bold.

So the big challenge here is to also return a string that is a concatenation of school names for a particular
Superintendent in a given state and county. For example:East,Kennedy,Apolo,Morrison.

So basically the stored proc should accept input parameters of the Region, Country, State, and County

Here is the data table:

REGION COUNTRY STATE SUPER_INTENDENT PHONE_NO SCHOOL County

NA USA Texas Mike Andrews 789-3614 East Lake
NA USA Texas Mike Andrews 789-3614 Kennedy Lake
NA USA Texas Mike Andrews 789-3614 Apolo Lake
NA USA Texas Mike Andrews 789-3614 Morrison Lake
NA USA Texas Amy Markson 789-2134 Anderson Maylor
NA USA Texas Amy Markson 789-2134 Molina Maylor
NA USA Texas Amy Markson 789-2134 Polima Maylor
NA USA Ohio Terry Ellis 966-8314 Kingston Keel
NA USA Ohio Terry Ellis 966-8314 Martin Keel
NA USA Ohio Terry Ellis 966-8314 Eastmore Keel
NA USA Ohio Terry Ellis 966-8314 Canondale Keel

Here is the sample output the way it will appear on a web form:

State:Texas

County:Lake

Mike Andrews East,Kennedy,Apolo,Morrison
789-3614

County:Maylor

Amy Markson
789-2134 Anderson,Molina,Polima

State:Ohio

County:Keel

Terry Ellis Kingston,Martin,Eastomore,Keel

You can mail me the check..

You could create a stored proc that would concatenate the values based on given state and county.

Declare

@.schoolvarchar(500)

select

@.school=isnull(@.school,'')+','+ school

from

yourTable

where

state= @.state and county=@.county

Given the above hint, I hope you can figure out the rest.

|||Sorry your post has not helped me at all. I need someone to write the query for me. I will be happy to pay that person. please do not respond to this post if you are not serious about helping me.|||

ndinakar:

You can mail me the check..

That was meant to be a joke. I forgot to put a "smiley face" at the end. I was trying to show you how to write the code yourself since what you are trying to do is pretty simple. Now, I do agree that if you cannot figure out how to put the rest of the puzle together, you definetely need someone to write you the code. And perhaps you will need to spend more than the $40 you are offering..

|||

I appreciate your sense of humor. No problem.

Another fellow on the forum has offered to help me. I will see take his advice and try to get it working. Thank you.

Need help writing a SQL Script

Im attempting to write a script to add specific permissions to tables each
morning.
The problem is I have a database that gets purged each morning along with
the tables, and any permissions I have set for a user is also purged.
My user name is "workflow" with only public rights. In the database, users
"workflow " has select permissions to three tables which are FMS_Acct,
APS_Vendor, APS_Master.
If anyone can assist with the proper SQL that restorse the permissions would
be of great assistance.
Thank you in advance
RobertYou mean something like:
GRANT SELECT ON FMS_Acct TO workflow
"robert_at_cbb" wrote:
> Im attempting to write a script to add specific permissions to tables each
> morning.
> The problem is I have a database that gets purged each morning along with
> the tables, and any permissions I have set for a user is also purged.
> My user name is "workflow" with only public rights. In the database, users
> "workflow " has select permissions to three tables which are FMS_Acct,
> APS_Vendor, APS_Master.
> If anyone can assist with the proper SQL that restorse the permissions would
> be of great assistance.
> Thank you in advance
> Robert|||Yes thank you
"Jack" wrote:
> You mean something like:
> GRANT SELECT ON FMS_Acct TO workflow
> "robert_at_cbb" wrote:
> > Im attempting to write a script to add specific permissions to tables each
> > morning.
> >
> > The problem is I have a database that gets purged each morning along with
> > the tables, and any permissions I have set for a user is also purged.
> >
> > My user name is "workflow" with only public rights. In the database, users
> > "workflow " has select permissions to three tables which are FMS_Acct,
> > APS_Vendor, APS_Master.
> >
> > If anyone can assist with the proper SQL that restorse the permissions would
> > be of great assistance.
> >
> > Thank you in advance
> > Robert

Need help writing a SQL Script

Im attempting to write a script to add specific permissions to tables each
morning.
The problem is I have a database that gets purged each morning along with
the tables, and any permissions I have set for a user is also purged.
My user name is "workflow" with only public rights. In the database, users
"workflow " has select permissions to three tables which are FMS_Acct,
APS_Vendor, APS_Master.
If anyone can assist with the proper SQL that restorse the permissions would
be of great assistance.
Thank you in advance
Robert
You mean something like:
GRANT SELECT ON FMS_Acct TO workflow
"robert_at_cbb" wrote:

> Im attempting to write a script to add specific permissions to tables each
> morning.
> The problem is I have a database that gets purged each morning along with
> the tables, and any permissions I have set for a user is also purged.
> My user name is "workflow" with only public rights. In the database, users
> "workflow " has select permissions to three tables which are FMS_Acct,
> APS_Vendor, APS_Master.
> If anyone can assist with the proper SQL that restorse the permissions would
> be of great assistance.
> Thank you in advance
> Robert
|||Yes thank you
"Jack" wrote:
[vbcol=seagreen]
> You mean something like:
> GRANT SELECT ON FMS_Acct TO workflow
> "robert_at_cbb" wrote:

Need help writing a SQL Script

Im attempting to write a script to add specific permissions to tables each
morning.
The problem is I have a database that gets purged each morning along with
the tables, and any permissions I have set for a user is also purged.
My user name is "workflow" with only public rights. In the database, users
"workflow " has select permissions to three tables which are FMS_Acct,
APS_Vendor, APS_Master.
If anyone can assist with the proper SQL that restorse the permissions would
be of great assistance.
Thank you in advance
RobertYou mean something like:
GRANT SELECT ON FMS_Acct TO workflow
"robert_at_cbb" wrote:

> Im attempting to write a script to add specific permissions to tables each
> morning.
> The problem is I have a database that gets purged each morning along with
> the tables, and any permissions I have set for a user is also purged.
> My user name is "workflow" with only public rights. In the database, users
> "workflow " has select permissions to three tables which are FMS_Acct,
> APS_Vendor, APS_Master.
> If anyone can assist with the proper SQL that restorse the permissions wou
ld
> be of great assistance.
> Thank you in advance
> Robert|||Yes thank you
"Jack" wrote:
[vbcol=seagreen]
> You mean something like:
> GRANT SELECT ON FMS_Acct TO workflow
> "robert_at_cbb" wrote:
>

Need help writing a custom View in Sql Server

Does anyone knowif the following sql view is possible to write and execute as a view script?

**********find employee matching the given UserID*************
SELECT * FROM Employees WHERE EmployeeID=@.UserID

***********find client matching the given ClientID**********
SELECT *FROM Clients WHERE ClientID=@.ClientID

**********findall contacts and events associated with ClientID*********
SELECT *FROM Contacts WHERE Contact.ClientID=@.ClientID
SELECT *FROM Events WHERE Event.ClientID=@.ClientID

*********selectall audits with Key values matching the primary keys of each client, contact orevent*********
SELECT *FROM Audit Where Key In (Client.ClientID, Contact.ContactID, Event.EventID)

I basicallyneed to find a employee based on its ID. Then I need to find any records from the table Auditwith Key values matching the given fields in the results of any clients, contacts events that were returned from the previous select statements. Is this possible?

You need list all your table's schema. If they are related, you can write a query with JOINs and put into a view.|||Well I don't want to JOIN them. I just want to select certain items out of the Audit table based on the key values taken from records retrieved from Clients, Contacts and Events. I just need to return records matching the schema in Audit, nothing else.|||

You could do something like this: If this is not what you are looking for you need to detail the structure of each of the tables and the columns that would be used in the [Key] column in the Audit table.

Declare @.table table (KeyIdint)INSERT INTO @.table SELECT <column>FROM EmployeesWHERE EmployeeId = @.UserIdINSERT INTO @.table SELECT <column>FROM ClientsWHERE ClientID=@.ClientIDINSERT INTO @.table SELECT <column>FROM ContactsWHERE Contact.ClientID=@.ClientIDINSERT INTO @.table SELECT <column>FROM EventsWHERE ClientID=@.ClientIDSELECT *FROM AuditWHERE [Key]In (SELECT KeyIdFROM @.table)

|||

You can format and execute a string similar to:

CREATE VIEW MyView as Select * from MyTable where ID = 55

But the view will be hardcoded to the ID of 55...probably not very useful.

A view itself cannot take parameters but I read that you can do it with user-defined functions.

|||

If you need to pass a parameter to it you need to use a stored procedure, not a view.