Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?
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
ADVERTISEMENT
Jun 6, 2015
I want to create a table with primary key , and put relationship with second table.
View 5 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Apr 11, 2006
Hello again,
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Thank you for your assistance.
View 1 Replies
View Related
Oct 31, 2015
Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?
For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.
View 3 Replies
View Related
Apr 23, 2015
I've attempted to identify a primary and foreign key in these two tables, but I am getting a bunch of errors re duplicate keys and column names needing to be unique.Perhaps the primary and foreign key I have identified don't meet the criteria?
CREATE TABLE StockNames
(
-- Added Primary key to [stock_symbol]
[stock_symbol] VARCHAR(5) NOT NULL CONSTRAINT PK_stock_symbol PRIMARY KEY,
[stock_name] VARCHAR(150) NOT NULL,
[stock_exchange] VARCHAR(50) NOT NULL,
[code].....
View 19 Replies
View Related
May 30, 2007
How do I create a table with a foreign key
View 6 Replies
View Related
Dec 15, 2014
I'm trying to create a table in Microsoft Server Management Studio 2012. The table has two fields which are both foreign keys.
I created the following:
create table tblRoomEquipment(
RoomID nvarchar(8),
EquipmentType nvarchar(1),
foreign key (RoomID) references tblRoom(ID),
foreign key (EquipmentType) references tblEquipment(Type)
)
Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!
Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.
View 6 Replies
View Related
May 21, 2008
i'm using sql express, management studio express and a visual web developer starter kit.
i have 4 tables: items; categories; categorization; old_items
the old_items table has both the item id and category fields in it, the new db has them separated: items.id; categories.id; categorization.itemId, categorizaton.parentCategoryId, both of which are foreign keys.
i need to get the old_item.id and old_item.category values into the new categorization table but it keeps choking on the foreign keys and i can't get past it. as far as i've been able to figure out so far, i need to turn off the foreign keys to do the insert but i'm not sure and i haven't been able to find any sql query examples that demonstrate it or explain it well enough for my n00b self.
i've read http://msdn.microsoft.com/en-us/library/10cetyt6.aspx, "How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements" but i don't get how that affects it, it seems like one of the other options would actually disable them.
can anyone help? i've been trying all the permutations of queries i can think of and not getting it.
thanks.
View 5 Replies
View Related
Apr 6, 2007
Hi GuysOff late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?Thanks
View 2 Replies
View Related
May 11, 2007
I'm using MS SQL Server Studio Express 9.0. When selecting SQL Server Compact Edition, I'm able to create tables and primary keys with no problem. But how do I create a relationship with another table (foreign key)?
Bob K.
View 11 Replies
View Related
Oct 19, 2005
I have an application in which i need to get the foreign key fieldsfrom a table and then get all the foreign keys primary key field fromthe linking table. Could some one tell me how i do this usingINFORMATION_SCHEMA. I have tried and can get the foreign keys but notsure how to get the associated primary keys.
View 1 Replies
View Related
Oct 2, 2015
In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitiions:
CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT NULL,
[schoolID] [int] NOT NULL,
[number] [varchar](10) NOT NULL,
[lockID] [int] NULL
CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
[code]....
The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.
Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.
I have sql that looks like the following so far:
declare @SchoolID int = 999
insert into test.dbo.Locker ( [schoolID], [number])
select distinct LKR.schoolID, A.lockerNumber
FROM [InputTable] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID
AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID)
order by LKR.schoolID, A.lockerNumber
I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables?
View 7 Replies
View Related
Oct 2, 2015
In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitions:
CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT NULL,
[schoolID] [int] NOT NULL,
[number] [varchar](10) NOT NULL,
[lockID] [int] NULL
CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
[Code] ....
The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.
Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.
I have sql that looks like the following so far:
declare @SchoolID int = 999
insert into test.dbo.Locker ( [schoolID], [number])
select distinct LKR.schoolID, A.lockerNumber
FROM [InputTable] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID
AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID)
order by LKR.schoolID, A.lockerNumber
I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables? Thus can you either modify the sql that I just listed above and/or come up with some new sql that will show me how to accomplish my goal?
View 7 Replies
View Related
Jun 22, 2001
I am having trouble dropping constraints(Primary and Foreign Keys). I would like to do so so I can truncate the tables and repopulate them. Any time I use the DROP CONSTRAINT #### on one table, I get an error message saying this is referenced in another table. Any help in how to drop the keys so I can truncate the tables in a database would be appreciated. I must be overlooking something simple. Thanks for the help.
Regards,
Mark
View 2 Replies
View Related
Oct 28, 2004
Be warned, index padding is not included in this, and I'm not sure the fillfactor setting is correct.
set nocount on
create table #PK(constraint_schema sysname not null, constraint_name sysname not null, sql varchar(4000) not null, constraint PK_#PK primary key clustered(constraint_schema, constraint_name))
create table #cols(constraint_schema sysname not null, constraint_name sysname not null, column_name sysname not null, ordinal_position int not null, constraint PK_#PKcol primary key clustered(constraint_schema, constraint_name, ordinal_position))
create table #FK(constraint_schema sysname not null, constraint_name sysname not null,
unique_constraint_schema sysname not null, unique_constraint_name sysname not null,
sql varchar(4000) not null, constraint PK_#FK primary key clustered(constraint_schema, constraint_name))
insert into #PK
select constraint_schema, constraint_name, 'ALTER TABLE ' + quotename(table_schema) + '.' + quotename(TABLE_NAME) +
' ADD CONSTRAINT ' + quotename(CONSTRAINT_NAME) +
' PRIMARY KEY ' + CASE WHEN si.indid<>1 THEN 'NON' ELSE '' END +
'CLUSTERED (>cols<) WITH FILLFACTOR=' + cast(si.OrigFillFactor as varchar) + ' ON ' + quotename(fg.groupname)
AS SQL
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN sysindexes si on TC.CONSTRAINT_NAME=si.name
inner join sysfilegroups fg on si.groupid=fg.groupid
WHERE CONSTRAINT_TYPE IN('PRIMARY KEY','UNIQUE')
insert into #fk
select c.constraint_schema, c.constraint_name, c.unique_constraint_schema, c.unique_constraint_name,
'ALTER TABLE ' + quotename(F.table_schema) + '.' + quotename(F.table_name) +
' ADD CONSTRAINT ' + quotename(F.constraint_name) +
' FOREIGN KEY(>cols<) REFERENCES ' + quotename(r.table_schema) + '.' + quotename(r.table_name) +
'(>rcols<)'
AS sql
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS F
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C ON F.constraint_schema=C.constraint_schema AND f.constraint_name=c.constraint_name AND F.constraint_type='FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS R ON R.constraint_schema=C.unique_constraint_schema AND r.constraint_name=c.unique_constraint_name AND r.constraint_type in ('PRIMARY KEY','UNIQUE')
ORDER BY F.table_name, r.table_name
insert into #cols
select constraint_schema, constraint_name, COLUMN_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
declare @ctr int, @max int, @delim varchar(1)
select @ctr=1, @max=max(ordinal_position), @delim='' from #cols
set nocount on
while @ctr<=@max
BEGIN
update P SET SQL=Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<')
FROM #PK P INNER JOIN #cols C ON P.constraint_schema=C.constraint_schema AND P.constraint_name=C.constraint_name
WHERE C.ORDINAL_POSITION=@ctr
UPDATE F SET SQL=Replace(Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<'), '>rcols<', @delim + quotename(r.column_name) + '>rcols<')
FROM #FK F INNER JOIN #cols C ON F.constraint_schema=C.constraint_schema AND F.constraint_name=C.constraint_name AND C.ordinal_position=@ctr
INNER JOIN #cols R ON F.unique_constraint_schema=R.constraint_schema AND F.unique_constraint_name=R.constraint_name AND C.ordinal_position=R.ordinal_position
select @ctr=@ctr+1, @delim=','
END
set nocount on
update #PK SET SQL=Replace(SQL, '>cols<', '')
update #FK SET SQL=Replace(Replace(SQL, '>cols<', ''), '>rcols<', '')
select sql from #PK
select sql from #FK
drop table #pk
drop table #fk
drop table #cols
View 5 Replies
View Related
Jan 10, 2006
I have a DB with 100 tables. I was wondering if anybody knows a quick way to list primary and foreign key with the column name for all the tables.
Your help would make my life a lot easier
thanks
View 1 Replies
View Related
Nov 2, 2006
Hi all!
I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:
== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)
== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)
== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)
The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?
Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?
Any ideas are appreciated.
Thank you,
Ben
View 1 Replies
View Related
Nov 17, 2005
Ok, so I've broken down and decided to write myself an invoicing
program. I'd like to use GUID fields as the Primary Keys in my
table. Now, I basicly bill for two seperate items:
Deliverables and Services.
So, my Layout's gonna look something like
Create Table Invoice(
ID UniqueIdentifier Primary Key,
-- Other Data
);
Create Deliverable(
ID uniqueidentifier Primary Key,
ParentInvoice uniqueidentifier,
-- Other data);
--...
Im sure there are probems with that as it's written feel free to edify me as to what they are.
my questions are such:
1) Does a uniqueidentifier field automagically get a value?
2) If not how do I generate one in TSQL?
3) If so, what do I use to store my Foreign Keys.
4) How do I declare my Foreign key constraints?
View 12 Replies
View Related
Sep 13, 2006
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies
View Related
Jan 7, 2008
hi.
How to update FormA table from customer table. Let say i wish to keep small number of fields from each table so i use foreign keys as reference.
However i had a problem when i tried to save the relationships of both tables, i receive the error that FormA_id is not able to insert null into value.
Cust_id(PK) is identify column, as well FormA_id(FK) and FormA_id(PK) too. For example, when i insert a record from customer table, it will automatically create id for FormA.
Table structure. Customer
cust_id(PK),name,age,formA_id(FK)
Table structure, FormA
formA_id(PK), info, date,
How to solve ?
View 1 Replies
View Related
Nov 17, 2013
Our SQL 2008 R2 relational database has tables with foreign key relationships for part numbers. We receive production data from a separate program and we need to import the CSV data into our database application.
The problem is our separate program creates a CSV file with the actual part number "362S162-33". In our database we have a separate parts table (example: 362S162-33 has identity "15").
We need to import data into a production table that has a "part number" (FK) column.
How can we, when importing, cross-reference the "parts table" to convert the part number to the identity number. We have thousands of parts, so we need this change of part number column to the FK identity automatically on import.
Production Table:
idComponent (PK), [1000]
ComponentName, [Assembly108]
idPartNumber (FK), [15]
ComponentLength, [230.5]
UserMessage, [Assembly is 230.5 inches using 362S162-33]
Qty; [1]
View 4 Replies
View Related
Oct 3, 2007
I've imported data from an Excel spreadsheet to a table that has fields to match the destination table I'm trying to populate. The destination table has an Insert trigger with several checks on certain fields to make sure they have corresponding records in other tables.
If I do a statement like
"INSERT INTO destinationTable
(
ItemId,
Product,
SuperID,
etc etc
)
SELECT * FROM oldtable"
it runs for a while then gives me error messages from the trigger and rolls back the Insert.
The trigger has code such as
"IF (SELECT COUNT(*) FROM inserted WHERE ((inserted.Product Is Not Null))) != (SELECT COUNT(*) FROM tblInProduct, inserted WHERE (tblInProduct.Product = inserted.Product))
BEGIN
(Error message code goes here)
END"
So, do I need to do an INNER JOIN to each of the related files?
When I try that, I get this error:
"Msg 121, Level 15, State 1, Line 2
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
Is SQL counting the foreign key fields as separate fields, or what?
View 6 Replies
View Related
Feb 12, 2008
I have a Brokers table and Trans (transactions) table. When I insert new transactions, I run a stored procedure or trigger to update the Brokers table since a transaction may have a new Broker. Is there an simpler way to do this than my query below?
INSERT INTO Brokers (Brokers.broker_id)
SELECT X.broker_id
FROM (SELECT DISTINCT broker_id FROM Trans) As X
WHERE NOT EXISTS (SELECT Brokers.broker_id FROM Brokers WHERE Brokers.broker_id = X.broker_id)
View 7 Replies
View Related
Aug 26, 2005
I have a setup with a bridge table. There are about 5 different tables
on one side of the bridge (all with compatable PK columns) one of which
is called 'mobilesub', and one on the other side called
'allcostcenters'. The bridge table is called 'subaccountcostcenter'.
I can enter data for mobilesub in the bridge table. But then when I try
to enter the info into the bridge table for any of the other tables,
such as localsub, there is a conflict like this:
INSERT statement conflicted with TABLE FOREIGN KEY constraint
'FK_subaccountcostcenter_mobilesub'. The conflict occurred in database
'test1', table 'mobilesub'.
The statement has been terminated.
Is there some rule against using a bridge table that references several
different tables, and I'm just not aware of it. Because I've done
everything I can to make sure the info from the different tables don't
conflict . . .
The same error comes up if I do the localsub table first--in that case
the foriegn key messing me up is FK_subaccountcostcenter_localsub. So
it's not something with the individual tables.
I need experienced advice lol
Thanks
View 1 Replies
View Related
Jan 21, 2005
I know altering the schema of system tables is a big no-no, but I was wondering if setting up a table that has foreign keys pointing to a system table is bad.
Basically what I'm refering to is in some cases I have CreationDate and CreatedBy fields in my tables that correspond to GETDATE() and USER_NAME() functions in insert statements....I want the CreatedBy field to be a valid SQL server DB username ... and not some unchecked string value (SYSNAME actually)
View 3 Replies
View Related
Feb 25, 2008
Hi. i am updating a field on a table i have called tblNavproductId. when doing the insert, i get the error message that you know, it is is violation of a foreign key constraint on NavId. i see the foreign key, so i know it is there and i understand that. i read the books online and here is the article;
http://msdn2.microsoft.com/en-us/library/ms186247.aspx
does anyone have a good example of this type of query? i am putting 990k+ records in at once, so would this be a bulk insert? Thanks.
View 3 Replies
View Related
Apr 29, 2004
I have a situation that I must resolve. I have a program being used by many but I had to create a new table to provide a new feature. The problem I have is this table must use the primary key from the parent table as its primary key, meaning when a user adds a new record to parent table, I need to instantly add the primary key to the child table. Now this was done in the program using sql statements, but I need to implement a trigger or such as to keep me from having to reinstall application on many computers.
basically person inserts new record, then I need to get the new primary ket and add insert it into the child tables. how can I do this with a trigger. I have tried to use an insert into statment with my trigger, but I can't seem to pass the parameters correctly.
CREATE Trigger dbo.Table_Borrower_Insert_Keys
ON Table_Borrower
AFTER INSERT
AS
begin
declare @bid as int
@bid = select MAX(BorrowerID)
FROM Table_SoldProgression
INSERT Table_SoldProgression(BorrowerID)
values (@bid)
end
GO
another attempt
CREATE Trigger dbo.Table_Borrower_Insert_Keys
ON Table_Borrower
AFTER INSERT
AS
INSERT Table_SoldProgression(BorrowerID)
values (select MAX(BorrowerID)FROM Table_Borrower)
GO
View 3 Replies
View Related
Oct 7, 2015
I'm shredding the below xml into relational tables. Each element of the xml has it's own table and there is a foreign key to join the tables, you can see this in the below picture. The process I follow is each relational table I always bring the nesecary xml and store it in the table and when shredding I always look at the parent table.So for example when processing the seat table, I use seat xml from the parent route table, also taking the ROUTEID from the route table. The reason I do this is all about taking the id from the previous step to create the relationships between the tables. without taking the xml down to the tables?The problem with this approach is I have xml stored in most tables and the tables are becoming very large.
<Route Type="OneWay" >
<Seat Type="FirstClass">
<Prices>
<Price Price="10" />
<Price Price="11" />
</Prices>
[code]....
View 4 Replies
View Related
Aug 13, 2007
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
View 7 Replies
View Related
Jul 24, 2014
I am trying to create a FK using a composite PK and here are the details that I want to achieve.
Table -A
Column A1 not null,
Column A2 not null
Primary key (A1, A2).
Table -B
Column B1 Primary key.
Column B2 not null
FK (B2) References A(A1).
When I try to do this I am getting some errors. Questions: First of all is this possible? if yes, then how I can create it.
View 1 Replies
View Related
Nov 17, 2003
I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?
thanks for your time,
Andre
View 1 Replies
View Related