Inserting With DTS In IDENTITY Table
Oct 12, 2006
Hi
I need to insert values from a text-file to a table with a primary key as identity. In the text file I have no idea of the primary key values and i get "foreign key constraint violation" when trying to import null values into the column.
How can I solve the problem? With ordinary insert-statement there is no problem since the table generates identity- key values automatically. Is there a possibility to generate identity values with DTS-import?
Björn
View 1 Replies
ADVERTISEMENT
Jul 14, 2014
We are in the conversion process of making the database ints.This is a change from a guid PK to an integer based PK that uses an int Identity. The program still uses the guid, and we are trying to map that guid to the databases int.We insert using TVPs passed from code. Since the identity is being set upon insert I have three things to accomplish:
1) Insert all the data into the dbo table
2) Update the parent Id in the table
3) Pass the SetsId guid, Sets_Id int, ParentSets_Id int back to the program
This is a high transaction table that will have a lot of records (millions).
--Sample table creation. There is a FK between Sets_Id to ParentSets_Id, Clustered PK on the Sets_Id
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JSets]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JSets](
[SetsID] [uniqueidentifier] NOT NULL,
[Sets_Id] bigint Identity (1,1) NOT NULL,
[code]...
View 0 Replies
View Related
May 2, 2007
Hi All
Is it possible to insert the identity cloumn in a table via SSIS. I've an ID (Identity) column is a table and I am importing data in the table using Excel sheet. I want to insert the value of ID column as Identity(1,1).
View 10 Replies
View Related
Sep 27, 2007
Someone asked me the other day about creating a table with one column that is an identity field and doing an insert so they can capture the SCOPE_IDENTITY() to use across seferal tables. I've never done anything like it and it seemed trivial at the time, but I'm not seeing how to make an insert work. So, for example, we have a table defines as: DECLARE @Bar TABLE (ID INT IDENTITY(1, 1) NOT NULL)
Now, how do you write a simple insert statement that will add a row to the table?
Basicaly, this is an acedemic exercise, but I can't seem to make it work unless I add another column to the table. Any ideas? Maybe I'm not caffinated enough..? :)
View 14 Replies
View Related
Jan 2, 2001
Hi Friends
Happy new year 20001.
Is it requires DBO Permission for do 'SET IDENTITY_INSERT FARRentalUnit ON'
or Write permission is enough.
I don't want give dbo perivilage to user.
Please reply soon.
Thanks for reply in advance.
Murali
View 3 Replies
View Related
Apr 26, 2002
I have an odd issue with a stored procedure using output parameters and a database text field.
I am using custom VB6 COM+ objects in the middle tier and SQL Server 2000 on the back end. The stored procedure has an int as it's first parameter; it is an output parameter which gets set to @@IDENTITY after the data is written to the tables. The last parameter is a text.
The COM+ object executes the proc; ADO 2.7 incorrectly identifies the text parameter as an adVarChar, so I explicitely convert it to an adLongVarChar when I detect that the incoming data is > 8000 characters.
The proc writes the data correctly to the database in all cases. However, when the data is > 8000 characters, SQL doesn't appear to correctly set the output variable. No errors are generated, I simply don't get any value written to the variable. I've searched through MS's documentation, but can't seem to find anything on this issue.
Any help or thoughts are appreciated.
View 1 Replies
View Related
Nov 15, 2006
I have a table with the following schema:
CREATE TABLE [itis].[wrk_taxon_authors] (
[wb_taxon_author_id] [int] NOT NULL ,
[taxon_author_id] [int] IDENTITY (1, 1) NOT NULL ,
[taxon_author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[kingdom_id] [smallint] NOT NULL
) ON [PRIMARY]
GO
I am trying to insert the following data (as you can see fields are seperated with the | ):
19||Flores-Villela and Sánchez-H., 2003|5|
20||Wiegmann, 1828|5|
16|17482|Gray, 1838|5|
17|9823|(Wiegmann, 1828)|5|
I get the following error:Server: Msg 4869, Level 16, State 1, Line 1
Bulk Insert failed. Unexpected NULL value in data file row 1, column 2. Destination column (taxon_author_id) is defined NOT NULL.
Server: Msg 4869, Level 16, State 1, Line 1
Bulk Insert failed. Unexpected NULL value in data file row 2, column 2. Destination column (taxon_author_id) is defined NOT NULL.
Since I have defined column 2 as an identity column, I don't understand why SQL Query analyzer is upset when I do not have a value in that field. To me, I would think it would auto-insert an integer (starting at seed 1 and incrementing by 1), but it doesn't. Could someone tell me what I'm doing wrong?
View 7 Replies
View Related
May 5, 2008
I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.
I have a database table as follows:
eg
1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4]
Using the myGameRecencyAllBalls table ---
I wish to insert a date into a new row but have not been able to determine how to with the identity specification on the GameId column.
Can anyone please assist?
Thank you.
lpbcorp
sqlCmd.CommandText = "DECLARE @date datetime SET @date = '" + Date +
"DECLARE @lastRowGameId int " +
"DECLARE lastrow_gameidcursor CURSOR SCROLL FOR " +
"(SELECT GameId FROM " + DBGameName.ToString() + "RecencyAllBalls) " +
"OPEN lastrow_cursor " +
"FETCH LAST FROM lastrow_gameidcursor INTO @lastRowGameId " +
"' INSERT INTO " + DBGameName.ToString() + "RecencyAllBalls.Date VALUES (@date) WHERE GameId = @lastrow_gameidcursor + 1";
sqlCmd.ExecuteScalar();
View 6 Replies
View Related
Jul 26, 2007
I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly.
How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure.
Waht is the way to achive this please?
Marc
View 2 Replies
View Related
Apr 2, 2008
I am having troubles trying to copy some rows from a table on my local computer to a table on a remote SQL Server 2005 that is being hosted by one of thos web hosting companies. The problem is that the table has an identity column. I first tried using the the following command:
SET IDENTITY_INSERT [remoteservername].Library2005.dbo.tblLanguages ON
but that results in the error:
Msg 8103, Level 16, State 1, Line 1
Table 'remoteservername.Library2005.dbo.tblLanguages' does not exist or cannot be opened for SET operation.
I read in another topic, that I should change this into the following:
EXECUTE [remoteservername].Library2005.dbo.sp_executesql N'SET IDENTITY_INSERT dbo.tblLanguages ON'
That command executes without error, but the problem is that I cannot perform the actual insert, because it is not within the execute statement. In other words, the following doesn't work:
EXECUTE [remoteservername].Library2005.dbo.sp_executesql N'SET IDENTITY_INSERT dbo.tblLanguages ON'
INSERT INTO [remoteservername].Library2005.dbo.tblLanguages
(colLangID, colEnglish, colGerman, colSpanish)
SELECT colLangID, colEnglish, colGerman, colSpanish FROM tblLanguages
This results in the error:
Msg 7344, Level 16, State 1, Line 2
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[remoteservername].[Library2005].[dbo].[tblLanguages]' because of column 'colLangID'. The user did not have permission to write to the column.
The remote server is linked correctly on my end via the sp_addlinkedserver and sp_addlinkedsrvlogin. Is there any way to force the remote server to turn IDENTITY_INSERT ON permanently and then let me execute as many INSERTS as I want and then turn it back OFF?
View 2 Replies
View Related
Apr 21, 2008
Hi,
I am using SQL Server 2005 Mobile Edition & Merge Replication
in this I want to insert a record into table,
in that table I have taken UserID coloumn as auto incrementing Identity type.
In a book I read that,
If you are using an Identity column, you must find the next available number and reseed before an insert can be successful. You will also have to set up ranged identity columns on the published database to prevent errors when the new data is merged.
Now I want to ask here that, how should I ressed that value before inserting?????
any help in a form of CODe will be appriciated.....
thanks in advance...
View 8 Replies
View Related
Jan 16, 2006
Hi, All:Please help. I use sql server as back end and Access 2003 as front end(everything is DAO).A table on SQL server has an identity column as the key.We have trouble on adding records to this table using the following SQL.strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,E FROM myTableonAccessLocal"db.execute strSQLThe schema of the table "myTableOnSQLServer" and the schema of the table"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" andthe table "myTableonAccessLocal" does not have a key.When we try to run the query, it gives errors indicating the key is violatedor missing.Should I figure out the autonumber for it first and then add to the SQLserver table?Many thanks,HS
View 1 Replies
View Related
Apr 24, 2015
I am writing an Instead of Insert trigger. I would like to fire an error when inserting into an 'Identity' column. Since UPDATE([ColumnName]) always returns TRUE for insert statements, is there an easy/fast way around this? I don't want to use:Â
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NULL))
here is my pseudo-code...
CREATE VIEW [org].[Assets]
WITH SCHEMABINDING
[Code] .....
-- How does this statement need to be written to throw the error?
--UPDATE([AS_ID]) always returns TRUE
IF(UPDATE([AS_ID]))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Is there a faster/better method than this?
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NOT NULL))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Do Stuff
END;
-- Should error for inserting into [AS_ID] field (which is an identity field)
INSERT INTO [org].[Assets]([AS_ID], [Tag], [Name])
VALUES(1, 'f451', 'Paper burns'),
(2, 'k505.928', 'Paper burns in Chemistry');
-- No error should occur
INSERT INTO [org].[Assets]([Tag], [Name])
VALUES('f451', 'Paper burns'),
('k505.928', 'Paper burns in Chemistry');
View 7 Replies
View Related
Oct 10, 2007
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
View 3 Replies
View Related
Feb 20, 2007
Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an
Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)
My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....
//insert an attendance record to the attendance table
string userID;
userID = Session["User"].ToString();
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@MembershipNo, @Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//insert an aerobics record into the aerocibs table
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@MachineID, @Intensity, @ExerciseTime)";
pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//same code as above for the resistance table
However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
Cheers
Scotty
View 8 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
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
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
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Feb 23, 2015
I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.
View 2 Replies
View Related
Apr 24, 2008
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.
main.ds.MailsSignature.Clear();
main.ds.MailsSignature.AcceptChanges();
string[] signFiles = Directory.GetFiles(Settings.signDirectory);
foreach (string signFile in signFiles)
{
mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();
mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.
main.ds.MailsSignature.Rows.Add(mailsSignatureRow);
}
mailsSignatureTableAdapter.Update(main.ds.MailsSignature);
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Is there any limitation in CF?
Regards,
Professor Corrie.
View 3 Replies
View Related
May 24, 2007
Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
View 1 Replies
View Related
Oct 22, 2012
I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.
View 6 Replies
View Related
Aug 14, 2015
Below is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
View 5 Replies
View Related
Nov 2, 2015
INSERT
INTO [Table2Distinct]Â Â Â Â Â Â Â Â
([CLAIM_NUMBER]Â Â Â Â Â Â Â Â
,[ACCIDENT_DATE]
[code]....
I used the above query and it still inserts all the duplicate records. What is wrong with my statement?
View 5 Replies
View Related
Sep 18, 2014
I have a 2010 SSIS package where I am reading csv files with different fields and formatted data, I have created separate packages for each file and I am formatting the data to fit the final destination tables data elements, I've been instructed to create 7 separate packages to read each file and the format the data from the 7 csv files, and insert into their table tbl1, tbl2, tbl3...etc then, I'm taking a execute sql task and wanting to insert the tbl1, tbl2, tbl3...etc into destination table that will be the final table for all reports and other uses.
1- should I create a ID?
2- these files will be read once a month
3- I want to append the data, not drop and recreate each run,
4- It's 2012 SQL and 2010 SSIS
Each csv file is in a different format, some have 15 columns, other have 8 I have to parse the data, in SP to align with the fields in the destination table.
5- Can I force RowID to be the next auto gen number from tbl1,for the start of insert for tbl2, then last row of tbl2 for insert of tbl3???
View 0 Replies
View Related
Jan 19, 2008
Code Block
Hi,
I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:
Table Name: metrics_ladder
id security_id metric_id value
1 3 80 125.45
2 3 81 548.45
3 3 82 145.14
4 3 83 123.32
6 4 80 453.75
7 4 81 234.23
8 4 82 675.42
.
.
.
Table Name: metric_details
id metric_id metric_type_id metric_name
1 80 2 Fiscal Enterprise Value Historic Year 1
2 81 2 Fiscal Enterprise Value Current Fiscal Year
3 82 2 Fiscal Enterprise value Forward Fiscal year 1
4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2
5 101 3 Calendar Enterprise value Historic Year 1
6 102 3 Calendar Enterprise Value Current Fiscal Year
5 103 3 Calendar Enterprise value Forward Year 1
6 104 3 Calendar Enterprise Value Forward Year 2
Table Name: metric_type_details
id metric_type_id metric_type_name
1 1 Raw
2 2 Fiscal
3 3 Calendar
4 4 Calculated
The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:
id security_id metric_id value
1 3 101 <calculated value>
2 3 102 <calculated value>
3 3 103 <calculated value>
4 3 104 <calculated value>
Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.
Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,
metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally
metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).
As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:
security_id 80 81 82 83 101 102
----------- -- -- -- -- -- --
3 125.45 548.45 145.14 123.32 <calculated value> <calculated value>
4 ...
.
.
.
which is then unpivoted.
The SQL that achieves this is more or less as follows:
*********
START SQL
*********
declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...)
-- Dummy year variable to make it easier to use MONTH() function
-- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc...
DECLARE @DUMMY_YEAR VARCHAR(4)
SET @DUMMY_YEAR = 1900;
with temp(security_id, metric_id, value)
as
(
select ml.security_id, ml.metric_id, ml.value
from metrics_ladder ml
where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...)
-- only consider securities with fiscal year end not equal to december
and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec')
)
insert into @calendar_averages
select temppivot.security_id
-- Net Income
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103]
,NULL as [104]
-- Share Holders Equity
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107]
,NULL as [108]
-- Capex
-- Sales
-- Accounts payable
etc...
..
..
from temp
pivot
(
sum(value)
for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)
) as temppivot
inner join company_details cd on temppivot.security_id = cd.security_id
*********
END SQL
*********
The result then needs to be unpivoted and stored in metrics_ladder.
And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.
Many thanks (if you've read this far!)
M.
View 6 Replies
View Related
Nov 20, 2013
I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.
create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)
View 7 Replies
View Related
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
Jan 16, 2005
Hi,
Anyone can help me?
How to create Table A by inserting all the data from Table B?
Cheers,
Daniel.
View 1 Replies
View Related
May 12, 2008
How do i insert data into multiple tables. Lets say i have 2 tables: Schedules and Event
Schedules data is entered into the Schedules Table first
then now i need to insert Event table's data by refrencing the (PK ID) from the schedules table.
How do i insert data into Event table referencing the (PK ID) from Schedules Table ?
Fields inside each of the tables can be found below:
Event Table
(PK,FK) ScheduleID
EventTitle
AccountManager
Presenter
EventStatus
Comment
Schedule Table
(PK) ID
AletrnateID
name
UserID
UserName
StartTime
EndTime
ReserveSource
Status
StatusRetry
NextStatusDateTime
StatusRemarks
View 2 Replies
View Related
Jun 4, 2015
I am looking for an alternate logic for below-mentioned code where I am inserting into a table and having left join with the same table
insert TABLE1([ID],[Key],[Return])
select distinct a.[ID],cat1,cat2Â from
(select ID,[Key] Cat1 ,[Return] cat2 from @temp as temp) a left join TABLE1 oon a.ID= o.ID
and a.Cat1 = o.[Key]
and a.cat2 = o.[return]
where [key] is null order by ID
View 2 Replies
View Related
Mar 23, 2006
I am new to asp.net and studying on book.. currently i am stuck with a problem which not understand what is it !! Can anyone help me ?? I trying a shopping cart "Check Out" method, and when i am done the process.. My order_lines Table can update the OrderID which just generated !! What wrong with the statement ??
Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick ' Insert the order and order lines into the database Dim conn As SqlConnection = Nothing Dim trans As SqlTransaction = Nothing Dim cmd As SqlCommand
Try conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) conn.Open() trans = conn.BeginTransaction cmd = New SqlCommand() cmd.Connection = conn cmd.Transaction = trans
' set the order details cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @City,@State, @PostCode, @Country, @Total)" cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50) cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime) cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50) cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255) cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 50) cmd.Parameters.Add("@State", SqlDbType.VarChar, 50) cmd.Parameters.Add("@PostCode", Data.SqlDbType.VarChar, 15) cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50) cmd.Parameters.Add("@Total", Data.SqlDbType.Money)
cmd.Parameters("@MemberName").Value = User.Identity.Name cmd.Parameters("@OrderDate").Value = DateTime.Now() cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text cmd.Parameters("@State").Value = CType(Wizard1.FindControl("txtState"), TextBox).Text cmd.Parameters("@PostCode").Value = CType(Wizard1.FindControl("txtPostCode"), TextBox).Text cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text cmd.Parameters("@Total").Value = Profile.Basket.Total
Dim OrderID As Integer OrderID = Convert.ToInt32(cmd.ExecuteScalar()) <-- Is it wrong or need to add wat ? ' change the query and parameters for the order lines cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID,Quantity, Price) VALUES (@OrderID, @ProductID, @Quantity, @Price)" cmd.Parameters.Clear() cmd.Parameters.Add("@OrderID", Data.SqlDbType.Int) cmd.Parameters.Add("@ProductID", Data.SqlDbType.Int) cmd.Parameters.Add("@Quantity", Data.SqlDbType.Int) cmd.Parameters.Add("@Price", Data.SqlDbType.Money) cmd.Parameters("@OrderID").Value = OrderID
For Each item As CartItem In Profile.Basket.Items cmd.Parameters("@ProductID").Value = item.ProductID cmd.Parameters("@Quantity").Value = item.Quantity cmd.Parameters("@Price").Value = item.UnitPrice cmd.ExecuteNonQuery() Next ' commit the transaction trans.Commit() Catch SqlEx As SqlException ' some form of error - rollback the transaction ' and rethrow the exception If trans IsNot Nothing Then trans.Rollback() End If ' Log the exception Throw
Finally If conn IsNot Nothing Then conn.Close() End If End Try ' we will only reach here if the order has been created successfully ' so clear the cart Profile.Basket.Items.Clear() End Sub
View 4 Replies
View Related
