SQL Server 2012 :: Insert Foreign Key Value Into Primary Table?

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


ADVERTISEMENT

Transact SQL :: 2012 / Insert Foreign Key Value Into Primary Table?

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

SQL 2012 :: Insert Data Onto Tables Having Primary And Foreign Key Relations?

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

SQL Server 2012 :: How To Get Primary Key Value If Not Present In Foreign Key Via Join

May 4, 2014

I've following query which display the result as required, but I need to get the missing Primary Key Values which are not available in the result:

SELECTA.SignedByUserID, B.FullName, COUNT(A.OutletID) AS TotalSignups, DATENAME(Month, A.SignupDate) AS Month
FROMdbo.tblMer_Outlet AS A LEFT OUTER JOIN dbo.tblGen_Users AS B ON A.SignedByUserID = B.UserID
WHERE(A.SignupDate >= '2014-04-01 00:00:00' AND A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY A.SignedByUserID, B.FullName, DATENAME(Month, A.SignupDate)

This Query returns the following result:

SignedByUserID FullName TotalSignups Month
--------------------------------------------------------
9 Babu Raj 16 April
11 Faheem 19 April
39 Fasil Abbas 16 April
29 Hafiz Suleman 10 April

[code]....

which does not have a signup for the month of April, but I need it to be available for this or any upcoming month. I need this orr all users, which does not exists in the context needs to be displayed in the result.

View 9 Replies View Related

Import Csv Data To Dbo.Tables Via CREATE TABLE &&amp; BUKL INSERT:How To Designate The Primary-Foreign Keys &&amp; 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 View Related

SQL Server Admin 2014 :: Few Record Loss In Table Primary Key Where Same Records Exists In Foreign Key Table?

Jun 21, 2015

Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.

View 3 Replies View Related

SQL Server 2012 :: Efficient Way To Insert From Foreign Key Lookups

Feb 17, 2015

An SSIS task imports data from a flat file and inserts the data into a staging table. The staging table holds the data in its raw form. A second process then selects the data from the staging table, looking up the foreign key id's for raw data values, and then inserts the data into the live table.

SQL - Only key columns shown for clarity
-- Staging Table
CREATE TABLE Staging
(Information VARCHAR(10),
MachineName VARCHAR(10),
Status VARCHAR(10))

[code]...

The insert into the live table should look up the id for machine 1, and the id of status success and insert the foreign key values into the live table for the row.There could be 1000's of rows for the output of machine 1 all with different status's - (all pre set in the Status table, i.e success, failure, rerun) and the same for lots of other machines held in the machine table.

What is the best to insert this data all in one go, rather than reading each row of the staging table one by one, looking up the foreign key values depending on the machine and status values, then inserting the data.

I was thinking along the lines of:

INSERT INTO dbo.LiveTable
(Information, MachineID, StatusId)
SELECT Staging.Information, dbo.Machine.MachineId, dbo.Status.StatusId
FROM dbo.Staging
JOIN Machine ON Machine.MachineName = Staging.MachineName
JOIN STATUS ON Status.Status = Staging.Status But I notice the problem with this is, it doubles up the inserts!

View 2 Replies View Related

Reporting Services :: Copy Table From One To Another Server With Primary And Foreign Key Constraints

Jun 9, 2015

How to copy a table from one server to another server with primary and foreign key constraints.

View 5 Replies View Related

SQL 2012 :: Simple Way To Remember Primary And Foreign Key?

Dec 15, 2014

What is the simple way to remember Primary and foreign key?

Why is the ID column in a table declared as a integer datatype?

View 2 Replies View Related

T-SQL (SS2K8) :: Find Primary Key Table Name And Field Name Of Foreign Key

Apr 10, 2015

How can i find the primary field name and primary table name of a given foreign key.

For example

Table A:
IDa
Column1
Column2
Column3

Table B:
IDb
column1
column2
column3 <---- this is foreign key to table A's IDa

What i want to do is i pass input of tableB, column3 and i get name of Primary tableA and field name IDa. How is it possible to do in tsql ?

View 4 Replies View Related

Transact SQL :: Identify Primary And Foreign Keys In Two Table

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

Columns In Primary Table And Foreign Key Table

Feb 12, 2007

mahesh writes "HI,

I am new to sql server.

can anybody help me

I have a table named tblqualificationmaster.

can i know the foreignkeys and the table related to this

tblqualificationmaster having foeign keys using stored procedure."

View 1 Replies View Related

DB Design :: More Than One Table Primary Key Adding In Single Foreign Key Column

Nov 6, 2015

I have come up with one scenarios where I have three table like Product, Services and Subscription.  I have to create one table say Bundle where I can have some of the product id , service id and Subscription id ,  i.e. a bundle may contains sum prduct , services and subscription . How I can design these relations ?

View 3 Replies View Related

SQL Server 2012 :: How To Add A Primary Key For Existing Column In The Table

Oct 19, 2015

How to add a primary key for existing column in the table

View 8 Replies View Related

How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.

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

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

SQL Server 2012 :: Insert Into Table With Identity Columns From Another Table

Dec 23, 2013

I just created a new table with over 100 Columns and I need to populated just the first 2 columns.

The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.

column1 = ID
column2= P_CLIENT_D

SET IDENTITY_INSERT PIM1 ON

INSERT INTO PIM1 (P_CLIENT_ID)
SELECT
Client.ID
FROMP_Client

So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.

View 1 Replies View Related

SQL 2012 :: Find All Table Names That Have Foreign Key

Oct 20, 2015

Is there a way to find all the table names that have a foreign key that references FILE_ID_IN([FILE_ID])

Note: The table def below has a foreign key

IF object_id('CODE_MAP_IN', 'U') IS NOT NULL DROP TABLE CODE_MAP_IN
Print 'Creating table CODE_MAP_IN...'
Create table CODE_MAP_IN
(
[CODE_MAP_FR] [varchar] (53),
[CODE_MAP_TO] [varchar] (53),
[FILE_ID] [float] DEFAULT 1000
PRIMARY KEY ([CODE_MAP_FR]),
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
) ON [PRIMARY]

GO

View 2 Replies View Related

Insert Records Into A Table With Foreign Keys

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

Insert Values In Multiple Table With Foreign Key

Feb 28, 2008

Hi,

I've been working as web dev for quite sometime now but there are still few things that i would like to clarify. Hope you guys can shed lights.

I currently have several tables and all this tables are having some 1 to many relationships. I know that how i insert value into my tables are very inefficient. I do it in this manner..
-> Insert a value in TableA
-> Query the latest ID that i have inserted in Table A then
-> Insert this value in TableB. And so on and so forth.

I believe there is an efficient way to to do this but i'm not sure how? Could anyone shed me a light on this matter?

Your reply would be highly appreciated. TIA.

View 6 Replies View Related

SQL 2012 :: Create Table With Both Keys As Foreign (error)

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

SQL 2012 :: Script To Remove Any Foreign Characters From Table

Sep 14, 2015

I have a table that is riddled with weird characters. So far I have found an escape character for PDF files and a trademark sign. These characters are crashing my SSIS packages. I am able to remove these characters with an update script...

Update TABLE
set LEAD_NOTES__C = Replace(LEAD_NOTES__C, nchar(65533) COLLATE Latin1_General_BIN2, '!');

Update TABLE
set LEAD_NOTES__C = Replace(LEAD_NOTES__C, nchar(1671) COLLATE Latin1_General_BIN2, '!');

This works fine, but my question is...

I would like to write a script that removes all foreign characters with the exception of the normal characters like (@,#,$,%,etc). I need a dynamic process that handles this so I am not losing time sifting through over 20,000 rows of data and changing my update script to remove a specific column. Although this method works, I would prefer a dynamic query. I intend to wrap this in a stored procedure that loops through all columns in a table (as parameter).

View 4 Replies View Related

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem

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

The INSERT Statement Conflicted With The FOREIGN KEY Constraint 'Table'

Jan 11, 2008

 Hi,A conflict error occured when I insert data using Formview.The INSERT statement conflicted with the FOREIGN KEY constraint "FK_personal_personal_category". The conflict occurred in database "E:INETPUBWWWROOTWEBSITEAPP_DATADATABASE.MDF", table "dbo.personal_category", column 'cat_id'.The statement has been terminated. ----------------------------------------------------Personal TablepID (primary)pNamecat_id (foreign key from personal_category, which has a relationship with it's primary key cat_id)pID | pName | cat_id1 | Scott | 112 | John | 122 | Mick | 13-----------------------------------------------------Personal_Category Tablecat_id (primary)cat_Descriptioncat_id | cat_description11 | Dean12 | Student13 | Professor-----------------------------------------------------I'm inserting a new data on Personal Table while choosing a category usinga DropdownList which is binded to another DataSource (LinqDataSource2).... <asp:FormView ID="FormView1" runat="server" DataKeyNames="tut_id" DataSourceID="LinqDataSource1"            BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"            CellPadding="4" DefaultMode="Insert" ForeColor="Black" GridLines="Vertical">            <FooterStyle BackColor="#CCCC99" />            <RowStyle BackColor="#F7F7DE" />            <InsertItemTemplate>            <asp:TextBox ID="pName" runat="server" Text='<%# Bind("pName") %>' />            <br />            <asp:DropDownList ID="DropDownList1" runat="server"                  DataSourceID="LinqDataSource2" DataTextField="cat_desc" DataValueField="cat_id" DataSource='<%# Bind("tut_cat") %>' >            </asp:DropDownList>            </InsertItemTemplate>            <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />            <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />            <EditRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" /> </asp:FormView>        <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="PersonalsDataContext"            TableName="Personal" EnableInsert = "true">                     </asp:LinqDataSource>        <asp:LinqDataSource ID="LinqDataSource2" runat="server" ContextTypeName="PersonalCategoriesDataContext"            TableName="Personal_categories" EnableInsert="false" >        </asp:LinqDataSource>...  Hope you could help.cheers,imperialx 

View 2 Replies View Related

SQL 2012 :: Partition Existing Table On Foreign Key (datetime) Column?

May 28, 2015

Is it possible to partition an existing table on a foreign key (datetime) column? Also would partition switching work?

View 0 Replies View Related

SQL Server 2012 :: Insert Rows In A Table

Jul 28, 2015

In a t-sql 2012 sql script, I have the following script, that only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value or 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?

UDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
WHERE LKC.seq = 2

A normal select statement looks like the following:

select * from TST.dbo.Locker LKR
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
where LKR.number in (000,001,1237)

In case you need the ddl statements for the tables affected here are the ddl statements:

CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[number] [varchar](10) NOT NULL,
[serialNumber] [varchar](20) NULL,
[type] [varchar](3) NULL,
[locationID] [int] NULL,

[code]...-

View 1 Replies View Related

INSERT Statement Conflicted With COLUMN FOREIGN KEY SAME TABLE Constraint

Aug 1, 2006

For some reason, I'm getting this error, even without the DBCC Check:

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.

The very first insert fails...it was working fine before:


DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'


/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@@ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'


Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

View 8 Replies View Related

SQL Server 2012 :: Insert Rows Into A Table Without Dropping It

Sep 23, 2014

I am finding it difficult to find an example that allows for insertion of additional rows into a table, without dropping the table I'm inserting into. Or inserting specific values. Like this example..

[URL] ....

I have 6 table I am formatting the data to conform to the final table as I'm inserting it into, but none of these examples gives me the example needed. I am using SQL 2012.

<code>
SELECT
CONVERT(VARCHAR(50),[FName]) + ' ' + CONVERT(VARCHAR(50),[LName]) AS [CustName]
,CAST('ALARMCOM' as nvarchar(8)) as VendorName
,CONVERT(VARCHAR(25),[CUSTOMER_CS_ACCOUNT_NUMBER]) AS [Cust_ID]
,CONVERT(VARCHAR(40),[Charge_Description])as [ChargeType]
,CASE

[Code] ....

View 6 Replies View Related

SQL Server 2012 :: Insert Into Table Statement Dynamically

Apr 20, 2015

I am having 2 tables one is staging temp and another is main import table.

In my staging table there are 3 column Col001,Id,Loaddate

in Col001 column data are present with '¯' delemeter.

I am having function which is used to load data from staging to import table using one function.

this function create a insert statement.

My Existing function

-- Description: To Split a Delimited field by a Delimiter
ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter]
(
@fieldname varchar(max)
,@delimiter varchar(max)
,@delimiter_count int

[Code] ....

I am unable to get correct statement with above function.

View 1 Replies View Related

SQL Server 2012 :: XML File Insert Into A Table Field

Aug 31, 2015

I have been tasksed to create a data table and stored procedure to extract a special formatted XML file that is an attachment with a standard XML envelope. The XML file is an attchment in a node within the XML wrapper. There are other MIME files (pdf's ) that are handle by a seperate procedure. But I need to just extract the XML file attached along with those and put it into the datable with some other PK?FK fields.

Is a blob the best datatype. How to I insert that XML file into it?

View 2 Replies View Related

SQL 2012 :: Create Primary Key On A Table?

Apr 22, 2014

In what situations we can create primary key on a table? I mean what is the minimum no of rows we can prefer to create PK.

View 8 Replies View Related

SQL Server 2012 :: Insert XML (with Multiple Nodes ) Into Table With Query

Dec 9, 2014

My xml is in below format:

DECLARE @MyXML XML
SET @MyXML = '<RS>
<R id="6330">
<WF id="71445">
<WFS id="12790"> <fname>John12790</fname> </WFS>

[Code] ....

I need to insert into table with TSQL query:

My output should be similar below:

John12790 12790 71445 6330
Eric 12791 12790 71445 6330
John12793 12793 71446 6331
Eric12794 12794 71446 6331

otherwise i need to write huge c# code to execute such thing..

View 2 Replies View Related

SQL Server 2012 :: How To Get A Table Identity Inserted By Instead Of Insert Trigger

May 14, 2015

I have a problem described as follows: I have a table with one instead of insert trigger:

create table TMessage (ID int identity(1,1), dscp varchar(50))
GO
Alter trigger tr_tmessage on tmessage
instead of insert
as
--Set NoCount On
insert into tmessage

[code]....

When I execute P1 it returns 0 for Id field of @T1.

How can I get the Identity in this case?

PS: I can not use Ident_Current or @@identity as the table insertion hit is very high and can be done concurrently.Also there are some more insertion into different tables in the trigger code, so can not use @@identity either.

View 5 Replies View Related







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