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
ADVERTISEMENT
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
Jan 30, 2014
I am trying to insert values in a single table with four columns from 4 different sources. is it possible to run these 4 insertions in parallel. all these insertion are independent of each other
View 3 Replies
View Related
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Aug 12, 2014
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies
View Related
Jun 19, 2008
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
View 6 Replies
View Related
Feb 24, 2015
I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
View 8 Replies
View Related
Dec 12, 2012
I've started using a SEQUENCE in a table instead of an identity.
I seem to be experiencing problems of the sequence getting reset to a lower value periodically. Inserting will work on the table, producing the next bigint in the sequence as the primary key, for days and then all of the sudden duplicate primary key errors show up. When I check, the last primary key value in the table is higher than the current value of the sequence.
For example: right now I have primary key values 6000 through 7032 contiguously in the table, all of which were generated with the sequence. Suddenly I'm getting duplicate primary key errors. A quick check of the sequence shows it's at 7002, but the last inserted row has a primary key of 7032!
I'm populating this table in one place (in the application layer), leaving the primary key null, which allows the default constraint to get the next sequence.
When the problem shows up, I've reset the sequence to the higher number in the past and all is well for many days, then the problem occurs again.
The definition for the sequence is:
CREATE SEQUENCE [dbo].[IntegrationQueueSEQ]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 9223372036854775807
CYCLE
CACHE 50
The default constraint for the primary key on the table is defined as:
ALTER TABLE [dbo].[IntegrationQueue] ADD CONSTRAINT [DF_IntegrationQueue_IntegrationQueueID] DEFAULT (NEXT VALUE FOR [dbo].[IntegrationQueueSEQ]) FOR [IntegrationQueueID]
View 5 Replies
View Related
Jul 9, 2006
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
View 10 Replies
View Related
Oct 26, 2015
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
View 2 Replies
View Related
Jun 30, 2006
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
View 12 Replies
View Related
Apr 28, 2015
I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.
However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?
Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, why one location is better than another location?
View 3 Replies
View Related
Sep 15, 2015
I have table like below.
filename col1 col2 col3
ABD Y NULL Y
XYZ Y Y Y
CDZ Y Y Y
I Need a output like this
filename col1 col2 col3 Group
ABD Y NULL Y Group1
XYZ Y Y Y Group2
CDZ Y Y Y Group2
I wanted to group the col1 , col2, col3 and group it as same group.
View 3 Replies
View Related
Aug 5, 2015
I have a table with 8 columns, I need to update data in multiple columns on this table, this table contains 1 million records, having single update was taking time so I broke the single update into multiple update statements and running multiple update statements in parallel, Each update statement updates different column.
This approach is working fine but I am getting the deadlock error.
Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I tried with various lock hints but no success.
View 4 Replies
View Related
Oct 7, 2015
I am trying to produce a query result that will be using a Case statement to determine values based on scores in a table for each row. The result needs to be exported to be used to upload to a state reporting website. My problem is that the state requires in the CSV file that is uploaded a lot of fields that we do not actually have in the database table we are creating the result set from. After I receive my result set using the Case statement, is there a way to add additional columns that don't actually exist in a table so I can export directly from SQL?
View 6 Replies
View Related
Apr 28, 2015
I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, can you tell me why one location is better than another location?
View 12 Replies
View Related
Jan 23, 2014
I am trying to pivot table DYNAMICALLY but couldn't get the desired result .
Here is the code to create a table
create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,
[Code] .....
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
View 1 Replies
View Related
Oct 30, 2014
I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.
I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.
I started with a normal select statement selecting each column with a join to the project and then a where clause using
(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...
The rest of the columns(all with OR between them).
The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.
Obviously because I have all the columns in the select statement... So I need something like
Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))
View 2 Replies
View Related
Mar 23, 2006
Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?
View 8 Replies
View Related
May 23, 2015
Can a Primary Key column also be a Identity column? The reason I am asking this question is because I have created a table and each time I insert data into the Address Table I am also inserting the AddressID, how do I get the Primary Key (AddressID column) to self generate ID values.
View 9 Replies
View Related
Oct 22, 2014
I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.
CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)
[Code] .....
The results I am looking to achieve are:
PLanIDGroupIDSubGroupIDAddOnCodeExternalCode
101000005LAA1M231_1
101000005OCA2M231_2
101000005UCA3M231_3
101000005XCA4M231_4
201000005LAA1M231_1
201000005OCA2M231_2
201000005UCA3M231_3
201000005XCA4M231_4
Is there an SQL statement that can be used to accomplish this?
View 1 Replies
View Related
Feb 3, 2015
I have this query:
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
View 3 Replies
View Related
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
Feb 13, 2008
I am having problem when performing the alter table switch. Both tables are identical and have pk.
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresults' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part' .
CONSTRAINT [PKIDX_testSummary] PRIMARY KEY CLUSTERED
( [testresult_id] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON TResultsScheme (testresult_id) ) ON TResultsScheme (testresult_id)
I have performed the function on other tables successfully, but this is the first that has an identity column involved. Is there anything special that needs to be done?
[testresult_id] [int] IDENTITY(1,1) NOT NULL,
View 11 Replies
View Related
Jan 29, 2014
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
[code]...
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
View 9 Replies
View Related
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
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
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
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
Aug 31, 2006
I have 2 tables with same structure.only difference is, TableA has key colum which is declared as identity starting from 1.
I want to insert data from TableB into TableA where TableB.Key is all null with TableA.Key starting from 9000001
when i try to do that iam getting an error
"An explicit value for the identity column in table 'lEADlOAN' can only be specified when a column list is used and IDENTITY_INSERT is ON.
"
even when I turned IDENTITY_INSERT ON for TableA
View 4 Replies
View Related
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
Jul 31, 2001
I'm sure this is a common problem but I can't find any relevant info on this site
I have a table that I would like to insert values into. I want to take the values from a secondary table using a select statement.
e.g. a simplified version...
TABLE1
(
Id INTEGER IDENTITY,
UserName VARCHAR(50),
Description VARCHAR(50)
)
TABLE 2
(
UserName VARCHAR(50),
Description VARCHAR(50)
)
Runing the insert statement
INSERT Table1 (UserName, Description)
SELECT *
FROM TABLE2
results in the error
erver: Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'Id', table 'CDS_Live.dbo.Table1'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I was under the impression that an identity column would be automatically inserted by SQL server. Now I know I could write a piece of anonymous Transact SQL which declares a cursor by selecting all rows from table 2 and inserting rows into table 1 on a row by row basis, but is there any way I could do the Insert with a single INSERT statement?
thanks
neill
View 1 Replies
View Related
Apr 25, 2007
Hi,
I have a table with 1000000 records i try to add a field with following`spec.
[mkey] [int] IDENTITY(1,1) NOT NULL
I get the following meassage:-
yearly' table
- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.
Then I Have the following error:-
Time out error
- How is the best way to copy a large table from one to anther.
regards
View 2 Replies
View Related
