Creating Tables On The Fly

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


ADVERTISEMENT

Creating Tables

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

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 View Related

Creating Tables On The Fly

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

Creating Tables

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

Creating New Tables In Aspnetdb.mdf

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

Creating New Tables For New Users

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

Creating A Third Table From Two Tables

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

Creating The Database Tables

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

Creating A FK On Existing Tables

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

Creating Access Tables With SQL

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

Creating Database And Tables

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

Creating Tables Within My Application.

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

Creating Tables With SQL Scripts

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

Creating Multilple Tables At Once

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

Creating Link Between Two Tables

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

Creating Multiple Tables?

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

Manually Creating Membership Tables

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

Creating Triggers On System Tables!!

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

Creating Triggers On System Tables

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

Dynamically Altering/creating Tables

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

Creating Temp Tables During Login

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

Creating Table From 3 Different Individual Tables

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

SQL Server 2008 :: Creating Tables From ERD

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

Creating A View By Using Multi Tables

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

Creating Tables Via Management Studio

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

Creating View Help With Temporary Tables

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

Creating Insert Scripts From Tables

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

Beginner: Trouble Creating And Populating Tables

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

Creating Custom Tables During MSDE Setup!

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

Need Help Creating Outer Join On Multiple Tables

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

Need Help Creating Outer Join On Multiple Tables

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

Creating /accessing Tables With Dynamic Names

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







Copyrights 2005-15 www.BigResource.com, All rights reserved