MS SQL Server :: Insert Statement Results In Data Duplication Using MDAC 2.8

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"

Could You please tell me what am I missing here ?
Thanks a lot.

View Replies !

Error Catching On Data Duplication In A Sql2005 Db
Hello, everyone. I am having problems catching a data duplication issue. I hope I can get an answer in this forum. If not, I would appreciate it if someone can direct me to the right forum.
I am working on a vs2005 app which is connected to a sql2005 db. Precisely, I am working on a registration form. Users go to the registration page, enter the data, ie. name, address, email, etc. and submit to register to the site.
The INSERT query works like a charm. The only problem is that I am trying to reject registrations for which an email address was used. I put a constraint on the email field in the table and now if I try to register using an e-mail address that already exists in the database I get a violation error (only visible on the local machine) on the sql's email field, which is expected.
How can I catch that there is already an email address in the database and stop the execution of the code and possibly show a message to the user to use a different address?
Thank you for all your help.

Antonio

View Replies !

HOW? Access Results Of Computed SQL Statement In A Data Flow
The SQL computed is complex enough that I can't see a way to make it a parameterized query. The obvious approach seems to be to compute the SQL in a CONTROL FLOW SCRIPT TASK and then use it to load a variable to set the VARIABLE SOURCE of a CONTROL FLOW EXECUTE SQL TASK.

I see that I can return a resultset to a variable.

But getting the rows of the results into a dataflow is not obvious. I have heard mentione that a Derived Column can do this. I can see using a dummy SCRIPT COMPONENT as DATA SOURCE with nothing in it to then drop into DERIVED COLUMN. But when setting up DERIVED COLUMN I don't see how to pull the columns out of the RESULTSET variable.

If it makes a difference I think the columns of the resultset will always be the same in this scenario.

Maybe this is totally the wrong approach? Any clues would be appreciated.

View Replies !

Excluding Part Of Select Statement If No Data Is Returned In Results
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.
Here is my select statement:
SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate -
(SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name,
department.department_name, location.location_name
FROM employee INNER JOIN
request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN
department ON employee.emp_department = department.department_id INNER JOIN
location ON department.department_location = location.location_id
GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial,
employee.emp_lname, department.department_name, location.location_name
ORDER BY location.location_name, department.department_name, employee.emp_lname

The section below is the part that may or may not contain information:
SELECT (SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft

So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!
TIA

View Replies !

Fix Legacy Data - Missing Primary Key + Duplication Record + Large Table
We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work

Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table"

Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?

View Replies !

Minimum MDAC Version Requirement (Error Very Strange..because My MDAC Is 2.8 Sp1) ...help Me Please!!!
I trying to install SQLServerExpress on PC with W2000 Pro Sp4 - IE6.0Sp1 - dot.net framework 2.0 and MDAC 2.8 Sp1, but when the setup check the requirements, I received this error:

Minimum MDAC Version Requirement (Error)
Messages
* Minimum MDAC Version Requirement

* The system does not have the required version of Microsoft Data Access Components (MDAC) for this SQL Server release. For details, see Hardware and Software Requirements for Installing SQL Server 2005 in Microsoft SQL Server Books Online.

After... I reinstalled all MDAC versions and the MDAC Component Checker and I verifyed that my version is 2.8.Sp1 and the key HKEY_LOCAL_MACHINESOFTWAREMICROSOFTDATAACCESSFullInstallVer=2.81.1117.6.

Why??? :-(((( help me please!!!!

Thanks a lot!

View Replies !

Bulk Insert Vs. Data Flow Task (different Row Results Using Flat File Source)
I'm importing a large csv file two different ways - one with Bulk Import Task and the other way with the Data Flow Task (flat file source -> OLE DB destination).

With the Bulk Import Task I'm putting all the csv rows in one column. With the Data Flow Task I'm mapping each csv value to it's own column in the SQL table.

I used two different flat file sources and got the following:

Flat file 1: Bulk Import Task = 12,649,499 rows; Data Flow Task = 4,215,817 rows
Flat file 2: Bulk Import Task = 3,403,254 rows; Data Flow Task = 1,134,359 rows

Anyone have any guess as to why this is happening?

View Replies !

Insert Statement That Pulls Column Data From Another Table?
hi.

i cant get this quite right.

i have a table and i need to insert one column with data from another table. it goes something like this (although i know this is wrong, just here for a visual explaination) :

Code:

INSERT INTO List
(list_date, email_address, list_status, list_email)
values
(
GetDate()
, 'name@rice.edu'
, 0
, SELECT emailAddress FROM Users WHERE id = '72'
)

so, list_email needs the email address from the Users
table. i tried messing around with inner joins but, well,
here i am...

thanks in advace.

View Replies !

Problem With Data Refreshing After Insert Or Update Statement
Dear All,

Im using VS2008, visual basic and SQL Server express.

General questions please: Is it necessary, to issue some sort of command, to 'commit' data to a SQL express database after a INSERT or UPDATE sql command?

I'm getting strange behavior where the data is not refreshed unless i exit my app and re-enter. In other words, i can run a sql command , the data is apparantly saved (because i get no errors) then if i refresh a data set or do a sql select query the data that i expect to return is not there.

Im fairly new to SQL express (and SQL server generally) so i dont know if its my coding or i need to switch some 'feature'
on/off or not.

I hope thats clear

Also, could someone point me to documentation that explains each parameter in the connection string

Many Thanks

Chris Anderson

My code is:

ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

Connection = New SqlConnection

Connection.ConnectionString = ConnectionString

Connection.Open()
'''''''''''''''''the above code is done at the start of my application

'''''''this code below called during application many times

dim sql as string = "my sql string here"

Dim cmd As SqlCommand = Nothing

cmd = New SqlCommand(sql, Connection)

Try

cmd.ExecuteNonQuery()

Catch err As SqlException

MessageBox.Show(err.Message.ToString())

MessageBox.Show(sql, "SQL ERROR: ", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

cmd.Dispose()

View Replies !

How Do I Prevent An Insert Into Statement To Increase Tempdb Data Files So Much
I'm running this procedure which insert into table_name(id, name.....) select id, name.... from table_name. For some reason the tempdb data file grow up to 200GB. The tempdb is set to expand unrestricted by 10%. How can I prevent that from hapening? Thanks.

View Replies !

System.Data.OleDb.OleDbException: Syntax Error In INSERT INTO Statement.
Hi All I'm having a bit of trouble with an sql statement being inserted into a database - here is the statement: string sql1;
sql1 = "INSERT into Customer (Title, FirstName, FamilyName, Number, Road, Town,";
sql1 += " Postcode, Phone, DateOfBirth, email, PaymentAcctNo)";
sql1 += " VALUES (";
sql1 += "'" + TxtTitle.Text + "'," ;
sql1 += "'" + TxtForename.Text + "'," ;
sql1 += "'" + TxtSurname.Text + "'," ;
sql1 += "'" + TxtHouseNo.Text + "',";
sql1 += "'" + TxtRoad.Text + "',";
sql1 += "'" + TxtTown.Text + "',";
sql1 += "'" + TxtPostcode.Text + "',";
sql1 += "'" + TxtPhone.Text + "',";
sql1 += "'" + TxtDob.Text + "',";
sql1 += "'" + TxtEmail.Text + "',";
sql1 += "'" + TxtPayAcc.Text + "')"; Which generates a statement like:INSERT into Customer (Title, FirstName, FamilyName,
Number, Road, Town, Postcode, Phone, DateOfBirth, email, PaymentAcctNo) VALUES ('Mr','Test','Test','129','Test Road','Plymouth','PL5
1LL','07855786111','14/04/1930','mr@test.com','123456') I cannot for the life of me figure out what is wrong with this statement. I've ensured all the fields within the database have no validation (this is done within my ASP code) that would stop this statement being inserted. Line 158: dbCommand.Connection = conn;Line 159: conn.Open();Line 160: dbCommand.ExecuteNonQuery();Is the line that brings up the error - I presume this could be either an error in the statement or maybe some settings on the Database stopping the values being added. Any ideas which of this might be ? I'm not looking for someone to solve this for me, just a push in the right direction! Thanks!

View Replies !

1 Log File Can Contain More Records After Performing Backup Database Statement, 2 Why Can't Insert Data When Is Log Is Not Full
question 1:

i found that database log file can contain more records after performing backup database statement.

for example:

i create a database and limit the log file to 2mb. then i create a table and insert data.

If i backup the database before i insert data , the database file can contain 192 records unitl the log file is full.

If i don't perform the 'backup database' statement.
The 'dbcc sqlperf(logspace)' indicate the utilization ratio is less than 40% after inserting 192 records

why?

I list my code:

Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
backup database db_test to disk='db_test.bak' --- if i don't execute this line, log file can contain a lot of record
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<192
begin
insert into db_test..table1 values(replicate('a',8000))
set @n=@n+1
end

question 2:
i create a database and limit the log file to 2mb. Then i create a table and insert data in an endless loop.

After the inserting operation executing for a while, the 9002 error occurs, indicate the log file for the database is full.
But the 'dbcc sqlperf(logspace)' command indicate the unilization ratio is low, and log_reuse_wait_desc in sys.database is 'CHECKPOINT'
And I can insert data , and i'm sure the state of log_use_wait_desc is 'CHECKPOINT'.

As i known, the checkpoint can't truncate log under full recovery model. Only the back log operation can truncate the transaction log.
So log is not full, why 9002 error is encounterd. and why the log_reuse_wait_desc return 'CHECKPOINT'?

I list my code:

Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
create table db_test..table1(col char(8000))

--insert data to fill up the database log
declare @n int
set @n=0
while @n<>-1
begin
insert into db_test..table1 values(replicate('a',8000))
end

any suggestions?

thanks in advance.

View Replies !

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!

View Replies !

Extract Data In &"Insert Into...&" Statement Format
Is there a way in SQL Server 2000 to extract data from a table, such thatthe result is a text file in the format of "Insert Into..." statements, i.e.if the table has 5 rows, the result would be 5 lines of :insert into Table ([field1], [field2], .... VALUES a,b,c)insert into Table ([field1], [field2], .... VALUES d, e, f)insert into Table ([field1], [field2], .... VALUES g, h, i)insert into Table ([field1], [field2], .... VALUES j, k, l)insert into Table ([field1], [field2], .... VALUES m, n, o)Thanks in advance

View Replies !

Duplication
How can I achieve the following...
I have a Membership No. field which comes from a bookings table, so multiple membership no. do exist. What I want to achieve is a list of membership No.s with no duplication. Sorry to be so dumb, but we all have to start somewhere.

View Replies !

Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62

thanks

View Replies !

How To Write Query To Insert 10,000 Rows In A Table Using Insert Statement One Time
create table and inserting 10,000 row values at a time using single insert statment

ex: I want to create table employee having two coloumns
like employeeid , name

note : name can be static i.e : same name for 10,000 rows

employeeid is unique

can any one write a query for me?

View Replies !

Using SQL Results In If Statement

Hello
How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters></asp:SqlDataSource>
any help would be appreciated

View Replies !

Duplication Error
i have taple translatio witch have theses coloumn
ID,TypeID,Status,ComID,RecordID,Translator
in this table we assign a certain company with a certain typeID to a certain translator .
so next time when the translator log he goes to the company that he assigned to it when he log to the company page,
there is another company page n arabic that is transalted by the translators , in the arabic page there is a button when you can send this company to transaltion.
but a duplicate have been happen made because user send the same documnet to the translation table so many time so translator transalte the same company profile more than one time witch is not good.

we handle the duplicate of the send to translation button put still there is duplicate record in the database witch is more taht 3000 record
how ican to remove thsi record from the DB without make ant other erroe

View Replies !

Report Job Duplication

HI

I have created a job to run a reporting services job which then named it in the job scheduler 354EEF12-404F-46BD-B54F-708B5027837F. I then renamed the job to Rpt ETL log.

However it I was surpised to see two emails come with reference to this report. It seems to have created another one with the long job names.

Is there any way to stop this as I would really like to name to schduled rpt jobs without it duplicating.

Many Thanks

Robert

View Replies !

Case Statement Error In An Insert Statement
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View Replies !

Prevent Duplication On UPDATE
Hello
I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling:
UPDATE [dbo].[Prod_Cat] SET [ProdName]=N'merseyside' WHERE ProdName = 'mmserseyside'
The above code correctly updated the spelling error, but it also inserted a new row with the corrected data.
So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows.
What do I need to do to prevent this happening next time. As I find that I need to update the names of some products, but I don't want to duplicate them.
Thanks
Lynn

View Replies !

Track Duplication Of Records
Hello,

I have a table which consists of 27,000 of records. Among these records, there is one record which
is a duplication of another record.

Is there any way to track this record from the same table by the SQL statement ?

I have been advised to use the following statement but it does not help:

Select count(*) As Duplicate, columnname from tablename group by columnname

Scrolling 27,000 lines of records with bare eyes is very painful.

Any help is appreciated.

Cheers

View Replies !

Duplication In The Primary Key Column
Hi
I have a set of excel files that i need to export to sql2005 using ssis. Now the issue is that i have no idea about he data ie it may have duplication in the primary key column. If i export it as it is to sql server, it will cause me problems. Is there any way i can filter out the rows which have duplication in the primary key column?
Umer

View Replies !

De-Duplication Performance Issue
Hi All,

Scenario: De-duplication logic should pick one record from source and check with all the destination records and insert if not duplicate. Else raise error. There are average 8 to 10 lookup check for each logic path. Key fields used for de-duplication check are FirstName, LastName, DOB, Gender, SSN.

Issues:
· Since picking row by row and processing the performance is constrained.
· Since 8-10 comparison is done using lookup performance downstream. (Lookup is used without caching, if cashing is used the package is failing after sometimes as if memory is failing. Can we handle this memory problem?)

Please give some suggestion to improve the performance. Current performance is around 2500 records per hour, but there are 8 lac records in total to process.

I am looking for guidance on this issue. If someone can guide me on how to do it better it would help me a lot.

Thanks,
S Suresh

View Replies !

How To Avoid PrimaryKey Duplication?
Hi, I having a problem with my query...

I want to copy data from 4 different database to 1 database... but if the destination database have already the same Primary Key the copying stops/terminated and not copying others that is not yet in the destination...

I don't have knowledge in T-SQL like IF...ELSE
my database is SQL Server 2000 but i'm using SQL 2005 Express Management for the query...

What i'm doing is like this:

Use osa (Destination Database)
Go

DELETE FROM tblFaculty (*I'll delete first the datas to avoid duplication)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM cislucena.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM amapn.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM abe.dbo.tMasFaculty)

INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM aclc.dbo.tMasFaculty)

My problem is if the facultyID (PrimaryKey) which i'm copying is already on the destination which is osa, the copying stops/terminated regardless whether there is more to copy. On the 4 source database, there might data that other database also has. That's why the copying is terminated. All i want to do is to check first each FacultyID if it is already on the destination before copying it to avoid error or duplication of Primary Key so it won't terminate the copying.

How is this possible sir? Anyone care to help? Thanks in advance! More Power!

Best Regards

View Replies !

Cycling Through Results Of A Select Statement
I am new to stored procedures and T-SQL so please stick with me. I have a table that holds information about companies. I am trying to write a stored procedure that when run will query that table and find out if there are more than one entry of that company. All company names in that table must be unique (they can only occur once), if they occur more than once I need to flag it for reporting.

So what is the best way to go about this? Essentially what i was thinking was doing a select * on the table and then going from the first entry to the last and at each entry running a select * from table where companyname = @nameofcompany. @nameofcompany would be the name for that entry. If the select statement revealed more than one entry then i would know there was a problem.

Like I said I am new and this is probably very simple but i need a little help getting started

thanks

View Replies !

Select Statement Returns No Results
I am using the following conditional select statement but it returns no results.

Declare @DepartmentName as varchar
Set @DepartmentName = null
Declare @status as bigint
Set @status = 4
IF (@DepartmentName = null) BEGIN

SELECT CallNumber AS [Call Number], Problem, Solution, Note
                          FROM          AdminView
                          WHERE      (Status = @status)
                          ORDER BY CallLoggedDT
END
ELSE IF (@DepartmentName <> null) Begin

      SELECT     CallNumber AS [Call Number], Problem, Solution, Note
      FROM         dbo.AdminView
      WHERE     (Status = @status) AND
(DepartmentName = @DepartmentName)
      ORDER BY CallLoggedDT
end

when i run the 2nd half by itself it tells me to declare @status but not @departmentname. whats going on???

Chris Morton

View Replies !

How Do I Print The Results Of My SELECT Statement?

After running a select statement in vb 6, how do i send the results to the printer. Here is the code i've written so far:

Private Sub ProcSelectRecords()
Dim MyConn As ADODB.Connection

        Set MyConn = New ADODB.Connection
        Dim MyRecSet1 As New ADODB.Recordset

        MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:DocumentsA2 Computing Courseworkdb1.mdb"
        MyConn.Open

    Set MyRecSet1 = MyConn.Execute("SELECT * FROM tblItem_Sales")

    MyConn.Close
End Sub

Im using MS Access as my backend

any help would me much appreciated.

View Replies !

Why Is This JOIN Statement Not Returning The Right Results?

Hi,
I have three tables:
1 Order Details
2 Product Info
3 Received Qty

The query is pretty simple however I'm getting incorrect results.

The query grabs a load of information (including a SUM statement) from the order details tbl.. this is INNER JOIN ed to the products tbl to match an ID field which grabs some extra info from the product info tbl.

all fairly straightforward and works without a problem..

However I now need to add in the received qty tbl (as the name suggests this keep a track of the qty's of items received)

I need to join this tbl to the order details tbl based on a unique ID from the orders tbl and then SUM together all of the entries in that received qty tbl for that item.

Result is I get a single record which shows the qty ordered (from the order details tbl), and the total received (from the received qty) tbl

These are held in a seperate tbl as an order can be deliveryed in several stages.

Here is my statement:

SELECT     PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID,
                      dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem,
                      dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
                      SUM(dbo.Suppliers_POs_Details.Qty * dbo.Suppliers_POs_Details.PricePerItem) AS LineTotal, SUM(dbo.Suppliers_POs_Details_Received.Qty)
                      AS QtyReceived
FROM         dbo.Suppliers_POs_Details INNER JOIN
                      PDS_Login.Supplier_Products ON dbo.Suppliers_POs_Details.ProductID = PDS_Login.Supplier_Products.ProductID LEFT OUTER JOIN
                      dbo.Suppliers_POs_Details_Received ON dbo.Suppliers_POs_Details.PODetailsID = dbo.Suppliers_POs_Details_Received.PODetailsID
WHERE     (dbo.Suppliers_POs_Details.POID = @POID)
GROUP BY dbo.Suppliers_POs_Details_Received.Qty, PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode,
                      dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem,
                      dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
                      dbo.Suppliers_POs_Details_Received.Qty

The problem is, if there is more than one relating record in teh Received Qty tbl instead of that being added together as I want, two results appear...

It's obviously something simple but I can't seem to figure it out - anyone point me in the right direction?

View Replies !

Count # Of Results From SELECT Statement
Hey all - VERY new to SQL so I apologize if I butcher normally trivial things :)

Looking to run a query that will retrieve the number of results returned from a select statement...

Currently have a LicenseID table with a Software column...the statement that works on it's own that i've got is:

SELECT * FROM Software WHERE LicensesID = 2

Currently when I run that with the data so far I get 4 results returned to me...how can I add to that statement so that instead of displaying the results themselves, I just get the number 4 returned as a total number of results?

Thanks all!

View Replies !

Formatting Case Statement Results
I am trying to format the Sys field (which is Int) in case the first byte is zero. But I'm getting an error msg;
Msg 156, Level 15, State 1, Procedure sp_Pull_Cancels, Line 43
Incorrect syntax near the keyword 'Right'.

CASE
WHEN substring(PCRACT, 5, 1) = '4' THEN 3641
WHEN substring(PCRACT, 5, 1) = '5' THEN 2012
WHEN substring(PCRACT, 5, 1) = '6' THEN 9694
END as Right('0000' + Cast(Sys as varchar (4)),4)

Any ideas how I construct this CASE statement to get the desired results.

Thanx,

View Replies !

Single Field Duplication Problem
I have a table with many fields but there is a single field that I do not want duplicates. If I index this specific field preventing duplicates, the entire record does not append. (The field in question is not keyed).

Thanks

Bill Howard

View Replies !

How To Put A Select Statement And Get Results In A Stored Procedure
Hello,I have written a stored procedure where I prompt the user for the Year and the Month (see below)How do I take the variable @TheMonth and find out how many days is in the month and then loop to display a total for every day in the selected month.Can someone please point me in the right direction.
CREATE PROCEDURE crm_contact_frequency_report
@TheYear         varchar(4),@TheMonth      varchar(2)
AS
SELECT
/* EMAILS (B) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND   (B.directioncode = 1)) AS    Total_EmailOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND   (B.directioncode = 0)) AS    Total_EmailImconing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND   (B.directioncode IS NULL)) AS    Total_EmailNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)) AS    Total_All_Emails,
/* PHONE CALLS (C) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND   (C.directioncode = 1)) AS    Total_CallOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND   (C.directioncode = 0)) AS    Total_CallIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND   (C.directioncode IS NULL)) AS    Total_CallNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)) AS    Total_All_Calls,
/* FAXES (D) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND   (D.directioncode = 1)) AS    Total_FaxOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND   (D.directioncode = 0)) AS    Total_FaxIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND   (D.directioncode IS NULL)) AS    Total_FaxNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)) AS    Total_All_Faxes
FROM   CampaignResponse AGO

View Replies !

SELECT Statement Not Giving Me Desired Results
I have the following SELECT statement attached to a dropdown box:
SELECT [Workshop] FROM [Workshops] WHERE Workshop <> (SELECT Workshop FROM Workshop_Registration WHERE FullName = @FullName AND Completed = @Completed) ORDER BY [Workshop]
I am trying to get all workshops (50 or more) from the WORKSHOPS table that the logged in user is not already registered for. This works perfectly as long as the student is registered for at least 1 class. It populates the dropdown with all of the other classes. If they aren't registered for a class then it doesn't list any classes. The problem is definitely the subquery, but how do I make it to where if the subquery doesn't return any results (student not registered for anything), I get all of the workshops in the dropdown? Any help is appreciated!
MikeD

View Replies !

Results Of SQL SELECT Statement Into Stored Procedure
I need to run a stored procedure on all the results of a select query.For a simplified example, I'd like to be able to do something likethis:SELECT JobID, QtyToAddFROM JobsWHERE QtyToAdd > 0Then for the results of the above:EXEC sp_stockadd @JobID, @QtyToAddWhere the above line ran for every result from the above select query.Anyone able to shed some light on how to do this?Many thanks,James

View Replies !

Getting A Variable That Has A SELECT Statement To Return Results
I have concatenated a long Select Statement and assigned it to a variable. (i.e.)

@a = Select * from foo ---Obviously mine is much more robust

How do I execute @a to actually Select * from foo?

View Replies !

Storing Results Of Select Statement In @variable

I'm new to sql stored procedures but I would like to store the results
of an sql statement in a variable such as:

SET @value = select max(price) from product

but this does not work, can someone tell me how I would go
about in storing the results in a variable.

@value is declared as int

Thanks in advance,
Sharp_At_C

View Replies !

Basic UPDATE Statement Results Question

Hi,

I have written a basic UPDATE statement to update two fields in one table using a where clause to identify the record i wish to update. i.e.

UPDATE TableName SET field1=1234, field2='' WHERE primary_key=N'1234';

When i run the statement, in the results window i get a value returned that is equal to the total number of records in that table!! i.e.

(No column name)
--------------------------
588061

but when i check the table, indeed only one record has been updated as expected (and i have confirmed this by using SELECT @@ROWCOUNT straight after)

My question is, why does it do this?? i paniced at first as i thought i had updated ALL rows, but luckily this seems not to be the case.

And can i turn this off??

Many thanks,
Martin

View Replies !

Insert SP Results Into Variable?
Is it possible to take the result from an SP that returns 1 value andinsert it into a variable?

View Replies !

How To Insert Query Results ?
How can I insert the results of a query into a .csv file ?

View Replies !

Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:

SELECT
l.loanid,
p.investorid,
l.duedate,
case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate,
pc.interestrate
FROM loan l
inner join participation p on p.loanid = l.loanid
inner join paymentchange pc on pc.loanid = l.loanid
where p.investorid = '12345' and RateDueDate is not null
order by l.loanid, pc.duedate

I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.

Thanks!

View Replies !

Weird INSERT INTO SELECT Results
Hi all,

I am having a weird problem occur when trying to insert missing records based on an INSERT INTO...SELECT statement.

Basically, I am populating a temporary table with data, then inserting into a main table those records from the temporary table that aren't in the main table. In addition, I am updating those records that are in the main table.

When I call the following SQL, all of the records from the temporary table are inserted even though those records already exist. Bizzarrely the update statement works perfectly.

INSERT INTO
AffiliateCategories (Identifier, AffiliateID, NetworkID, AffiliateCategoryName, NetworkCategoryName)
SELECT
Identifier,
AffiliateID,
1,
AffiliateCategoryName,
NetworkCategoryName
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName NOT IN
(
SELECT
AffiliateCategoryName
FROM
AffiliateCategories
)

I am not sure why it is doing this and it's driving me crazy. If anyone has any ideas why this is happening, or knows of a better way to accomplish this then please let me know.

Are there some issues with string comparisons going on here?

The table that populates the temp table is defined as:

CREATE TABLE [dbo].[Staging_TradeDoubler_Categories](
    [id] [int] NULL,
    [name] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
    [merchantName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
    [TDCategories_Id] [int] NULL,
    [AffiliateID] [int] NULL
) ON [PRIMARY]

Full code listing:

IF OBJECT_ID('tempdb..#TempTradeDoublerCategories') IS NOT NULL
DROP TABLE #TempTradeDoublerCategories

CREATE TABLE #TempTradeDoublerCategories
(
Identifier INT,
AffiliateID INT,
AffiliateCategoryName VARCHAR(255),
NetworkCategoryName VARCHAR(255)
)

INSERT INTO
#TempTradeDoublerCategories (AffiliateCategoryName)
SELECT DISTINCT
CAST(merchantName AS VARCHAR(255)) AS MerchantName
FROM
Staging_TradeDoubler_Categories
ORDER BY
MerchantName

UPDATE
#TempTradeDoublerCategories
SET
#TempTradeDoublerCategories.Identifier = Staging_TradeDoubler_Categories.id,
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Categories.AffiliateID,
#TempTradeDoublerCategories.NetworkCategoryName = Staging_TradeDoubler_Categories.[name]
FROM
Staging_TradeDoubler_Categories
WHERE
Staging_TradeDoubler_Categories.merchantName = #TempTradeDoublerCategories.AffiliateCategoryName

UPDATE
#TempTradeDoublerCategories
SET
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Affiliates.AffiliateID
FROM
Staging_TradeDoubler_Affiliates
WHERE
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Affiliates.TradeDoublerAffiliateID

Print 'Inserting records'

INSERT INTO
AffiliateCategories (Identifier, AffiliateID, NetworkID, AffiliateCategoryName, NetworkCategoryName)
SELECT
Identifier,
AffiliateID,
1,
AffiliateCategoryName,
NetworkCategoryName
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName NOT IN
(
SELECT
AffiliateCategoryName
FROM
AffiliateCategories
)

Print 'Updating records'

UPDATE
AffiliateCategories
SET
AffiliateCategories.AffiliateCategoryName = #TempTradeDoublerCategories.AffiliateCategoryName,
AffiliateCategories.NetworkCategoryName = #TempTradeDoublerCategories.NetworkCategoryName,
DateModified = GETDATE()
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName IN
(
SELECT
AffiliateCategories.AffiliateCategoryName
FROM
AffiliateCategories
)

PRINT 'Dropping table'

IF OBJECT_ID('tempdb..#TempTradeDoublerCategories') IS NOT NULL
DROP TABLE #TempTradeDoublerCategories

View Replies !

Group By Query Insert The Results Into Different Table
I am trying to do a select statement and input the result to a different table how can this be done in one step? Now I am just coping to excel and importing back in this is a real pain.

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

View Replies !

Insert Into Temp Results Of A Union Query
Hi,
I have follwing union query. I want to put this all in a temp table.

select Store_Id,batchnumber
From
Adjustments where updatedDt between '10/30/2007' and '11/20/2007' and Store_id in(8637 ,8641)
group by Store_Id, batchnumber
Union
select DestinationId,b.batchNumber
from
batch b
inner join Carton C on C.Carton_Id = b.General_ID
inner join Document d on d.Document_Id = c.Document_Id
where b.BatchType = 'Warehouse' and b.TranTable = 'Carton'
and (d.DestinationId in (8637 ,8641) ) and c.UpdatedDt Between '10/30/2007' and '11/20/2007'
Union
select d.DestinationId,b.Batchnumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferIn' and b.TranTable = 'Document'
and (d.DestinationId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
Union
select d.SourceId,b.batchNumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferOut' and b.TranTable = 'Document'
and (d.SourceId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
order by batchnumber

Kindly advice.

Thanks
Renu

View Replies !