how to append rows of 2 tables. Each table consist only 1 row.
sample:
table1
f1 f2 f3
table2
g1 g2 g3
my expected result is:
f1 f2 f3 g1 g2 g3
Note: tables have no relation to each other...
thanks
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
hello sir,
as u explained very well transfered data through Import& Export Wizard for creating destination table but sir i want append rows in the
destination table example. how should i append data in the destination table while copying or transfering data in the exsisting destination table
is there any specfic type of table structure or any setting is require for that if sir yes pls. let me know by my above email add.
as delete rows in the destination table option working properly in IMPORT & EXPORT Wizard only problem in append rows in the destination
table every time i am transfereing data or copying its showing double of exsisting rows its not appending exsising rows & as i want each time
the already exsist rows data should append & insert only new row below that exsisting one .
i hope sir u got my query if u have answer, sir pls revert back with soluntion on my above email add.
thanks & regards
Tamanna Shaikh
Hi,
Im creatting an SSIS project that uses an Data Flow OLE DB Source to read data from an SQL Table and import it into a Destination table using Data Flow OLE DB Destination. but now everytime I run the project it appends all the rows not the new data rows only. How can I make the application so that it appends only the new data from a source table to a destination table. Is there maybe another Data Flow Control that can copy source table to destination and the next time it runs it only copy new rows. or any other way to do this using SSIS.
Your assistance will be highly appreciated.
SQL Server 2000 Standard SP4 Build 2187.
When we are creating the function below the compilation process append one row at the end of the function text:
Code Snippet
ALTER FUNCTION [dbo].[FX_LOJA_REPOSICAO] (
@sParametros varchar(8000),
@sFiltroFiliais varchar(8000),
@sFiltroProdutos varchar(8000)
)
RETURNS @RESULT TABLE (
FILIAL VARCHAR(25) COLLATE DATABASE_DEFAULT NOT NULL,
PRODUTO CHAR(12) COLLATE DATABASE_DEFAULT NOT NULL,
COR_PRODUTO CHAR(10) COLLATE DATABASE_DEFAULT NOT NULL,
COD_TIPO SMALLINT NOT NULL,
DESC_TIPO VARCHAR(40) COLLATE DATABASE_DEFAULT,
QTDE AS (T1+T2+T3+T4+T5+T6+T7+T8+T9+T10+T11+T12+T13+T14+T15+T16+T17+T18+T19+T20+T21+T22+T23+T24+
T25+T26+T27+T28+T29+T30+T31+T32+T33+T34+T35+T36+T37+T38+T39+T40+T41+T42+T43+T44+T45+T46+T47+T48),
T1 SMALLINT, T2 SMALLINT, T3 SMALLINT, T4 SMALLINT, T5 SMALLINT, T6 SMALLINT,
T7 SMALLINT, T8 SMALLINT, T9 SMALLINT, T10 SMALLINT,T11 SMALLINT,T12 SMALLINT,
T13 SMALLINT,T14 SMALLINT,T15 SMALLINT,T16 SMALLINT,T17 SMALLINT,T18 SMALLINT,
T19 SMALLINT,T20 SMALLINT,T21 SMALLINT,T22 SMALLINT,T23 SMALLINT,T24 SMALLINT,
T25 SMALLINT,T26 SMALLINT,T27 SMALLINT,T28 SMALLINT,T29 SMALLINT,T30 SMALLINT,
T31 SMALLINT,T32 SMALLINT,T33 SMALLINT,T34 SMALLINT,T35 SMALLINT,T36 SMALLINT,
T37 SMALLINT,T38 SMALLINT,T39 SMALLINT,T40 SMALLINT,T41 SMALLINT,T42 SMALLINT,
T43 SMALLINT,T44 SMALLINT,T45 SMALLINT,T46 SMALLINT,T47 SMALLINT,T48 SMALLINT,
FILIAL_ORIGEM VARCHAR(25) COLLATE DATABASE_DEFAULT,
FILIAL_DESTINO VARCHAR(25) COLLATE DATABASE_DEFAULT,
ORIGEM_DESTINO VARCHAR(25) COLLATE DATABASE_DEFAULT NOT NULL,
PACK VARCHAR(20) COLLATE DATABASE_DEFAULT,
PRIMARY KEY NONCLUSTERED (FILIAL, PRODUTO, COR_PRODUTO, COD_TIPO, ORIGEM_DESTINO) )
AS
...FUNCTION BODY...
RETURN
END
SP_HELPTEXT returns the function text with the row bellow appended:
Code Snippet
...FUNCTION BODY...
RETURN
END
(T1+T2+T3+T4+T5+T6+T7+T8+T9+T10+T11+T12+T13+T14+T15+T16+T17+T18+T19+T20+T21+T22+T23+T24+
T25+T26+T27+T28+T29+T30+T31+T32+T33+T34+T35+T36+T37+T38+T39+T40+T41+T42+T43+T44+T45+T46+T47+T48)
Why this happening....
Any suggestions?
I'm trying to append rows horizontally - I'm using the "xml path" approach
SELECT
E.[USER_NAME] As 'User Name',
(
SELECT ',' + C.[PERMISSION_NAME]
FOR XML PATH('')
) As [Associated Groups]
FROM TABLEA As A
JOIN TABLEB AS B ON A.PK_OBJ_ID = B.FK_APP_OBJECT_REF
JOIN TABLEC AS C ON C.PK_PERMISSION_ID = B.FK_PERMISSION_REF
JOIN TABLED AS D ON D.FK_PERMISSION_REF = C.PK_PERMISSION_ID
JOIN TABLEE AS E ON E.PK_PERSONNEL_ID = D.FK_PERSONNEL_REF
WHERE A.[OBJECT_NAME] = 'MyObjectName'
It's not working. I'm getting:
User nameAssociated Groups
A. SmithG1
A. SmithG2
A. SmithG3
etc...
What I'm looking for is:
User NameAssociated Groups
A. SmithG1, G2, G3
etc...
Hi guys I have a quick Q:
I have two tables:
tblOne
ID/ PageName / Desc /URL
12/Home Page/This is the home page/www.fff.com
tblTwo
ID/ Name /Link
34/News Page/www.bbb.ie
I wish to create a new table or stored procedure to append one table(which has 4 colums) onto the other(which has only 3 columns) giving the following results:
ID / Name /Link
12/Home Page/www.fff.com
34/News Page/www.bbb.ie
Thanks for help in advance!
Help,
I have a client that requres me to add a header line and trailer line to a flat file. The trick is the header and footer row is required to have a length of 120 with 5 colunmns while the data included in the query has a length of 1125 and about 70 columns. How can I append a header row to a file through SSIS.
I want to loop through rows and append values to a declared variable. The example below returns nothing from Print @output, it's as if my @output variable is being reset on every iteration.
declare @i int,@output varchar(max)
set @i = 1
while @i < 10
begin
set @output = @output + convert(varchar(max),@i) + ','
print @output
set @i = @i +1
end
I need to run a query which will pull data from two tables and appendit as one when it displays the result. The data are in two tables. Butthe result set will be identical in terms of number of columns. I wantto display it one set below the other.This is for pay history. From 2003 we have a new payroll system. Till2002 we used to have a different system. But I need to run a querywhich will pull the data for an employee for the last one year. So theinformation is spread out between these two tables. Both these tablesare in SQL Server databases.I want to write a Stored Procedure. I can use Shape/Append but I thinkit doesnt work on QA/Stored Proc. It needs an OLEDB.How can I write the query. I dont want to use temporary tables and doinserts.ThanksGIRISH
View 1 Replies View RelatedWhat I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have:
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:SoderquistString %>"
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID"
SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @State)">
<DeleteParameters>
<asp:Parameter Name="UserID" Type="Int32" />
</DeleteParameters>
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment:
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID
DELETE FROM [photo] WHERE [UserID] = @UserID;
DELETE FROM [album] WHERE [UserID] = @UserID;
DELETE FROM [comment] WHERE [UserID] = @UserID;
...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!
Hello,
Do anyone knows how can i count rows in all the tables in a database.
With select count(*) I can count rows only from one table .
TIA.
I need to compare two tables in each row. the tables are as follows:-
Table a:
Code:
IDFirst_Name Last_name Birthdate
1Shradha Deshkmukh 1981-12-25 00:00:00
2Shradha Verma 1981-05-11 00:00:00
3Divya Dutta 1982-07-21 00:00:00
4Matthew Palmer 1983-12-28 00:00:00
table d:-
Code:
idfnlndob
1ShradhaTiwari1981-12-25 00:00:00
2DivyaDutta1983-07-21 00:00:00
3SulabhManesar1975-09-11 00:00:00
4MatthewPalmer1983-12-28 00:00:00
5SamuelMaxwell1984-05-22 00:00:00
I want to compare the tables using first name, and I have a log variable which I want to have the value as per the differences in the table that is if the first name matches and second name and dob dont match it shows log value for that FN as 'LN and DOB dont match'.
similarly if First name matches and dob matches then @log is 'LN not match'.
And in case all three match it should show 'match'as log value.The query I use is a s follows:-
Code:
USE testing
GO
DECLARE @NR int
DECLARE @log varchar(200)
SELECT @NR = COUNT(*) FROM a
WHILE @NR>0
[code]...
the result I am getting is :-
Code:
fnlndob (No column name)
ShradhaTiwari1981-12-25 00:00:00match
ShradhaTiwari1981-12-25 00:00:00match
DivyaDutta1983-07-21 00:00:00match
MatthewPalmer1983-12-28 00:00:00match
I have tried using CASE but that doesnt work either.
Hello All
Please can anyone advice me how I can fetch list of all tables which have no records in it in sql server
Thanks
This is a question that I have not had an opportunity to test. Was wanting to know if anyone in the SQl world knows the answer. In SQL 2K and 2005 your rolls are limited to 8060 bytes without using varchar(MAX). My question is do you have to specify varchar(max) before your roll can exceed 8060 or does SQL 2005 exceed without specifing varchar(Max). Also does it expand it across multiple pages automatically. Please assist if you can.
Thanks
Hi,
Does anyone know how to calcuate total no of rows of all tables from database in single query?
Thanks in advance
--kneel
Dear All,I have a table with 10 billion records but there are no key on it. I cannotbuild a key on it as it is the data source.However, the data source exits the duplicated rows.I have used the DTS to transform the data into a new table and delete theduplicated rows. As there are 10 billion records, i need to divide it into 3parts and also the process lasts for 6 hours each part.I want to ask is there any other good methods to slove my problem??ThxEsther
View 1 Replies View RelatedIf I have the following tables:
ID Car
1 Mazda3
2 Miata
Color Car ID
Black 1
Silver 1
Black 2
Style Car ID
i 1
s 1
touring 1
What is the simplest query that will return a result set like:
Car # of Colors # of Styles
Mazda3 2 3
Miata 1 0
I need to delete some rows in some of my tables after tranfering data from my OLTP to SQL database.
Im using SQL 2000
I have tried with the following:
Delete from fsalesinvoiceline
Join dsalesinvoiceheader on
Fsalesinvoiceline.salesid= dsalesinvoiceheader.salesid and
Fsalesinvoiceline.company= dsalesinvoiceheader.company
Where dsalesinvoiceheader.billtocustomerno=€™INDTAST DEBITORNUMMER€™
Go
Delete from dsalesinvoiceheader
Where dsalesinvoiceheader.billtocustomerno=€™INDTAST DEBITORNUMMER€™
I get the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'JOIN'
What am I doing wrong?
/Søren D. Jensen
Hello,I have 4 tables having Customer, Customer_personal_info, Customer_Financial_info, Customer_Other_infoIn this Customer table had a primary key CustomerID , related with every other table with fkey.I want to insert data into four tables using one form having TABs .I created class and storedProcedures to insert row for each table.How to execute all four classes using beginTrans-commitTrans-Rollback-EndTrans. Thanking you,
View 1 Replies View RelatedHello,
I have a question, i loaded 2 files into SQL and the files have some cells that have the same model number.
how can I merge the cells together that have the same model number and (if possible take the avarage of their cell called price)
(and combine their other cell called stock)
and make it into one cell.
Any help would be very very apriciated. Thank you.
i tryed this but it does not work
SELECT Model_number FROM Products
Join Where Model_number='3CM3C1670800B'
I have also Tryed this, IT SHOULD work but I have an error someWhere:
delete from Productsfrom part_number a join
(select part_number, max(part_number) from part_number group by part_number having count(*) > 1) b
on a.part_number = b.part_number and part_number < b.part_number
I have two tables and I want to return data from both. Currently my select statement is returning just 1 child record for each parent record and I want to return all child records that match the parent record.
Here's a sample of my tables/data/etc.
t1
------------
speciesid | species
1 | Mammals
2 | Rodents
3 | Reptiles
t2
---------
animalid | animal
3 | Skink
3 | Iguana
3 | Rattlesnake
2 | Meerkat
1 | Hippo
1 | Elk
What I want to do is pull up a list of all the species and under each list all the animals currently listed under that species.
So the result I want should look like:
Mammals (Hippo, Elk)
Reptiles (Skink, Iguana, Rattlesnake)
Rodents (Meerkat)
so currently I have:
SELECT A.animalid, S.speciesid, A.animal, S.species from t2 as A, t1 as S where S.speciesid=A.animalid order by species
this works great, it's just that it only returns one animal instead of all of the animals. Any help would be appreciated.
Can i insert values into multiple tables? Usually we using this
INSERT INTO Customers (CustomerID) VALUES ('ABC')
But i want to combine both into one statement
INSERT INTO Customers (CustomerID) VALUES ('ABC')
INSERT INTO Orders (OrderID) VALUES ('DEF')
I'm trying to run a sql query using the core dotnetnuke database. I am tying to pull from two separate tables in the same database and return the results below:
------------------------------------
LastName | FirstName | City | PhoneNumber
Smith Joe New York (555)555-5555
------------------------------------
Last Name and FirstName are two separate columns in the DB_Users table, while City and PhoneNumber are both values of the same column DB_UserProfile.PropertyValue located in separate rows defined by an ID in the DB_UserProfile.PropertyDefinitionID column.
The UserProfile table looks something like this...
ID | PropertyDefinitionID | PropertyValue
1 27 New York
2 31 (555)555-5555
------------------------------------
This is what I have so far but it is not working out to well...
SELECT DB_Users.FirstName, DB_Users.LastName, DB_UserProfile.PropertyValue AS City, DB_UserProfile.PropertyValue AS PhoneNumber
FROM DB_Users, DB_UserProfile WHERE DB_Users.UserID = DB_UserProfile.UserID AND DB_UserProfile.PropertyDefinitionID = 27 AND DB_UserProfile.PropertyDefinitionID = 31
ORDER BY DB_Users.LastName
------------------------------------
THANKS!!
Script for finding row counts of two specific tables in each database of SQL in PIVOT format . e.g
Database || TableName1| Tablename2|| RowsCounts
Say for instance I got 2 tables
Subject Table and a Student Table
The Subject Table consist of the following attributes:
Subject_ID [PK], Subject_Name, Course_ID and Course_Name
The Student Table consists of the following attributes:
Subject ID [FK], Students_Name, Students_bday, Students_age, Students_height and Students_weight
How can I use the INSERT function when I would like to add a row with the following details:
Course_Name : Biotechnology
Students_Name : Fred
Students_bday : 01/JAN/1990
Stundets_age : 54
how to use the INSERT function for multiple tables.
I am using three tables/view.
The first is a vendor table where I am pulling company/ID/name
I am using a left join to the other two tables on company/ID
The problem I am having is if the second table has 8 rows and the third table just has 1 row it will repeat 8 times.
How do I show all 8 from the second table but only the 1 from the third table?
You can see below that the voucher number and amount repeat
vendor_idvendor_namepo_nopo_amountvoucher_no amount_due
36999VITEK6012838 $174.00 2622666 $(1,791.00)
36999VITEK6016464 $822.90 2622666 $(1,791.00)
36999VITEK6017791 $876.00 2622666 $(1,791.00)
36999VITEK6025495 $600.00 2622666 $(1,791.00)
36999VITEK6029781 $930.00 2622666 $(1,791.00)
36999VITEK6034433 $3,264.00 2622666 $(1,791.00)
36999VITEK6037821 $2,715.00 2622666 $(1,791.00)
I need to write a sql statement to retrieve column headers and rows from 2 different tables.
E.g.:
row1: EMP column list, tag: emp
row2: DEPT column list, tag: Dept
row3: EMP columns data
row4: DEPT columns data
And the data sorted by deptno column
E.g.:
EMP, Deptno, empno, ename, age, ...,
DEPT, Deptno, ename, loc
EMP, 10, 7548, Justin, 43,....
DEPT, 10, marketing, california
EMP, 20, 7345, Carol,26,....
DEPT, 20, finance, New York
EMP, ....
DEPT,...
.....
......
Hello , I have 870 tables in a database and I need to sort tables by row numbers to get the ones having biggest number of rows , is there a query?
F16 LĂ?GHTĂ?NĂ?NNNG
Hi,
I have a table in Sql 2005 called
Customers
CustomerId
CustomerName
CustomerAge
CustomerRank
CustomerStCode
I have to transfer the records into 2 tables
CustomerMaster
CustomerId
CustomerStCode
CustomerDetails
CustomerId
CustomerName
CustomerAge
CustomerRank
I have to pick up a row from Customers and transfer it to CustomerMaster and CustomerDetails. CustomerId of CustomerMaster will be the CustomerId of CustomerDetails while transfer.
How to do this?
thanks
Rows in table A that have a business status of 'open' are written to table B (on a different db) on a daily basis.
I have managed to extract the relevant rows in table A and table B.
What I now need to do is compare table A, row N with table B row N and return them both if the values differ.
What is the best way of doing this
e.g.
Table A
customerId amount
1 200
2 106 *return this row*
3 412
Table B (filtered by MAX(audit date))
customerID amount
1 200
2 100 *return this row*
3 412
?
I imported all rows of my txt file using SSIS 2005 into a table. I am now trying to figure out how to split out the header, payment rows, and maintenance rows. First, some information.
An example of table results is here:
http://www.webfound.net/split.txt
The table has just one field of type varcha(100) because the incoming file is a fixed length file at 100 bytes per row
The header rows are the rows with HD in them...then followed by detail rows for that header (see here http://www.webfound.net/rows.jpg).
I need to
1) Split out the header into a header table
2) Split out the maintenance rows (related to the header) into a maint table
3) Split out the payment rows (related to the header) into a payment table
I'll need to maintain a PK/FK relationship between each Header and it's corresponding maint and payment rows in the other 2 tables.
To determine if it's a payment vs. maintenance row, I need to compare chars 30 - 31. If it contains 'MT' then you know it's a maintenance row, else it's a payment row.
How in the hell do I do this???
I have an SQL 2000 server. I have multiple tables in the db that have a row with a time stamp of '10-23-2007'. What I am trying to do is delete these specific rows because they don't belong.
So I need to query the db for table names that are like 'elect_Sub%' and then execute a query on those tables that would delete the row with the time_stamp '10-23-2007'. I know that I have to use the db schema to get the table names, but I need help in writing the sql script that will automatically scroll through the tables.
Thanks,
I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.
View 3 Replies View Related