Creating Tables
Nov 29, 2005
Chris writes "How do I create a copy of a table (not temporary).
In Oracle I do .
Create table newtable as select * from oldtable
This command copies data and column data types.
I tried entering
SELECT * INTO db2.dbo.newtable FROM db1.dbo.oldtable
in the query manager and it appears to create the new table, but it only seems to be a temporary table because when I leave query manager and check my data base it isn't there.
I have to convert a table that has about 150 columns and about 2 million rows. I rather not have to create the data types for 150 columns."
View 6 Replies
ADVERTISEMENT
May 6, 2002
I am new at the development stages of my DBA career and I am stumped on how to approach this problem, any help would be great.
Problem: Our web developer is designing a Software survey that ranks the difficulty of tasks within a software application like MS Word. Each of these tasks have a rank tied to them.
Here are the tables that he created.
Table 1
EmpID Text
123456 A1, A2, A3
Table 2
ValueID Text Rank
A1 Create Table 1
A2 Insert Query 5
In the text field of table 1 he inserts every task or valueid that the employee is able to do.
He needs to be able to select Empid, text(from table 2), and Rank. Based on need. An example would be 'Which employees can Insert a query'. And he would need to be able to look in the text string on Table 1 and find every A2 in listed then get empid and rank. I know that it can be done with full text searching, however I guess what I want to know is if there is another way to create tables that would jus make a join necessary where the value id can be tied to the empid for each task the employee is able to do?
I hope that this makes sense!!
View 1 Replies
View Related
Oct 29, 2005
I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 backend. I currently have a selections table in the front end file which theusers use to make selections of records. The table has two fields -- primarykey (which matches primary key of main, SQL Server table), and a booleanfield. The table is linked to the main table in a heterogeneous inner join.I'm looking to move the table to the back end, while still giving eachmachine a unique set of selections. Using one large table with machine nameas part of the primary key actually slows things down. So I'm consideringusing a series of tables, where each machine has its own table in the backend for selections. The machine name would be incorporated in the particularselections table name, and the front end link would be modified on the flywhen the database is opened to point to that machine's back end selectionstable.This would require having about 50-100 individual selections tables in theback end database. Also, if a machine doesn't have a table when the databaseis opened on that machine, then that table would be created on the fly,populated, and pointed to via the ODBC link.Anyone see any problems with this approach, specifically creating the tableon the fly and then immediately using it, as well as having that many littletables running around? Thanks for any input!Neil
View 31 Replies
View Related
May 1, 2008
I'm transferring data from DB2 to SQL Server - the data gets wiped every night and reloaded. To keep up with any changes in the tables and to reduce the amount of maintenance on keep field & table definition updated, it would be nice to be able to create the tables in SQL Server in the SSIS package, based on the DB2 definition.
I've noticed it can be done in design mode when creating the OLE DB Destination (clicking on the 'New' button), but I'm not sure how I would automate this...
any suggestions?
Thx
View 4 Replies
View Related
Apr 10, 2006
I am trying to go through an arraylist and create some database tables for each entry in the array. what I have is
Dim ques As String
For Each ques In questions
query = "create Table " + ques + " (plantid nvarchar(100), Answer nvarchar(100))"
cmd = New SqlCeCommand(query, con)
cmd.ExecuteNonQuery()
Next
I am wanting to use the item in the arraylist as the name of the db. I am getting an error saying
There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = 1 ]
Can anyone see what I am doing wrong.
View 5 Replies
View Related
Apr 27, 2007
I have made a new table called 'customer' which i wish to tie into the userId of the db. I have used a db diagram to do this (there are keys on each side of the link and userId is the FK) . When i put the membership/profile view onto the form alongside the new customer table nothing displays in the customer table when i run the app., i dont even see the titles - any ideas (i'm new to this sql malarky btw - so its probably something unbelievably straightforward). Any help appreciated.
Thanks
View 2 Replies
View Related
Oct 16, 2007
hi all,
How can i create a new data table automatically for new users signing into the website.......what i mean here is that i have a predefined database table and i want that users signing in can have that table for them unique , so that they can fill data for themselves and that data will be visible to all just like forums
View 1 Replies
View Related
Apr 7, 2008
Hi All,I will like to create a table from two tables this (explained below) way using a stored procedure. Basically i want the stored procedure to return me the third table but i dont know how to do it.Table ATopicID Topic ----------------------------- 1 Sometopic 12 Some topic 23 Some topic 34 Some topic 4 Table BRateID Rate TopicID---------------------------------------1 5 12 5 13 5 24 4 25 4 36 3 5 Table CTopicID Topic Rate--------------------------------------------1 Some topic1 52 Some topic 2 5 3 Some topic 3 5Basically i have a table that stores topics associated with a particular subject. Those topics are stored in table A above. My users can just read the messages associated with the topic and rate that message. The rating is then stored in table B.Now i will like my users to search for topics with a particular word or sentence in table A and the topics that are returned have a rating they specify. My procedure takes the word or sentence to search in table A and and the rating which will be used in table B. I will like to construct another table that has this newly searched results and return it using a stored procedure or anything simple. I dont know how to do this because i have very little knowledge in stored procedures or sql..... Any help(Code) will be greatly appreciated...
View 11 Replies
View Related
Feb 18, 2004
Hi,
I need to create a table and a few fields in the SQL database programmatically in asp.net.
I know the tabes name, the server which it resides on, username/password. Would anyone have a small sample of this code?
Thanks
rad
View 9 Replies
View Related
Sep 22, 2006
hello
I am working with an existing database and there is no Foreign key between 2 tables
how can i create a FK after , when the tables are allready full ?
product :
product_id
report_id
name
report :
report_id
dateR
i want to create a FK on product.report_id, and ON DELETE CASCADE
thank you
View 2 Replies
View Related
Feb 25, 2004
I'm currently writing a web application in Coldfusion which uses a Access 2000 db. I can create tables in SQL ok but am having problems with the Autonumber type. Any ideas?
View 8 Replies
View Related
Feb 6, 2008
I am very new to SQL Server 2005 so please excuse my mandune queastion.
First what I accomplished was I created several tables within SQL Server 2000 using the Database and Table Wizards. I also created all the relationships the same way. Next, I had SQL Server 2000 and had the tool reverse engineer the SQL for this new database with all the relationships.
My next step was to run this newly created SQL script on my local box with SQL Server 2005 (SMSS). What I did was create the database first and then assign the users to the new database. From here, I moved to running the sql script. While it was not 100% perfect, it did create all the tables and relationships with no problems.
Now, I am trying to accomplish the same process one a development instance of SQL Server 2005, but the script fails. The data base error says the new database created does not exist. When we go back and look at the database within SMSS, it is GONE!
I am at a lost at how to replicate the database I have on my local box. Any suggestions would be greatly appreciated.
Russ
View 2 Replies
View Related
Jul 20, 2005
I am having the following problem and any help would be GREATLYappreciated:In an application I am developing, at some points we create a newtable. When I create this table on another users box, I can not accessit from my box. In sql server I am dbo, but the table created by myapplication when run on a different box has an owner of : "FCxxxx". Ihave sent permissions on this thing to public, but I am still gettingan error when I try to query this thing from my application. And I cannot get query analyzer to recognize this thing. I can see it inEnterprise manager. I would think there is a way to handle this sortof thing. If anyone out there has done anything like this I would bemuch obliged for any ideas. Thanks.Sincerely,Ed HawkesJoin Bytes!
View 1 Replies
View Related
Dec 19, 2006
In sql server 2003 I would create tables with generated sql scripts.
This way I could easily create duplicate dev environments.
Now in SQL 2005 Dev edition I try to run my scripts to create tables.
I create a db [MSS].
They run successfully but there is no table created.
(example script)USE [MSS]
GO
/****** Object: Table [dbo].[KataNames] Script Date: 08/17/2006 13:28:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KataNames](
[Kata] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SequenceId] [int] IDENTITY(1,1) NOT NULL,
[Description] [char](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Style] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Any clue what else I have to do to let these scripts actually create tables or why if they run ok, where is my table ?
Thanks
Kim Allbritain
View 9 Replies
View Related
Jul 27, 2006
I had a SQL db that i copied all the tables into excel and it generated the quereies in order to create those tables....Is there anyway i could excute all those table creations at once or would it be easier to just write an application that does that for me????
View 5 Replies
View Related
Aug 15, 2015
I have data model as below....I have created a unique constraint as below;
CREATE UNIQUE NONCLUSTERED INDEX [UNQ_StaffIDEventID] ON [dbo].[tblStaffBookings]
(
[StaffID] ASC,
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
I now tried to create a link between two tables tblStaffBookings and tblEventStaffDisciplinary by dragging from tblEventStaffDisciplinary.StaffID to tblStaff Bookings.StaffID and it gives me error as below;What do I need to do to link tblEventStaffDisciplinary.StaffID to tblStaffBookings.StaffID?
View 15 Replies
View Related
Jul 31, 2006
Hello,I need to create around 1500 similar tables.Does anyone know how to create them all at once instead of one-by-one?thanks
View 8 Replies
View Related
May 26, 2007
Hi, I have limited control on the server I use. I can not create data bases but can add tables and edit the data base that has been created for me. So what I would like to do is be able to use a tool such as aspnet_regsql through SQL Server Express to add the tables that Membership would automatically create. Any help in pointing me in the right direction would be great
Thanks
howlinhuskie
View 6 Replies
View Related
Feb 27, 2002
Does anyone know if it's possible to create a trigger on the sysdatabases table in the master database? I keep getting permission denied which I'm not sure is right.
View 2 Replies
View Related
Mar 1, 2002
Hi,
Does anyone know of a way I can create a trigger on a system table (say sysdatabases in master). I know this is not supported but presumably there's a way it can be done by referencing it's equivilant in Information_schema somewhere.
I'm trying to write a script that will automatically set up a backup schedule for a database that has just been created. I was hoping the trigger would query the sysdatabases table for new database name entries, log necessary info in an audit table and then call a backup script to set up the schedule. Any ideas??
Thanks
Rob
View 1 Replies
View Related
Jul 22, 1998
Folks,
Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL
statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?!
Something like
alter table add
column+@i
The alter table statement is within a Cursor and ideally @i should increment each time and alter
the table to put in a new column.
If I went 3 times thru the loop, I should have
column1
column2
column3
added to the new table.
Any pointers would be greatly appreciated. Thanks much in advance.
View 1 Replies
View Related
Nov 17, 2004
Frenz,
I need to create tables that are session specific. i.e When I login to a database i have to create a Table that can be accessed across all the procedures and triggers. When I log out the Tables should be droped. I understand that Local Temporary (#) tables can be created in annonymus block, but I need the tables to be created during login.
Your response is highly appreciated.
-Cheeku
View 2 Replies
View Related
Jan 28, 2013
In SQL Server, I need to create a table from 3 different individual tables. I am new to the SQL Scene, so i want to know the best way to go about this. Each table has different fields, so that is making it difficult (at least for me). How about creating 1 table, that is composed of 3 other tables?
I know this is bad practice, but our District Manager wants to see production for his 'team' and whoever set this up back in the beginning gave each individual employee a different table, with custom fields (Why?!?!?!?!) so now I am trying to merge those 3 into 1, but want to get some insight on the best way to do this since there are different fields in each table and the merged (or master) table needs to have all of those fields.
View 11 Replies
View Related
Mar 3, 2015
I need to write create table statements for the er diagram that I attached. I am new to sql and I have trouble integrating foreign keys with these bigger er diagrams.
These are the tables I need to create:
Create Table Author(...)
Create Table Writes(...)
Create Table Book(...)
Create Table Copy(...)
Create Table Loan(...)
Create Table Customer(...)
View 1 Replies
View Related
Jun 12, 2007
Here I need to create a view by using following criteria, there is 3 tables which are Tbl.adminCategory, tbl_adminuser, tbl_outbox respectively. I am working on 2000SQL server
I am treing to create view as following but getting some error.
SELECT tbl_adminuser.adminUserName, tbl_AdminCategory.Name,
COUNT(tbl_outbox.msgUserID),
FROM tbl_adminuser INNER JOIN
tbl_AdminCategory ON
tbl_adminuser.adminUserID = tbl_AdminCategory.CatID INNER JOIN
tbl_outbox ON tbl_AdminCategory.CatID = tbl_outbox.msgUserID
AND tbl_outbox.msgUserID <> 0
GROUP BY tbl_outbox.msgUserID
But I am getting error pls correct the view,
thanks to all
View 4 Replies
View Related
Feb 3, 2008
I am hoping someone could lead me in the right direction on how to figure out why my version of SQL Server 2005 Standard Edition is not working correctly. Here is my issue.
1. I've created two users: normal and superuser and associated the user to the new database. (I don't see any problem here just wanted to state two new users were created.)
2. I've created a new database (TestDB) as sa using the default seetings.
3 Next I want to create new tables in the new database. I've right-clicked on tables directory under the new database for creating a new table inside the Object Explorer Window. I get the below error when I do
Object reference not set to an instance of an object. (SQLEditors)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)
I was thinking something was wrong with the database so I ran sp_helpdb <database_name> and I don't get an error.
Thank for taking the time to read my post. I appreciated your time and any suggestion you could offer.
Russ
View 1 Replies
View Related
Mar 11, 2008
Hi All,
In my SQL I am having temporary tables. And in Microsoft SQL Server Management Studio (Microsoft SQL Server 2005) whenever I execute sql statement its working fine & I am getting the records.
My SQL statement is using 2 databases as follows:
1.PerformanceDeficiencyNotice
2.HRDataWarehouse
Both the above databases are SQL SERVER 2000(80) with a compatibility level of 80.
The problem is when I am trying to create a new view with my sql statement and when I am saying “Verify SQL Syntax�, I am getting an error as “Invalid Object Name ‘#pdninfo’.
And when I am saying “execute SQL�, I am getting an error as “Unable to parse query text� but when I am continuing with the error, the sql statement is running and I am getting the data.
And now when I am trying to save the view I am getting the error as below
“Incorrect syntax near the keyword ‘INTO’�.
Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.
Please suggest how to solve this problem. Any help is greatly appreciated.
Thank You
MY SQL Statement is as follows:
SELECT
pdn.transactionid,
pdn.employeenbr,
pdn.lastname,
pdn.firstname,
pdn.processlevel,
pl.facilityname as processlevelname,
pdn.department,
pdn.jobcode,
pdn.title,
pdn.supemployeenbr,
pdn.managername,
pdn.timeframe as pdn_timeframe,
pdn.actualeffectivedate as pdn_startdate,
/*actualeffectivedate is the start date for the pdn. starteddate is when info starts being put in the system*/
/*the pdn end date has to be calculated for the pdn based on the timeframe and actualeffectivedate*/
case when pdn.actualeffectivedate <> convert(datetime,'01/01/1900',110) then
case pdn.timeframe
when '30' then dateadd(month,1,pdn.actualeffectivedate)
when '60' then dateadd(month,2,pdn.actualeffectivedate)
when '90' then dateadd(month,3,pdn.actualeffectivedate)
else null
end
end as pdn_enddate,
pdn.status as pdn_status,
status.description as pdn_statusdesc,
pdn.managersignoff as pdn_managersignoff,
pdn.managersignoffdate as pdn_managersignoffdate,
pdn.associatesignoff as pdn_associatesignoff,
pdn.associatesignoffdate as pdn_associatesignoffdate,
pdn.witnessname as pdn_witnessname,
/*the start date for the extension has to be calculated by subtracting 30 days from the evaluationdate*/
/*where the evaluationtype = 'X' (Extension Final).*/
/*there is only one timeframe of 30 days for an extension and only one extension is allowed per pdn for an associate*/
case
when (eval.evaluationtype = 'X' and eval.status not in ('C','D','N')) then dateadd(month,-1,eval.evaluationdate)
else null
end as ext_startdate,
eval.evaluationdate as eval_evaluationdate,/*end date of the evaluation or extension*/
eval.evaluationtype as eval_evaluationtype,
evaltype.description as eval_evaltypedesc,
eval.status as eval_status,
status2.description as eval_statusdesc,
eval.effectivedate as eval_effectivedate,
eval.managersignoff as eval_managersignoff,
eval.managersignoffdate as eval_managersignoffdate,
eval.associatesignoff as eval_associatesignoff,
eval.associatesignoffdate as eval_associatesignoffdate,
eval.witnessname as eval_witnessname
into #pdninfo
FROM [PerformanceDeficiencyNotice].[dbo].[PDNMain] pdn
left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationsMain] eval
on pdn.transactionid = eval.transactionid
left outer join [HRDataWarehouse].[dbo].[ProcessLevel] pl
on pdn.processlevel = pl.processlevel
left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status
on pdn.status = status.status and status.type = 'PDN'
left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status2
on eval.status = status2.status and status2.type = 'EVAL'
left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationTypes] evaltype
on eval.evaluationtype = evaltype.type
/*select active pdns from PDNMain (status: 'A' = Approved, 'S' = Submitted)*/
WHERE pdn.status in ('A','S')
/*select extensions from EvaluationsMain (evaluation type: 'X' = Extension Final; status: <> 'C' - Completed,*/
/*'D' - In Progress, or 'N' - Not started)*/
OR (eval.evaluationtype = 'X' and eval.status not in ('C','D','N'))
/*get last performance rating and last (maximum) performance review date from PerformanceReviewHistory*/
/*Note: A PerformanceReviewHistory record gets created within a couple of days after an associate is hired.*/
/* The rating and updatedate are null initially. Aggregate functions (i.e. MAX) ignore null values.*/
/* You must check for "updatedate IS NOT NULL" as shown below or the record will be dropped.*/
SELECT distinct(#pdninfo.employeenbr), perfreview.rating, perfreview.updatedate
into #perfreview
FROM #pdninfo, [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview
WHERE #pdninfo.employeenbr = perfreview.employeenbr
AND perfreview.updatedate =
(SELECT max(updatedate)
FROM [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview2
WHERE perfreview2.employeenbr = perfreview.employeenbr
AND updatedate IS NOT NULL)
/*select active pdns ('orig' = original)*/
SELECT 'orig' as orig_or_ext,
#pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate,
/*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/
emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi,
(SELECT emp2.lastname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname,
(SELECT emp2.firstname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname,
(SELECT emp2.mi
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi
FROM #pdninfo
left outer join #perfreview
on #pdninfo.employeenbr = #perfreview.employeenbr
left outer join [HRDataWarehouse].[dbo].[Employee] emp
on #pdninfo.employeenbr = emp.employeenbr
WHERE #pdninfo.pdn_status in ('A','S')
union
/*select extensions ('ext' = extension)*/
SELECT
'ext' as orig_or_ext,
#pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate,
/*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/
emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi,
(SELECT emp2.lastname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname,
(SELECT emp2.firstname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname,
(SELECT emp2.mi
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi
FROM #pdninfo
left outer join #perfreview
on #pdninfo.employeenbr = #perfreview.employeenbr
left outer join [HRDataWarehouse].[dbo].[Employee] emp
on #pdninfo.employeenbr = emp.employeenbr
WHERE #pdninfo.eval_evaluationtype = 'X' and #pdninfo.eval_status not in ('C','D','N')
drop table #pdninfo
drop table #perfreview
View 5 Replies
View Related
Mar 29, 2007
Hi,
A seemingly simple question: How do I create an Insert script from the data of a selected number of tables in a given database?
(The Insert script has to be part of a deployment phase)
Esben
View 3 Replies
View Related
Oct 16, 2007
Hey guys, I'm an old DevShed member, but my old account isn't working for some reason, so I had to recreate..
I've recently decided to learn MS SQL, and having some trouble with creating and populating tables. Using MS SQL Express 2005.
Heres the code, I keep reading through my notes on how to do it, but I cant see what I'm doing wrong. This is my first attempt at it, so there may be more wrong that I think.
Code:
drop table Property_rental;
drop table Property_type;
drop table Property_owner;
drop table Staff;
drop table Tenant;
drop table Tenant_category;
create table Tenant_category
(TCATID SMALLINT PRIMARY KEY NOT NULL,
TTYPE NVARCHAR(15))
;
create table Property_type
(PTYPEID SMALLINT PRIMARY KEY NOT NULL,
PTYPE NVARCHAR(20) NULL)
;
create table Property_owner
(POWNERID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20) NULL,
SNAME NVARCHAR(20) NULL,
CONTACT NVARCHAR(15) NULL,
ADDR NVARCHAR(50) NULL)
;
create table Staff
(STAFFID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20))
;
create table Property_rental
(ID SMALLINT PRIMARY KEY NOT NULL,
PTYPEID SMALLINT NOT NULL,
STAFFID SMALLINT NOT NULL,
POWNERID SMALLINT NOT NULL,
CONSTRAINT Prop_Type_fk FOREIGN KEY(PTYPEID) REFERENCES Property_type(PTYPEID),
CONSTRAINT Prop_Staff_fk FOREIGN KEY(STAFFID) REFERENCES Staff(STAFFID),
CONSTRAINT Prop_Owner_fk FOREIGN KEY(POWNERID) REFERENCES Property_owner(POWNERID))
;
create table Tenant
(TENANTID SMALLINT NOT NULL,
TCATID SMALLINT NOT NULL,
ID SMALLINT NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20),
COMMENTS NVARCHAR(20),
CONSTRAINT Ten_Cat_fk FOREIGN KEY(TCATID) REFERENCES Tenant_category(TCATID),
CONSTRAINT Ten_Prop_fk FOREIGN KEY(ID) REFERENCES Property_rental(ID),
CONSTRAINT Ten_pk PRIMARY KEY (TENANTID, TCATID, ID))
;
Error messages I'm getting;
Code:
Msg 547, Level 16, State 0, Line 55
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 56
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 57
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 58
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 59
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 60
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 61
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 82
The INSERT statement conflicted with the FOREIGN KEY constraint "Prop_Staff_fk". The conflict occurred in database "master", table "dbo.Staff", column 'STAFFID'.
The statement has been terminated.
View 6 Replies
View Related
Nov 13, 2004
Hi folks, i need ur guidance.
I have a few DDL and DML scripts which i want to be automatically applied during MSDE setup; or an .MDF file be attached automatically. Please suggest an easier way!
Howdy!
View 1 Replies
View Related
Nov 2, 2005
I'm trying to join 3 tables in an outer join since I am loosing records that need to be included if I only use an inner join. I am pulling data from an MSDE database using the microsoft query tool.
The problem is that I get the message that I can't use an outer join on a query with more than 2 tables, but that can't be right can it?
I'm a SQL code novice so any help would be greatly appreciated!
SELECT
Article.articleId
, Article.articleName
, Article.articleStatus
, Articlegroup_2.ArticlegroupId
, Articlegroup_2.g2_key
, Articlegroup_2.g2_name
, articleGroup.articleGroupId
FROM
HIP.dbo.Article Article, HIP.dbo.articleGroup articleGroup, HIP.dbo.Articlegroup_2 Articlegroup_2
WHERE
articleGroup.articleGroupId = Article.articleGroupId AND
Article.articleGroupId2 = Articlegroup_2.Articlegroup_2_Id
View 5 Replies
View Related
Dec 14, 2007
I'm trying to join 2 tables in an outer join, but MS Query won't let me do this because I have another 2 tables included in an inner join ("only two tables are allowed in an outer join"). I am pulling data from an MSDE database using the microsoft query tool.
I'm a SQL code novice so any help would be greatly appreciated!
Here is my existing SQL query (without the new outer join table):
SELECT
Lead_.Country, Lead_.Company_Name, Employee.Full_Name, Lead_.Rn_Create_Date, Lead_.Marketing_Project_Name, Employee_1.Full_Name, Lead_.Comments
FROM
ProductionED.dbo.Employee Employee, ProductionED.dbo.Employee Employee_1, ProductionED.dbo.Lead_ Lead_
WHERE
Employee.Employee_Id = Lead_.Account_Manager_Id AND Employee_1.Employee_Id = Lead_.Created_By_Employee_Id AND ((Lead_.Market_Segment='new'))
View 5 Replies
View Related
May 17, 2004
Hi
I have developed an application in ASP/SQL server 7.
Ths system is single user. One of the tables is updated by usr actions( say Table A).
To make it multi user. I want to create table such as A_Username.
How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username .
Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option?
pls suggest
View 6 Replies
View Related
