February 6, 2009 at 12:00 pm
Hello ALL,
would you please tell me the INSERT command? how can I insert the data from 5 different Views such as dbo.BOCLEARED, dbo.CTCLEARED, dbo.NHCLEARED, dbo.VTCLEARED, dbo.TotalCleared
I don't know the command how to insert the data from these 5 views that I mentioned above to the new table called SummaryCleared. And in my SummaryCleared has 5 field names such BOCLEARED, CTCLEARED, NHCLEARED, VTCLEARED, TOTALCLEARED. Would you please tell me how?
Thank you
February 6, 2009 at 2:17 pm
http://www.sqlservercentral.com/articles/Best+Practices/61537/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 6, 2009 at 2:28 pm
Hi Bob,
I try to transfer the data from 5 tables into one single table(GrandTotal) with the same field names. Below are my codes, but it gives me a syntax error message : Must declare the scalar variable "@BOCLEARED"
In my table grandtotal, I have a field name BOCLEARED which is identical with field name BOCLEARED in table BOCLEARED.
DO you know why?
Insert into dbo.GrandTotal([BOCLEARED], [CTCLEARED], [MECLEARED], [NHCLEARED], [RICLEARED], [VTCLEARED], [WOCLEARED], [TOTALCLEARED])
Select dbo.BOCLEARED.BOCLEARED, dbo.CTCLEARED.CTCLEARED, dbo.MECLEARED.MECLEARED, dbo.NHCLEARED.NHCLEARED, dbo.RICLEARED.RICLEARED, dbo.VTCLEARED.VTCLEARED, dbo.WOCLEARED.WOCLEARED, dbo.TOTALCLEARED.TOTALCLEARED
From dbo.BOCLEARED, dbo.CTCLEARED, dbo.MECLEARED, dbo.NHCLEARED, dbo.RICLEARED, dbo.VTCLEARED, dbo.WOCLEARED, dbo.TOTALCLEARED
Where dbo.BOCLEARED.[BOCLEARED] = @BOCLEARED OR dbo.CTCLEARED.[CTCLEARED] = @CTCLEARED OR dbo.MECLEARED.[MECLEARED] = @MECLEARED OR dbo.RICLEARED.[RICLEARED] = @RICLEARED OR dbo.VTCLEARED.[VTCLEARED] = @VTCLEARED OR dbo.WOCLEARED.[WOCLEARED] = @WOCLEARED OR dbo.TOTALCLEARED.[TOTALCLEARED] = @TOTALCLEARED
February 6, 2009 at 2:31 pm
http://www.sqlservercentral.com/articles/Best+Practices/61537/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 6, 2009 at 2:58 pm
Hello all
Please show me how to transfer data from multiple tables to single table with the same column field name.
Thanks
February 6, 2009 at 3:06 pm
Just a guess but have you declared the variable @BOCLEARED ?? Just a hunch 🙂
February 7, 2009 at 12:37 am
hi,
if you have table1 and table2 and a totalTable with the same columns and same datatypes, you can use something like this:
insert into totalTable
select * from table1
insert into totalTable
select * from table2
or if you do not have the totalTable
you can use this:
select * into TotalTable from table1
insert into TotalTable
select * from table2
February 7, 2009 at 7:57 am
In a view you can't have variables only in SP.
What you could look at is CTE which is kind of like building a view inside a sp.
Example
WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
SELECT
p.ProductName,
c.CategoryName,
p.UnitPrice
FROM Products p
INNER JOIN Categories c ON
c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)
SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC
---Example TWO selects
---It take scome time to get the understanding of a CTE but here this example is
---building two views (that what i call it and the final select takes from both views).
WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS
(
SELECT
CategoryID,
CategoryName,
(SELECT COUNT(1) FROM Products p
WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
FROM Categories c
),
ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
(
SELECT
ProductID,
CategoryID,
ProductName,
UnitPrice
FROM Products p
WHERE UnitPrice > 10.0
)
SELECT c.CategoryName, c.NumberOfProducts,
p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
INNER JOIN CategoryAndNumberOfProducts c ON
p.CategoryID = c.CategoryID
ORDER BY ProductName
February 10, 2009 at 6:17 am
hi
yes i declared the variable bocleared
February 10, 2009 at 6:34 am
Hello,
thanks for your helps....sorry it took me this long to response back to you. because i broke my arm.
if table BOCLEARED has three columns such as DDS, BOCLEARED, and BOCLEAREDDATE. I wish to take only the data in the column BOCLEARED, and insert into the table grandtotal.
table CTCLEARED also has three columns such as DDS, CTCLEARED, CTCLEAREDDATE. wish to take only the data in the column CTCLEARED, and insert into the table grandtotal.
table NHCLEARED also has three columns such as DDS, NHCLEARED, NHCLEAREDDATE. wish to take only the data in the column NHCLEARED, and insert into the table grandtotal.
would you please tell me how?
thank you
February 10, 2009 at 9:49 am
Hello
I have a question which need to ask you
I have four tables, each table such as
Table BOCLEARED
DDS BOCLEARED BOCLEAREDDATE
Table CTCLEARED
DDS CTCLEARED CTCLEAREDDATE
TABLE NHCLEARED
DDS NHCLEARED NHCLEAREDDATE
TABLE TOTALCLEARED
DDS TOTALCLEARED CLEAREDDATE
I wish to transfer the data from one column of each table such as BOCLEARED, CTCLEARED, NHCLEARED, TOTALCLEARED to new table calls GrandTotal.
After transfered all data from each table then I wish to calculate the total cases for each column such as BOCLEARED, CTCLEARED, NHCLEARED and TOTALCLEARED.
Would please tell me how?
Sorry it took me this long to respond back to you because i broke my arm.
Thank you
February 10, 2009 at 10:01 am
Do you want to join all the tables and insert? If YES then what is the joining key column.
If NO then you can simple use UNION ALL.
Thanks
Vijaya Kadiyala
February 10, 2009 at 10:08 am
I wish to transfer the data from one column of each table such as BOCLEARED, CTCLEARED, NHCLEARED, TOTALCLEARED to new table calls GrandTotal.
After transfered all data from each table then I wish to calculate the total cases for each column such as BOCLEARED, CTCLEARED, NHCLEARED and TOTALCLEARED.
Would please tell me how?
February 10, 2009 at 10:41 am
am not sure if u r asking for the solution like inserting from 3 different tables column data into one final table column.
If yes its simple by one go with union of all the tables.
create table #grandtotal (column1 varchar(100))
create table #BOCLEARED (DDS int, BOCLEARED varchar(100), BOCLEAREDDATE datetime)
create table #CTCLEARED (DDS int, CTCLEARED varchar(100), CTCLEAREDDATE datetime)
create table #NHCLEARED (DDS int, NHCLEARED varchar(100), NHCLEAREDDATE datetime)
insert into #BOCLEARED values(1,'BOCLEARED-1',getdate())
insert into #BOCLEARED values(2,'BOCLEARED-2',getdate())
insert into #BOCLEARED values(3,'BOCLEARED-3',getdate())
insert into #CTCLEARED values(1,'CTCLEARED-1',getdate())
insert into #CTCLEARED values(2,'CTCLEARED-2',getdate())
insert into #CTCLEARED values(3,'CTCLEARED-3',getdate())
insert into #NHCLEARED values(1,'NHCLEARED-1',getdate())
insert into #NHCLEARED values(2,'NHCLEARED-2',getdate())
insert into #NHCLEARED values(3,'NHCLEARED-3',getdate())
insert into #grandtotal
select BOCLEARED from #BOCLEARED
union
select CTCLEARED from #CTCLEARED
union
select NHCLEARED from #NHCLEARED
select * from #grandtotal
Comments please....
Srihari Nandamuri
February 10, 2009 at 11:01 am
Hello there,
I have already had four different tables. now I wish to select one column from each table and transfer to brand new table and call that table grandtotal. After transfered the data from each table into brand new table then I calculate the sum for each column of brand new column.
Thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply