Combing Two Tables With No Relationship
May 8, 2007
Which way to go?
Have two separate programs(Visual Studio-Coded in Visual Basic) that share the same sql 2005 database(called Night Audit)
The tables are called RoomSettlement and Settlement.
They were separate tables and separate programs in the past because it was just exported in excel to a visual integrator that dumped the amounts into our accounting software.
Now needing to combine the two tables(that have no link) on to a single excel sheet daily where the tables have the same date:
Table: Room Settlement – Field: RoomSettlementDatetxt =
Table: Settlement – Field: SettlementDatetxt
Any Ideas on the best way to go? (Link the tables with a foreign key and primary key, maybe union based on date, or any easier suggestion with out having to change a lot of the program )
Room Settlement Table
PrimaryKey
Availabilitytxt
Ratetxt
Othertxt
Arrivalstxt
departurestxt
GuestCounttxt
HotelOccupancytxt
AverageDailyRatetxt
RevPahrtxt
HotelRevenuetxt
ownerrentaltxt
hoteltransienttxt
hotelcorptxt
hotelgovtxt
hotelpacktxt
hotelexpediatxt
hotelsynxistxt
hotelothertxt
rentalpooltxt
employeetxt
Hotelnightstxt
activitestxt
Bonustxt
Golftxt
Tvinternettxt
laundrytxt
misctxt
petstaytxt
Telephonetxt
Purchaseunittxt
Damageincometxt
resalerevtxt
latefeestxt
IntrestIncometxt
Taxestxt
RoomSettlementDatetxt
sitetxt
senddatetxt
Settlement Table:
PrimaryKeyintUn
PreviousGuesttxt
TransferCityLedgertxt
ActivityGuesttxt
transferfromadvdepositguesttxt
transfromguesttoguesttxt
GuestchagefromInctxt
Guestnetcashtxt
Guestcheckstxt
guestpaidout
guestlockboxtxt
guestaetxt
GuestDinerstxt
GuestDiscovertxt
GuestMastercardtxt
GuestVisatxt
guestdepositchangecashtxt
guestnetchangetxt
guestnewbalancetxt
guestsystembalancetxt
guestvariance
PreviousCity
ActivityCitytxt
Citynetcash
Citychecks
citypaidouttxt
citylockboxtxt
cityaetxt
CityDinerstxt
CityDiscovertxt
CityMastercardtxt
CityVisatxt
citydepositchangecashtxt
citynetchangetxt
citynewbalancetxt
citysystembalancetxt
cityvariance
Sitetxt
DateStamptxt
SettlementDatetxt
Thanks JK
View 1 Replies
ADVERTISEMENT
Feb 9, 2007
i have a form that has a checkbox list
a table called checkboxitems populates the checkboxlist (checkname, checkid) and when submitted the selected items are put into another table's column selecteditems like "1,5,9"
is there a way to do an inner join or something to get the checkname dependent on which which items are in the selectitems
View 1 Replies
View Related
May 31, 2008
:rolleyes: can u send me deails for establish relationship of tables
i know namw of then but i need them by examples ans defination
as 1 simple
2 complex
3 muliple
u can send me ink if u get fron google also thanks
bye
View 7 Replies
View Related
May 14, 2007
<----------I
have 2 tables are: 'customers (parent)' and 'open_ac (child)'
<--------I
have tried to insert and update data into sql database by using textboxes
(don't use datagrid)
<--------My
tables details are below
<-------this
table uses for keeping user data
customers
fields:
Column
name
type
length
Description
cu_id
int
4
Primary key
Identifiers
cu_fname
nvarchar
20
allow null
first name
cu_lname
nvarchar
40 allow null
last name
cu_nat
nvarchar
20 allow
null
nationality
cu_add
nvarchar
40 allow null
address
cu_wplace
nvarchar
40 allow
null
workplace
cu_tel
nvarchar
10
allow
null
telephone
cu_fax
nvarchar
10
allow null
fax
cu_email
nvarchar
10 allow
null
email
<----the
open_ac uses for keeping register date/time of customers
open_ac
fields:
Column
name type
length
Description
cu_id
int
4
link key
op_date
date/time
8
register date
<----------my
code
Imports
System.Data.SqlClient
Public Class cus_reg
Inherits System.Web.UI.Page
Dim DS As DataSet
Dim iRec As Integer 'Current Record
Dim m_Error As String = ""
Public Property MyError() As String
Get
Return
m_Error
End Get
Set(ByVal Value As String)
m_Error =
Value
If
Trim(Value) = "" Then
Label3.Visible = False
Else
Label3.Text = Value
Label3.Visible = True
End If
End Set
End Property
Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim C1 As
New MISSQL
'DS =
C1.GetDataset("select * from customers;select * from open_ac;select * from
accounts")
DS =
C1.GetDataset("select * from customers;select * from open_ac")
Session("data") = DS
iRec = 0
Viewstate("iRec") = iRec
Me.MyDataBind()
Dim Dtr As
DataRow = DS.Tables(0).NewRow
DS.Tables(0).Rows.Add(Dtr)
iRec =
DS.Tables(0).Rows.Count - 1
viewstate("iRec") = iRec
Me.Label2.Text = DateTime.Now
Me.MyDataBind()
Else
DS =
Session("data")
iRec =
ViewState("iRec")
End If
Me.MyError = ""
End Sub
Public Function BindField(ByVal FieldName As String) As
String
Dim DT As DataTable = DS.Tables(0)
Return DT.Rows(iRec)(FieldName)
& ""
End Function
Public Sub MyDataBind()
Label1.Text = "Record : "
& iRec + 1 & " of " & DS.Tables(0).Rows.Count
txtcu_id.DataBind()
txtcu_fname.DataBind()
txtcu_lname.DataBind()
txtcu_add.DataBind()
txtcu_occ.DataBind()
txtcu_wplace.DataBind()
txtcu_nat.DataBind()
txtcu_tel.DataBind()
txtcu_fax.DataBind()
txtcu_email.DataBind()
End Sub
Here is
update code
Private Sub bUpdate_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles bUpdate.Click
Dim DT As DataTable = DS.Tables(0)
Dim DR As DataRow = DT.Rows(iRec)
'Dim DR1 As DataRow = DT1.Rows(iRec)
If DR.RowState = DataRowState.Added
Then
If
txtcu_id.Text.Trim = "" Then
Me.MyError = "please enter your id"
Exit Sub
End If
DR("cu_id")
= txtcu_id.Text
End If
If txtcu_fname.Text.Trim =
"" Then
Me.MyError =
"please enter your name"
Exit Sub
Else
DR("cu_fname") = txtcu_fname.Text
End If
If txtcu_lname.Text.Trim =
"" Then
Me.MyError =
"please enter your last name"
Exit Sub
Else
DR("cu_lname") = txtcu_lname.Text
End If
If txtcu_add.Text.Trim =
"" Then
Me.MyError =
"please enter your address"
Exit Sub
Else
DR("cu_add") = txtcu_add.Text
End If
If txtcu_occ.Text.Trim =
"" Then
Me.MyError =
"please enter your occupation"
Exit Sub
Else
DR("cu_occ") = txtcu_occ.Text
End If
If txtcu_wplace.Text.Trim =
"" Then
Me.MyError =
"please enter your workplace"
Exit Sub
Else
DR("cu_wplace") = txtcu_wplace.Text
End If
If txtcu_nat.Text.Trim =
"" Then
Me.MyError =
"Please enter your nationality"
Exit Sub
Else
DR("cu_nat") = txtcu_nat.Text
End If
If txtcu_tel.Text.Trim =
"" Then
DR("cu_tel") = DBNull.Value
Else
DR("cu_tel") = txtcu_tel.Text
End If
If txtcu_tel.Text.Trim =
"" Then
DR("cu_fax") = DBNull.Value
Else
DR("cu_fax") = txtcu_fax.Text
End If
If txtcu_email.Text.Trim =
"" Then
DR("cu_email") = DBNull.Value
Else
DR("cu_email") = txtcu_email.Text
End If
Dim Strsql As String
If DR.RowState = DataRowState.Added
Then
Strsql =
"insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email)
values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)"
Else
Strsql =
"update customers set
cu_fname=@P2,cu_lname=@P3,cu_add=@P4,cu_occ=@P5,cu_wplace=@P6,cu_nat=@P7,cu_tel=@P8,cu_fax=@P9,cu_email=@P10
where cu_id =@P1"
End If
Dim C1 As New MISSQL
Dim cmd As SqlCommand =
C1.CreateCommand(Strsql)
C1.CreateParam(cmd,
"ITTTTTTTTT")
cmd.Parameters("@P1").Value = DR("cu_id")
cmd.Parameters("@P2").Value
= DR("cu_fname")
cmd.Parameters("@P3").Value = DR("cu_lname")
cmd.Parameters("@P4").Value = DR("cu_add")
cmd.Parameters("@P5").Value = DR("cu_occ")
cmd.Parameters("@P6").Value = DR("cu_wplace")
cmd.Parameters("@P7").Value
= DR("cu_nat")
cmd.Parameters("@P8").Value = DR("cu_tel")
cmd.Parameters("@P9").Value = DR("cu_fax")
cmd.Parameters("@P10").Value = DR("cu_email")
Dim Y As Integer = C1.Execute(cmd)
If Y > 0 Then
DR.AcceptChanges()
Else
Me.MyError =
"Can not register"
End If
<---------code above in this sub it can update only customers tables and when I tried to coding below<------------it alerts can not update
Dim DT1 As DataTable = DS.Tables(1)
Dim DR1 As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.Added
Then
If
txtcu_id.Text.Trim = "" Then
Me.MyError = "Please enter id"
Exit Sub
End If
DR1("cu_id")
= txtcu_id.Text
End If
If Label2.Text.Trim = ""
Then
DR1("op_date") = Label2.Text
End If
Dim StrSql1 As String
If DR1.RowState =
DataRowState.Deleted Then
StrSql1 =
"insert into open_ac (cu_id,op_date) values (@P13,@P14)"
Else
StrSql1 =
"update open_ac set op_date=@P14 where cu_id=@P13"
End If
Dim C2 As New MISSQL
Dim cmd2 As SqlCommand =
C2.CreateCommand(StrSql1)
C2.CreateParam(cmd2, "ID")
cmd2.Parameters("@P1").Value = DR1("cu_id")
cmd2.Parameters("@P2").Value = DR1("op_date")
Dim Y1 As Integer = C2.Execute(cmd2)
If Y1 > 0 Then
DR1.AcceptChanges()
Else
Me.MyError =
"Can not register"
End If
End Sub
End Class
<------this
is class I use for connecting to database and call parameters....
MISSQL
class
Imports
System.Data.SqlClient
Public Class MISSQL
Dim PV As String =
"Server=web_proj;uid=sa;pwd=sqldb;"
Dim m_Database As String = "c1_itc"
Public Strcon As String
Public Sub New()
Strcon = PV &
"database=" & m_Database
End Sub
Public Sub New(ByVal DBName As String)
m_Database = DBName
Strcon = PV &
"database=" & m_Database
End Sub
Public Property Database() As String
Get
Return
m_Database
End Get
Set(ByVal Value As String)
m_Database =
Value
Strcon = PV
& "database=" & m_Database
End Set
End Property
Public Function GetDataset(ByVal Strsql As String, _
Optional ByVal DatasetName As String
= "Dataset1", _
Optional ByVal TableName As String =
"Table") As DataSet
Dim DA As New SqlDataAdapter(Strsql,
Strcon)
Dim DS As New DataSet(DatasetName)
Try
DA.Fill(DS,
TableName)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DS
End Function
Public Function GetDataTable(ByVal Strsql As String, _
Optional ByVal TableName As
String = "Table") As DataTable
Dim DA As New SqlDataAdapter(Strsql,
Strcon)
Dim DT As New DataTable(TableName)
Try
DA.Fill(DT)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DT
End Function
Public Function CreateCommand(ByVal Strsql As String) As
SqlCommand
Dim cmd As New SqlCommand(Strsql)
Return cmd
End Function
Public Function Execute(ByVal Strsql As String) As
Integer
Dim cmd As New SqlCommand(Strsql)
Dim X As Integer = Me.Execute(cmd)
Return X
End Function
Public Function Execute(ByRef Cmd As SqlCommand) As
Integer
Dim Cn As New SqlConnection(Strcon)
Cmd.Connection = Cn
Dim X As Integer
Try
Cn.Open()
X =
Cmd.ExecuteNonQuery()
Catch
X = -1
Finally
Cn.Close()
End Try
Return X
End Function
Public Sub CreateParam(ByRef Cmd As SqlCommand, ByVal
StrType As String)
'T:Text, M:Memo, Y:Currency,
D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture
Dim i As Integer
Dim j As String
For i = 1 To Len(StrType)
j =
UCase(Mid(StrType, i, 1))
Dim P1 As
New SqlParameter
P1.ParameterName = "@P" & i
Select Case
j
Case "T"
P1.SqlDbType = SqlDbType.NVarChar
Case "M"
P1.SqlDbType = SqlDbType.Text
Case "Y"
P1.SqlDbType = SqlDbType.Money
Case "D"
P1.SqlDbType = SqlDbType.DateTime
Case "I"
P1.SqlDbType = SqlDbType.Int
Case "S"
P1.SqlDbType = SqlDbType.Decimal
Case "B"
P1.SqlDbType = SqlDbType.Bit
Case "P"
P1.SqlDbType = SqlDbType.Image
End Select
Cmd.Parameters.Add(P1)
Next
End Sub
End Class
<-------Thank you in advance<-------and Thank you very much for all help
View 2 Replies
View Related
Aug 23, 2007
I am running SQL Server CE on Visual Studio 2005.
I have created 2 tables and wish to establish a relationship between the two tables.
However, I could not find a proper way to establish the connection.
Anyone can provide some help on this?
thank you.
View 2 Replies
View Related
Mar 16, 2007
I'm building a Search Function with different pull down options. I have 9 Tables and 6 of them are not related.
How do I build an effecient SELECT?
Thanks I'm somewhat new to MSSQL.
View 14 Replies
View Related
Apr 9, 2007
Hi
I have two database as Malathi,Indoo
In Malathi database i have
Employee Table AS
----------------------
Eid Int (PK)
Enam varchar(50)
In Indoo Database I have
Job table as
Eid int (FK)
Jid Int (PK)
Now how can i create relation b/w two tables of different database
Any One can help for this query
Malathi Rao
View 3 Replies
View Related
Jan 13, 2004
Hi, I am trying to create a create for two table A and table B which have no relationship each time. For TableA, there are 3 columns like ID, APoints1, APoint2. For Table B, there are also 3 columns as ID, Qty, BPoints. There is no internal relationship for these two tables. But there may be same ID inside A and B for some records. Now I want to create a datagrid for displaying the information as :
ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID
Please Notice that I can't use directly SQL script as following from table A and table B because there is no relationship for Table A and Table B, otherwise the recult set would be wrong:
Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID group by A.ID
May I know is there solution for it?
Thank you very much!
View 4 Replies
View Related
Mar 20, 2007
Inner join vs outer join:
Inner join will remove rows from the first table if there is not a corresponding row in the 2nd table. Outer join will keep the rows, and use all NULL values to fill in the gaps in the 2nd table.
In general outer joins are more likely to grow a result set and inner joins are more likely to shrink one (though that will depend on the data and the query). For this reason, it is usually better when building complex queries to try to do your inner joins before your outer joins, because then you will keep your result set smaller for longer and thus the later joins can run faster.
View 2 Replies
View Related
Feb 2, 2006
I am relatively new to MS SQL (not a novice, but hardly a master).
I am working on a content management application for a magazine publisher. It’s written in ASP (VB Script) and being created on Dreamweaver 8 with an MS SQL 2000 database. Now, I’m trying to decide the best and fastest approach to coding a complex INSERT and UPDATE function. My question isn’t as much about the SQL (although that will probably come up after I decide how to do this), but about the procedural steps and approach I shuold be taking to do this.
Reporters will use an online form to enter their story into the system. It collects the usual data: Headline, byline, story content, and the story category (feature, opinion, entertainment, business, sports, etc.). Each story may belong to MULTIPLE categories (feature & business, for example).
So, I’ve created three tables to support this many-to-many realtionship:
Story
Category
StoryCat (a junction table with the IDs from both the other tables).
The online form has a dropdown menu which pulls the available categories from the Categories table. When the reporter has entered the data I use ASP to performs the insert just as you would expect it to.
The next step needs to be to update the StoryCat table so that it creates a new record with the StoryID of the record it just inserted, along with the CategoryID that was in that record.
------------------
As I said, I’m not sure of the best way to do this.
Should I just pull back the last record inserted and then create a procedure that would insert into the StoryCat table (which is what I’m thinking of doing on the confirmation page), or is there another approach I should take (perhaps some sort of temporary table or stored procedure?).
Any and all help will be greatly appreciated.
View 1 Replies
View Related
Jun 11, 2007
Hi!
It's been always said that it is best to put index on commonly joined fields in the table. But putting too much index on the table would cause the table to be slow on insert and update.
My question is, how do you deal with your fields that uses look up tables? Like for example for these fields
- CountryID(smallint)
- CreatedBy(int)
- ModifiedBy(int)
- Status(tinyint)
Those fields don't come a big part in the table, though when I query the table I always join them with their respective primary table to get their respective text value. Do I still need to put Index & FK relationships to these fields?
What fields are normally good candidates for index or fk relationships?
Thanks,
Enzo
View 6 Replies
View Related
Jul 30, 2007
Hi all,
I just ran across an issue on a SQL 2000 sp4 db where RI was being maintained solely with triggers. I am attempting to change the primary key of a parent table and cascade the results to all its children without using the vendor-supplied trigger code (long story...) using an INSTEAD OF trigger.
My question is: does SQL Server create any kind of relationship between the inserted and deleted tables that I could exploit since the key field is unavailable?
I am trying to avoid having to add a surrogate key to each of the children just for this activity (as there are many M rows in each and no other suitable unique column combinations that span all the child tables).
-DC
View 5 Replies
View Related
May 25, 2015
I have two tables, Inspections and InspectionDetails. The InspectionDetails only contains information if an inspection found violations. Some inspections don't look for violations so inspectiondetails would have no entry.How can I create a 0-Many relationship between these two tables?Inspection Table has : InspectionID, InspectionDate, TypeofInspection, Inspector..InspectionDetail Table has : DetailID (an auto-incrementing identity column), InspectionID, SectionViolated, ViolationDescription, InspectorNotes.
View 6 Replies
View Related
Apr 27, 2004
Hi,
I'm trying to make relationship between two tables "reservation" and "charges". The column is "booking_ticket". Its giving me following error :
'reservations (akr)' table saved successfully
'charges (akr)' table
- Unable to create relationship 'FK_charges_reservations'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_charges_reservations'. The conflict occurred in database 'limp', table 'reservations', column 'booking_ticket'.
I used to make relationship before , but never found this problem.
Kindly guide me to solve it.
Regards,
View 1 Replies
View Related
Jul 23, 2005
I need to create a relationship between a local table and tables on alinked server. I used the design table wizard and selected therelationship property wizard. In the reslationship property wizard,the tables that I need to get the keys from in the linked server do notshow up. Is there a way to do this, or I simply don't have enoughpermission to tables in the linked server. On the local server, theSecurity tab of linked server property has Local Loging "sa", RemoteUser "sa" and Remote Password "****". Thanks for your help.
View 1 Replies
View Related
Mar 20, 2008
Hello,
I have two databases in sql server. I'll call them DB1 and DB2. I have a table in DB2 that needs to form a relationship with a table in DB1. When I attempt to add a relationship I only see tables in DB2. Can this be done?
Thanks,
Mark
View 10 Replies
View Related
Sep 8, 2015
I am developing a database in PowerPivot and I am wondering how to create many relationships between the same 2 tables. All relationships must be active.
Let me give you a DUMMY example: let's say that the database has 2 tables, the Employee table and Manager table:
->Employee Table: Employee_name, Previous_Manager, Current_Manager
->Manager Table: Manager_Name
Because I have 2 manager fields in the employee table, I need to create 2 links between the employee and manager tables:
-> Link 1: Previous_Manager ---- Manager_Name
-> Link 2: Current_Manager ---- Manager_Name
Right now, one of the links is inactive...
Is there a way in PowerPivot to create 2 active links like that ?
I have Power Pivot version 11.0.3000.0 on Excel 2010 on Windows 7
View 5 Replies
View Related
Jun 15, 2006
Supose I have two records in a parent-child relationsuip (actually I have many more such records). Does SSIS offer any support ask for inserting one record into the parent table, the other into the child table, and updating the foreign key of the child to point to the parent?
TIA,
Barkingdog
P.S. I have to do just this as part of the datawarehouse test I'm running. Seems like a common task but I don't recall anything in SSIS addressing the issue.
View 5 Replies
View Related
Aug 9, 2006
I used a function to create dataset as below:
Public Function GetSQLDataSet(ByVal SQL As String) As DataSet
......
MyConnection = New SqlConnection(MyConnectionString)
MyCommand = New SqlCommand(SQL, MyConnection)
MyDataSet = New DataSet
MySQLDataAdapter = New SqlDataAdapter(MyCommand)
MySQLDataAdapter.Fill(MyDataSet)
......
End function
It works fine.
How to code a function to return a dataset in which there are two tables and relationship?
View 1 Replies
View Related
May 26, 2004
Hi All,
Currently i am defining a simple relationship between
Customers->Orders->Order Details through the Database Diagrams feature in
the SQL 2K. Using the Server Explorer, i can see the Database Diagrams, but
when i try to "drop" the Database Diagrams into the page, it gives the error
message.
I would like to know the procedures to retrieve the database relationships
from Database Diagrams and manipulate them through ADO.NET
I prefer to "convert" already defined relationship using SQL Server Database
Diagrams into XSD file or probably there is another method to "read" those
relationship and manipulate them.
Thank you very much for all your help
View 3 Replies
View Related
May 12, 2014
I would like to create a auto relationship between tables.
Currently I am using Northwind DB with tables (Orders, OrderDetails, Customers)
Orders ( OrderId, Customerid)
OrderDetails(OrderId)
Customers(CustomerID)
Now, if the user wants to generate a relationship automatically based on SAME FIELD Names.
What is the approach?
View 9 Replies
View Related
Jul 31, 2014
I have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.
for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score
and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error
Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)
Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );
I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.
View 5 Replies
View Related
Nov 13, 2006
I am trying to insert data into two tables with a SSIS package. One table has a foreign key relationship to the other table's primary key. When I try to run the package, the package will just seems to hang up in bids. I have found two ways around the issue but I don't like either approach. Is there a way to set which table gets insert first?
If I uncheck the check constraints option on the child table, the package will run very quickly but this option alters the child table and basically disables the constraint. I don't like this option because it is altering the database.
The second approach is to set the commit level on both tables to say 10,000 and make sure that the multicast component has the first output path moved to the parent table. I don't like this option because I am not sure if the records are backed out if the package should abend after records have been committed.
View 1 Replies
View Related
Sep 7, 2006
HII am having one table.in that columns are trid and remarks.the data is:trid | remarks----------------------1 travel:comment11 travel:comment21 emp:emp comment11 emp:emp comment2 so i want to display query like in this below format:having 3 columns,i.e trid, travel remarks, empremarks and travelremarks should display data start with 't' , empremarks data should display data starts with 'e'. I am getting data start with 't' and 'e',but how to combine travel data, emp data. and it should display in one row itself instead of 4 rows for every trid '1'.trid | travelremarks | emp remarks-----------------------------------------------------------------------------------------------------------------------------------------------1 travel:comment1,travel:comment2 emp:emp comment1,emp:emp comment2 please help me. Thanks,swapprose.
View 19 Replies
View Related
Nov 19, 2003
I have spent several hours hurting my brain tring to solve the following. I am hoping one of you SQL gurus can help me out.
I have two tables (each with two fields):
Group with GroupName and GroupDescription
Here is some sample data:
HR HumanResources
IT Information Technology
Boston BostonOffice
NJ NewJerseyOffice
GroupMember with GroupName and UserID
Here is some sample data:
IT CMessineo
NJ CMessineo
Boston JSmith
IT JSmith
What I want is a single stored procedure that when passed a UserID will return a result set that lists all the groups and a 1 or ) if the UserID is a member of that group.
For example calling the procedure and passing CMessineo would produce this result:
HR 0
IT 1
Boston 0
NJ 1
Can this be done in a stored procedure?
This is what I have so far, but I am in over my head:
(
@UserID varChar(40)
)
As
SELECT"GROUP".GROUPNAME, (SELECT ???
FROM"Group", GroupMember
Where"Group".GroupName = GroupMember.GroupName and GroupMember.UserID = @UserID)
FROM"GROUP"
Thanks in advance,
Chris
View 1 Replies
View Related
Jun 6, 2015
I want to create a table with primary key , and put relationship with second table.
View 5 Replies
View Related
Nov 8, 2006
Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.
I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.
Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).
Thanks!
View 6 Replies
View Related
Mar 25, 2006
How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram?
For example:
I have 2 tables, tbl1 and tbl2.
tbl1 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl2 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl1_id {uniqueidentifier} as FK linked to tbl1.id
If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?
mradlmaier
View 3 Replies
View Related
Oct 14, 2015
I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.
Operation (Commodity, OperationKey) ==========>
Commodity (Commodity)
/
/
|
|
Advice (OperationKey) ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)
I have the following measure:
Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
View 9 Replies
View Related
Aug 2, 2004
Masters,
The below queries return the data that I seek, but I have no idea how to combine them into a single query.
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [0 to 30]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '0' and '30'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [31 to 60]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '31' and '60'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [61 to 90]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '61' and '90'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [90+]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) > '90'
Any assistance that can be provided will be greatly appreciated.
Grasshopper
View 1 Replies
View Related
Oct 19, 2015
I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.
Month Count
July
August
September
October.
View 3 Replies
View Related
Mar 28, 2000
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list
I have difficulty combining a select statement with a where clause stored in a variable inside a cursor
The users select the mail content and frequency of delivery and i deliver the mail
I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.
Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work
For example
DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3
I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary
How do I combine the select statement with the where clause?
Help me...I need help urgently
View 1 Replies
View Related
Jan 28, 2008
Hi all,
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=======================================
-- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=========================================
-- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?
Please help and advise.
Thanks in advance,
Scott Chang
View 6 Replies
View Related
