Pivoting A Table
May 7, 2008
Hi ,
I want to pull data from XLs file and put them in a table.
Source file looks like this.
Month
Mar-07
Apr-07
May-07
Non-Accruals
$304,732,515
$307,051,978
$308,274,921
REO
$115,072,839
$123,957,394
$149,744,174
Home Equity Total NPA
$419,805,354
$431,009,372
$458,019,095
Destination table should look like this.
Date(Only the first row ) in XL file is in the following format.
DATE(YEAR($O1)-1,MONTH($O1)-1,DAY($O1)).
From second row onwords data format is Money type.
I hope I need to convert the date row into SQL datetime type too. Otherwise it comes as NULL.
Month
Non-Accruals
REO
Home Equity Total NPA
Mar-07
$ 304,732,515
$ 115,072,839
$ 419,805,354
Apr-07
$ 307,051,978
$ 123,957,394
$ 431,009,372
May-07
$ 308,274,921
$ 149,744,174
$ 458,019,095
Can i create a SSIS package to do this job? if so , How? I'm not sure which transformation should i used and how?
Hope some one can help me
Thanks
View 5 Replies
ADVERTISEMENT
Jan 28, 2008
Hello there I have a table like so
Q1 Q2 Q3 Q4 Sales Rep
1 5 6 0 John
2 3 6 0 Rod
3 2 3 0 Gill
4 5 1 0 Guy
And I would like to rearrange it like
John Rod Gill Guy
Q1
Q2
Q3
Q4
Is this something I can accomplish with the PIVOT or UNPIVOT commands? Is there another way?
View 9 Replies
View Related
Nov 12, 2007
I have a query in which I would like to pivot the resultsI presently have my results displaying something like this. OrderNumber Product OrderQuantity--------------- --------------- ----------------------0608 Prod1 30608 Prod2 120608 Prod3 2 What I am after is for the results to display something like this.OrderNumber Prod1 Prod2 Prod3
--------------- --------- --------- ---------
0608 3 12 2 This is using SQL Server ver 8.0
View 3 Replies
View Related
Sep 21, 2007
hello guys !!!
i have a table as
id date data
1 3/10/2007 "hello"
1 4/10/2007 "hi"
2 3/10/2007 "hello"
2 4/10/2007 "why"
i need the output like
id 3/10/2007 4/10/2007
1 "hello" "hi"
2 "hello" "why"
Any idea ???
any means to do it??
View 5 Replies
View Related
May 7, 2008
Hi ,
My source file looks like this.
Month
Mar-07
Apr-07
May-07
Non-Accruals
$304,732,515
$307,051,978
$308,274,921
REO
$115,072,839
$123,957,394
$149,744,174
Home Equity Total NPA
$419,805,354
$431,009,372
$458,019,095
Destination table should look like this.
Month
HE Non-Accruals
HE REO
Home Equity Total NPA
Mar-07
$ 304,732,515
$ 115,072,839
$ 419,805,354
Apr-07
$ 307,051,978
$ 123,957,394
$ 431,009,372
May-07
$ 308,274,921
$ 149,744,174
$ 458,019,095
Can anyone help me to write t-sql code to transfer data into destination table as its shown above.
Thanks
View 1 Replies
View Related
Apr 25, 2008
I've done both a CTE and a pivot, but never together. I did see a few examples out there and followed them, but mine isn't working. I have four 'tables' within the CTE, and then my final select statement joins all of them and attempts to pivot. My error is:
Msg 156, Level 15, State 1, Line 90
Incorrect syntax near the keyword 'PIVOT'.
WITH TwoYrsActual (LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,"06Actual")
AS (
SELECT DISTINCT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD AS '06Actual'
FROM dbo.FactInventoryHistory HIS
INNER JOIN DimLocation LOC ON
His.LocationType_Code = Loc.LocationType_Code
INNER JOIN DimDate Date ON
His.Week_idx = Date.Date_Idx
INNER JOIN DimScenario Scenario ON
His.Scenario_Idx = Scenario.Scenario_Idx
WHERE Scenario_Code = 'FY06A'
GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD
),
OneYearActual (LocationType_Name, FiscalYear_Name,FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
"07Actual")
AS (
SELECT DISTINCT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD AS '07Actual'
FROM dbo.FactInventoryHistory HIS
INNER JOIN DimLocation LOC ON
His.LocationType_Code = Loc.LocationType_Code
INNER JOIN DimDate Date ON
His.Week_idx = Date.Date_Idx
INNER JOIN DimScenario Scenario ON
His.Scenario_Idx = Scenario.Scenario_Idx
WHERE Scenario_Code = 'FY07A'
GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD
),
PresentYrActual (LocationType_Name,FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
"08Actual")
AS (
SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD AS '08Actual'
FROM dbo.FactInventoryHistory HIS
INNER JOIN DimLocation LOC ON
His.LocationType_Code = Loc.LocationType_Code
INNER JOIN DimDate Date ON
His.Week_idx = Date.Date_Idx
INNER JOIN DimScenario Scenario ON
His.Scenario_Idx = Scenario.Scenario_Idx
WHERE Scenario_Code = 'FY08A'
GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD
),
PresentYrPlanned (LocationType_Name,FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedTY,
ActualSalesAmtUSD)
AS (
SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD AS PlannedTY,
ActualSalesAmtUSD
FROM dbo.FactInventoryHistory HIS
INNER JOIN DimLocation LOC ON
His.LocationType_Code = Loc.LocationType_Code
INNER JOIN DimDate Date ON
His.Week_idx = Date.Date_Idx
INNER JOIN DimScenario Scenario ON
His.Scenario_Idx = Scenario.Scenario_Idx
WHERE Scenario_Code = 'FY08P'
GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
ActualSalesAmtUSD
)
select * from (
SELECT PresentYrActual.LocationType_Name, PresentYrActual.FiscalPeriodOfYear, PresentYrActual.FiscalWeekOfPeriod, PresentYrActual.FiscalWeekOfYear, PresentYrActual.LocationType_Code, PresentYrActual.Scenario_Idx, PresentYrActual.Scenario_Code, "08Actual", "07Actual", "06Actual",PlannedTY
FROM PresentYrActual
FULL OUTER JOIN OneYearActual ON
PresentYrActual.FiscalWeekOfYear = OneYearActual.FiscalWeekOfYear AND
PresentYrActual.LocationType_Code = OneYearActual.LocationType_Code
FULL OUTER JOIN TwoYrsActual ON
PresentYrActual.FiscalWeekOfYear = TwoYrsActual.FiscalWeekOfYear AND
PresentYrActual.LocationType_Code = TwoYrsActual.LocationType_Code
FULL OUTER JOIN PresentYrPlanned ON
PresentYrActual.FiscalWeekOfYear = PresentYrPlanned.FiscalWeekOfYear AND
PresentYrActual.LocationType_Code = PresentYrPlanned.LocationType_Code
)
PIVOT
(
SUM(PlannedTY)
FOR PresentYrActual.LocationType_Code IN (C, M))
AS pivottable
View 13 Replies
View Related
Nov 12, 2004
I have a reference table that looks like this
id | value
==========
1,abc
1,def
1,ghi
2,def
2,jkl
I want these values to go horizontally into another table matched on id, to look like this:
id | value
========
1,abc def ghi
2, def jkl
I built a cursor to parse through it but was taking forever (there's 185,000 records in the reference table). Any idea's on the fastest way to perform this function?
View 1 Replies
View Related
Aug 29, 2013
I'm getting nulls on execution of the below query. I have tried both isnull and coalesce, but to no avail.
select *
from
(
select
a.BusinessUnitCode,
a.AdmitCCYYMM As Date, a.[Count of Admits]*1.0 / m.MemberCount * 12000 AdmitsPer1000
[Code] .....
View 3 Replies
View Related
Jan 4, 2006
there is a prblem with data in pivoting the table.
problem is like this--
there is some data 'xy' and some data 'xy '. when i m giving 'xy' as a pivot key value it doesent recognise 'xy ' and viseversa..
i can't reduce the size of the datatype coz there is some data of diffrent size as 'abcd'.
this data is loaded from excel sheet to sql sever table.
wht can i do for this problem.
is there any method to truncate the indivisual data, i m using nvarchar datatype for this.
View 2 Replies
View Related
Jul 20, 2005
gud day.please help me. im working right now on a case study that willretrieve/produce a simple report on sql. my problem is I dont know howto pivot queries like in access. please help me. thanks
View 2 Replies
View Related
Apr 28, 2008
Hi,
Can anybody help me with the following...I want to Pivot the following data
Pcode
Year
Month
Mcode
Value
2
2008
March
EN10A
56349.1
2
2008
March
EN10B
1061.6
2
2008
March
EN10C
2.67
2
2008
March
EN10D
8370
2
2008
April
EN10A
819.31
2
2008
April
EN10B
245.09
2
2008
April
EN10C
33.38
2
2008
April
EN10D
2.31
After Pivot...the data should be like this
Pcode
Year
Month
EN10A
EN10B
EN10C
EN10D
2
2008
March
56349.1
1061.6
2.67
8370
2
2008
April
819.31
245.09
33.38
2.31
Can we use Pivot function or is their a easier way for doing this...Also the MCodes are dynamic so now there are only 4 distinct MCodes but they may be more than four...
View 4 Replies
View Related
Oct 11, 2004
I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:
Measure Month Value
A June 10.00
A July 9.00
A Aug 11.00
B Jun 100.00
B Jul 98.00
B Aug 99.00
C Jun 0.75
C Jul 0.8
C Aug 0.91
I need to report the data like this:
Measure Jun Jul August
A 10 9 11
B 100 98 99
C 75% 80% 91%
This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.
But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.
I can restructure the database, if needed.
I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.
It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?
View 1 Replies
View Related
Jun 4, 2008
I have a table with the following structure
[ID] [A] [B] [Cnt] [Qty]
1 s v1 4 40
2 g v1 2 23
3 p v2 7 22
4 l v3 1 6
5 v v4 7 5
Since I do not know before hand what [B] will be , I have created a dynamic script to pivot the data :
Select *
from ( select [ID],[A],[B],sum([Cnt]) AS Cnt
group by [ID],[A],[B]
) a
PIVOT ( sum(cnt) for [B] in (@list)) b
Now thequstion is :
1. How do I amend this qry to eliminate nulls in my output
[ID] [A] [v1] [v2] ......[vn]
1 s null 9
2 g 10 null
2. Is it possible to include both [cnt] and [Qty] traspose along
[B] = v1,v2 ...... vn
Any advice will be most welcome.
Thanks
View 4 Replies
View Related
Nov 29, 2007
I need some help here in Pivoting the table.
I have the table with the Following Columns.. and here is the sample data. Assume that below table will just have one weeks worth of data.
I can write a stored Proc using cursor, but I just want to learn how
to do it with using cursors
TD-------- Acct------ RouteID----Symbol---- Quantity----
---------- ---------- ---------- ---------- -----------
11/26/2007 40B91209-- CSFB------ GBL--------100
11/26/2007 40B91209-- SIGMA-X----TDY--------100
11/26/2007 4W3L1209-- CSFB------ BIDZ------ 1300
11/26/2007 4W3L1209-- CSFB------ SURW------ 100
11/27/2007 HFS10003-- SIGMA-X----URBN------ 500
11/27/2007 RAM10001-- ISE--------DSCP------ 300
11/27/2007 RAM10001-- SIGMA-X----HYC--------468
11/28/2007 HFS10003-- CSFB------ ARO--------5900
11/28/2007 HFS10003-- CSFB------ CAB--------1300
11/28/2007 HFS10003-- CSFB------ PNRA------ 4600
11/29/2007 RAM10001-- CSFB------ DSCP------ 175
11/29/2007 HFS10003-- CSFB------ CL-------- 220
11/29/2007 WIL10008-- SIGMA-X----CBM--------1400
The output should look some thing like this. If some can help me
AcctNum----RouteID--symbol--MON--TUE--Wed--THU---- FRI
WIL10008---SIGMA-X--CBM-----0----0----0----1400-- 0
Thanks for any help.
View 2 Replies
View Related
Mar 20, 2008
In the following code examples I got to learn PIVOT, I found an error for SUM. However when this is ran against the AdventureWorks db it works fine. Notice it is using a table variable and not an actual table. What do I need to do to my db to get this to work?
Thanks!
declare @sales table
(
[Year] int,
Quarter char(2),
Amount float
)
insert into @sales values(2001, 'Q1', 70)
insert into @sales values(2001, 'Q1', 150)
insert into @sales values(2002, 'Q1', 20)
insert into @sales values(2001, 'Q2', 15)
insert into @sales values(2002, 'Q2', 25)
insert into @sales values(2001, 'Q3', 50)
insert into @sales values(2002, 'Q3', 20)
insert into @sales values(2001, 'Q4', 90)
insert into @sales values(2001, 'Q4', 80)
insert into @sales values(2002, 'Q4', 35)
select * from @sales
PIVOT (
SUM(Amount)
for Quarter in (Q1, Q2, Q3, Q4)) as p
Yields...
Incorrect syntax near 'SUM'.
View 2 Replies
View Related
Aug 1, 2014
I'm trying using the GROUP BY CUBE aggregation. Currently I have this working as such:
SELECT
ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date
,ISNULL([1 Attempt],0) [1 Attempt]
,ISNULL([2 Attempts],0) AS [2 Attempts]
,ISNULL([3 Attempts],0) AS [3 Attempts]
,ISNULL([4 Or More],0) AS [4 Or More]
[Code] .....
Basically this is used to work similar to a Pivot table in excel. My data will look as follows:
Date 1 Attempt2 Attempts3 Attempts4 Or MoreTotal
2012-09-04 239 68 2 8 317
The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8
If I change the FROM select clause to use SUM instead of COUNT
SELECT
CONVERT(DATE,[Date]) Date
,ISNULL(AttemptsFlag,'Total') as Attempt
,SUM(NoOfTimes) AS Totals
FROM
XXXXX
GROUP BY
CUBE([Date],AttemptsFlag)
It will return the correct Total amount but not the right numbers for the Attempt groupings...
View 1 Replies
View Related
Feb 12, 2007
Hi,
For the Data Driven Subscription in SSRS we are using the following stored procedure
In Step 3 - Create a data-driven subscription
create procedure spRSGetReportSettings
(
@ReportID as integer
) as
begin
set nocount on
declare @t as table(y int not null primary key)
declare
@cols as nvarchar(max),
@y as int,
@sql as nvarchar(max)
set @cols=stuff(
(select N',' + quotename(y) as [text()]
from (select ParameterName as y from Reportsettings where reportid=1) as Y
order by y
For XML Path('')),1,1,N'');
set @sql=N'select * from
(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D
pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'
exec sp_executesql @sql
end
Basically the idea is to maintain a single report parameter setting table for multiple reports.
Structure of the table is as given below
ReportID, ParameterName, ParameterValue.
Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)
But, in SSRS it is giving any results.
In Step 4 - Create a data-driven subscription,
Get the value from the database drop down, I am not getting any database columns.
Please help.
Kumar
View 3 Replies
View Related
Jun 13, 2006
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
View 3 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
Mar 29, 2007
Hi
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
Please help me.
Thanks.
View 1 Replies
View Related
Jul 24, 2015
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
View 4 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Jan 9, 2008
Hi all,
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing:
dbo.Categories
dbo.CustomerCustomerDemo
dbo.CustomerDemographics
dbo.Customers
dbo.Employees
dbo.EmployeeTerritories
dbo.Order Details
dbo.Orders
dbo.Products
dbo.Regions
dbo.Shippers
dbo.Suppliers
dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing:
dbo.Categories
New Table...
Modify
Open Table
Script Table as |> CREATYE To |>
DROP To |>
SELECT To |>
INSERT To |> New Query Editor Window
File....
Clipboard
UPDATE To |>
DELETE to |>
From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance,
Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
View 3 Replies
View Related
Aug 29, 2007
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies
View Related
May 29, 2008
'****************************************************************************
Cmd.CommandText = "Drop Table Raj"
Cmd.ExecuteNonQuery()
Cmd.CommandText = "Select * Into Raj From XXX"
Cmd.ExecuteNonQuery()
'**************************************************************************
This generates error that Table already exist.
If Wait 1 sec then execute statement then it works fine.
Thanks in Advance
Piyush Verma
View 1 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Jan 26, 2006
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies
View Related
Mar 20, 2008
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Thanks
View 8 Replies
View Related
Sep 17, 2007
In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
View 1 Replies
View Related
Nov 29, 2006
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp
both works fine , then what is the difference
waiting for ur reply
Banti"
View 1 Replies
View Related
Jun 9, 2014
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
View 3 Replies
View Related
May 23, 2007
Is there any Posibility to change a User Table to System Table.
How to create one system table.
I am in Big mess that One of the Table I am using is in System Type.
I cant Index the same. Is there any Mistake we can change a user table to system table.....
View 9 Replies
View Related
Sep 9, 2005
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
View 1 Replies
View Related
