Copying Columns In Query
Dec 29, 2006
I know this is no problem for anyone who has ever done a bit of programming in SQL, but for me it is a problem because I'm rather new in SQL. My question is: how to copy column in the same table in query? I need move one column to another and then to delete the first column (but not the whole column, just one part so I can't use Copy - Paste in Enterprise Manager). I know how to select the part that I want, all I need now is to copy columns.
Thank You all in advanced!
Sincerely,
Ivan
View 4 Replies
ADVERTISEMENT
Dec 30, 2007
Hello to all,
this is my first post on this forum, and I hope you'll be able to solve my problem. I'm working in industrial field and only from time to time I have to work with SQL. My problem is the following:
I have two tables. Both tables have same number of rows but different number of columns. Table 1 has 5, and table2 has three columns.
I need SQL statement to copy two columns A and B from table 1 to table 2 with condition, for example table2.C <> 2?
I don't know if I was clear enough. Basically I need to copy data from two columns of one table to other. Tables have no common data which I can use to join tables.
I tried something like this:
update table2 set (A,B)=(select A,B from table 1) where table2.c <>2
but don't know how to use syntax properly...
Thanks in advance
View 18 Replies
View Related
Jun 17, 2006
I am working with a SQL database that was migrated from MS Access and adapted for full-text search which involved creating a new table with a different design. I would like to copy three columns (YearGiven, MonthGiven, DayGiven) from the Documents table to the newly-created FullDocuments table so that I can delete the Documents table and four other tables with redundant data. Here are the two tables involved in the column copy:
Documents Table
DocID (Primary Key)
DocNo
SequenceNo
YearGiven
MonthGiven
DayGiven
FullDocuments Table
FullDocID (Primary Key)
DocNo
SequenceNo
SectionText
YearGiven (empty €“ no data)
MonthGiven (empty €“ no data)
DayGiven (empty €“ no data)
After the copy column procedure I want the FullDocuments table structure to look like this:
FullDocuments Table
FullDocID (Primary Key)
DocNo
SequenceNo
SectionText
YearGiven (full of transferred data)
MonthGiven (full of transferred data)
DayGiven (full of transferred data)
The problem is that the FullDocuments table contains approximately 4x as many rows as the Documents table. This is because each document has four types €“ the primary Text document and three supplemental documents (Background, Report, and Index). Whereas the Documents table has one row for each Document, the Full Documents table has four rows per document (the four document types). Instead of simply doing a copy and paste of columns based on a primary key and foreign key relationship (as described in the MSDN online books), I need to copy and paste based on the DocNo and SequenceNo columns. Thus the same date data (Year, Month, Day) will need to occupy four consecutive rows for each document in the FullDocuments table (as is now the case with the DocNo and SequenceNo).
I will end up with one table that meets the requirements for the full-text search (primary key and all the text to be searched) and eliminate five tables (Documents and the four document type text tables). This is a static database consisting of historical records so I am not concerned about input errors that can be associated with denormalization.
I tried exporting the data using the Export Wizard using the €śCopy data from one or more tables€? option. The transfer failed. The most relevant lines of the Error Report are:
·Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'FullDocumentID', table 'Documents.dbo.FullDocuments'; column does not allow nulls. INSERT fails.".
(SQL Server Import and Export Wizard)
·Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - FullDocuments" (61) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
·Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
(SQL Server Import and Export Wizard)
I then tried the €śWrite a query to specify the data to transfer€? option focusing on just the YearGiven column using various FETCH, INSERT AND UPDATE commands without any luck. Questions:
1. Is the query approach the best way to achieve my objective, or did I miss something in the €śCopy data€? option in the Export Wizard?
2. If the query approach is the best way, any suggestions on what the query will look like?
Thanks for any help you can provide.
View 5 Replies
View Related
Jan 18, 2007
I have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:
DECLARE @pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @pss OUTPUT
SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator
Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.
View 5 Replies
View Related
Mar 24, 2004
Hi,
I wanted to know a query in sql that would copy a whole table from one database to another database with both the structure and the content.
strangely, my college professor don't know the same.
I tried looking in the help menu in sql but it didn't helping me much.
Help me out...!! I have a deadline to meet. I really would appreciate it. thanxs. --vijay.
View 4 Replies
View Related
Jul 8, 2013
I have a rather complex query (to me at least) that I need to create but I am unsure of where to start. The query requires me to copy existing data into a new row (which will then create a new ID) as well as update all existing records with the newly created id. More specifically, I need to separate the data associated with LocationID 219 from it's parent, CompanyID 992.
Ideally I want to copy the data associated with LocationId 219 and then make a new CompanyId with the copied data (which will also create a new LocationID). Since this new record is no longer going to be associated with CompanyID 992 I will want to remove/delete/drop it from that record.
Finally, and perhaps most difficult of all, I need to update all tables that reference the old ID's together (992 / 219) to reflect the newly created Company ID and Location ID.
View 3 Replies
View Related
Sep 10, 2007
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups
Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:
How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Thanks for all your help!
View 1 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
Jul 20, 2005
The purpose, as you can probably guess, is to produce a set of sampledocuments from a large document run. The data row has a CLUB column and anIFC column; I want a set of samples that contains at least one of each CLUBand at least one of each IFC, but no more than necessary.Example schema and data:CREATE TABLE mDATA (ID INTEGER,CLUB CHAR(7),IFC CHAR(4));INSERT INTO mDATA (ID,CLUB,IFC) values (6401715,'AARPRAT','IC17')INSERT INTO mDATA (ID,CLUB,IFC) values (1058337,'AARPRAT','IC17')INSERT INTO mDATA (ID,CLUB,IFC) values (459443,'AARPPRT','IC25')INSERT INTO mDATA (ID,CLUB,IFC) values (4018210,'AARPBAS','IG21')INSERT INTO mDATA (ID,CLUB,IFC) values (2430656,'AARPBAS','IG21')INSERT INTO mDATA (ID,CLUB,IFC) values (6802081,'AARPPRD','IG29')INSERT INTO mDATA (ID,CLUB,IFC) values (4236511,'AARPPRD','IG29')INSERT INTO mDATA (ID,CLUB,IFC) values (2162104,'AARPBAS','IG21')INSERT INTO mDATA (ID,CLUB,IFC) values (2073679,'AARPPRD','IG29')INSERT INTO mDATA (ID,CLUB,IFC) values (8148891,'AARPBAS','IG21')INSERT INTO mDATA (ID,CLUB,IFC) values (1868445,'AARPBAS','IG21')INSERT INTO mDATA (ID,CLUB,IFC) values (6749213,'AARPBAS','IG21')INSERT INTO mDATA (ID,CLUB,IFC) values (8363621,'AARPPUP','IG29')
View 5 Replies
View Related
Jul 20, 2005
Hi All,I am new to Sql and I want to know if a given value lets say its 225is within ranges defined in the table.I have a table TblControl. Data type of both field is int.StartRange End Range0 100200 300500 600900 950(Sql server 2000 on window 2000)Thanks-Max
View 2 Replies
View Related
May 12, 2008
I have some data in week1 of the database and some data in week2 of the database. Now I want to add these both datas and put it in other column. Can I get exact SQL query for this??
View 6 Replies
View Related
Jul 11, 2001
My table:
Table1
T1T2T3T4T5
---------------
abcde
How to query to have following result:
Table2
abcde
---------------
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
Thanks in advance.
Janasha H
View 1 Replies
View Related
Jan 2, 2007
Can someone Please help me with what seems to me a "complex" query??
I have the following columns/data - See Original Data.jpg
This is what I need the SQL query(s) to do. I need it to
1) Select the distinct names AND months
2) Count the number of times that a name AND a month appeared together
3) Sum totalsold for each name/month
4) sum employeescore for each name/month
So it would look like - After Query.jpg
View 10 Replies
View Related
Mar 13, 2013
What are some good querys to run against a sample table of dates and items where the results can be used for charts?
Here is the sample table.
Code:
CREATE TABLE [dbo].[TestDates](
[ItemId] [int] IDENTITY(1,1) NOT NULL,
[AssignmentName] [nvarchar](max) NULL,
[AssignedDate] [date] NULL,
[DueDate] [date] NULL,
[Code] .....
View 1 Replies
View Related
May 13, 2008
Hi,
I have 3 tables:
Table SLA: SLA_code, SLA_name
Table industry: Industry_code, Industry_name
Table14: SLA_code_origin, SLA_code_destination, Industry_code, Freq
I would like to query to get in one table:
SLA_name for DESTINATION, Industry_name, Freq
Note: SLA_code contains the same codes as SLA_code_origin and SLA_code_destination. All are the same codes in column oringin means the From point and colum destination the To point.
How can I query to get the Freq and Industry for those destination SLAs?
I have make a relationship between:
Table SLA.SLA_code with Table14.SLA_code_destination and
Table_industry.Industry_code with Table14.Industry_code
But I am not sure if just a
SELECT SLA.SLA_name, Industry.Industry_name, Table14.Freq
FROM SLA, Industry, Table14
would select the SLA_destination and not the SLA_origin.
Thanks very much in advance,
Maria
View 1 Replies
View Related
Feb 9, 2015
I created the following simple query between two tables:
SELECT vru.User_Name0, vru.Full_User_Name0, vru.department0, vru.employeeNumber0,
vru.title0, vru.manager0, vrs.location0, vrs.Netbios_Name0, vrs.Last_Logon_Timestamp0
FROM v_R_User vru
LEFT JOIN v_R_System vrs
ON vru.ResourceID = vrs.ResourceID
ORDER BY vrs.User_Name0
I don't know how to properly insert a pic, but basically I get output, except the last 3 columns. I've tried every variation of JOIN and I cannot seem to solve this issue.
View 6 Replies
View Related
Apr 12, 2007
Hi, I have a query that gets the data for a specific week (starting Monday).
The below statement returns the data for the whole week mon-fri
What I would like to do is to have the data come back in a table with columns Mon,Tue,Wed etc breaking down the data.
How would I make the data come back by splitting it into columns mon,Subtot1,tue,Subtot1,wed,Subtot1,thur,Subtot1,fri,Subtot1
SELECT
dbo.People.FirstName,
dbo.People.LastName,
dbo.RequestTypes.Title,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed As Subtot1
FROM
dbo.Actions
INNER JOIN dbo.Requests
INNER JOIN dbo.Companies ON
dbo.Requests.CompanyId = dbo.Companies.CompanyId ON
dbo.Actions.RequestId = dbo.Requests.RequestId
INNER JOIN dbo.RequestTypes ON
dbo.RequestTypes.RequestTypeId = dbo.Actions.RequestTypeId
INNER JOIN dbo.People ON
dbo.People.PersonId = dbo.Actions.ActionedById
WHERE
(dbo.People.PersonId = 'JO'
dbo.Actions.ActionDate > DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) AND
dbo.Actions.ActionDate < DATEADD(wk, DATEDIFF(wk,0,getdate()), 5) )
GROUP BY
dbo.RequestTypes.Title,
dbo.People.FirstName,
dbo.People.LastName,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed
Thanks in advance,
Tugsy
View 1 Replies
View Related
Dec 26, 2007
I just want to query all records with the maximum value in column invoices.numbers. So for instance I have records with invoice numbers = 850, 849, 800 respectively. I want only records 850 (the highest value). Then next month I want invoices with value 851. Anyone know how to write this easily so I don't have to write it out everytime, e.g. invoice.numbers=851?
View 3 Replies
View Related
Jul 31, 2007
Hi All,
I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?
Does anyone have idea how to join table that can reflect both left and right join?
Table A Table B
Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5
A Jan 5 xxx A Jan 1 yyy
B Feb 3 C Mar 4
B Mar 4 C Apr 3
C Apr 2 D May 2
E Mar 1
Result Table
Column1 Column2 Column3 Column4 Column 5
A Jan 6 (= 5+1) xxx yyyy
B Feb 3
B Mar 4
C Mar 4
C Apr 5 (= 2+3)
D May 2
E Mar 1
So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).
Thanks for the help.
View 3 Replies
View Related
Aug 7, 2007
I need to find out all columns that are identity. How do I query syscolumn to find out this information.
Thanks.
View 6 Replies
View Related
Feb 6, 2008
Hi All,
I have a sample table as below:
(All columns do not have to have unique values)
Column n is int, the rest are varchar
[First row is the column Names]
n | t1 | t2 | t3 | t4
1 | a | b | c | d
3 | e | w | a | t
3 | w | q | u | k
2 | g | w | q | n
2 | a | b | s | a
5 | n | a | w | h
I need to create a query or some how create a way to get the data in the form of the below table:
n | t1 | t2 | t3 | t4 | t11 | t21 | t31 | t41
1 | a | b | c | d |
3 | e | w | a | t | w | q | u | k
2 | g | w | q | n | a | b | s | a
5 | n | a | w | h
What that means is that I have a huge table (~6000 rows) that has the column 'n' above (int type) which can have rows that start with the same number (such as number 3 & 2 above). I need these duplicate rows of numbers to be converted/transformed to columns alongside each other. This would create 1 entry for each type of number but have the 4 columns of t1, t2, t3, t4 'added' on as extra columns in the new table with a different column name ofcourse.
So what that would mean is that if there were 3 rows of number n=2 then would have 3x4=12 columns, plus the 'n' column and so forth.
I am not sure on how to start the query for this.
What I have thought is maybe using a cursor and going through the table. But not sure how I could do that really.
The other is using a Pivot in a SELECT statement. But that would not work since the columns can grow indefinetely depending on how many of the same 'number' row there is!
Any ideas on this? Thanks a bunch !!!
(running SQL 2005)
View 4 Replies
View Related
Oct 5, 2006
I have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column. My Query: Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUEFrom EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTESWhere EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_IDORDER BY Product_ID DESC
View 10 Replies
View Related
Dec 20, 2006
I am trying to query the database to get me the foreign key columns and the tables they belong to.I have: The name of the tableI need:The name of the column in the target tableThe name of the column in the referenced tableThe name of the referenced table Any help would be great, thanks
View 5 Replies
View Related
Jan 22, 2008
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that.
I have a table like this:
Select Name from Cars;
Result:
Col1
BMWMercedesFordAudi
But i like to make a query so it is displayed like this:
Col1 Col2
BMW FordMercedes Audi
So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
View 1 Replies
View Related
Mar 27, 2008
Hi,I have a empskill Table which has 3 Columns (1) EmpID (2) SkillTypeID and (3) CourseID.Now SkillTypeID column has data 1 or 2 .......in here 1 means Primary Skill and 2 means Secondary Skill. CourseID will reflect the ID's of subjects like c#,SQL,etc I need a Query which will count the number of primary skilled and number of secondary skilled persons based on subject and will display as followsCOURSE ID SKILL TYPE 21 222 123 424 1IN SHORT: I want to count the number of particular skill types and display them against their respective course id. You need to display one column data of database into two columns as output. Hope you people will help me in this regard.Thanks in advanceRameshRaj
View 5 Replies
View Related
Oct 21, 2005
Hi,I guess my brain stopped but I need help with this. create table #RETURN( uid int, cid int, serviceid int )
insert into #RETURN select 44,75,2 insert into #RETURN select 44,76,1 insert into #RETURN select 44,77,3 insert into #RETURN select 45,78,3I need to query this table that will get me a distinct UID with matching CID ordered by serviceid from lowest to highest.The solution should look likeuid cid44 7645 78Thanks
View 2 Replies
View Related
Oct 9, 2003
Is there a simple and direct way to perform a fulltext query in a table with multiple columns, and to use AND over multiple columns? I've noticed that AND only works within one column.
Example: Take a column 'lastname' with 'jones' and 'smith' in it. Another column 'firstname' with 'alan' where lastname=smith. Search for 'alan and smith', and no results are returned because they are in different columns. If you would have a lastname 'alan smith', that would be found.
View 2 Replies
View Related
Aug 30, 2004
I am wanting to make a query to find out if there are any columns in a table that are null for all records.
So far I am using a cursor that holds all the column names of that table and then a stored procedure that would use a loop to cycle through all the column names in a query to essentially count the number of nulls for each column and then an If statement to check to see if the #of nulls = to the number of records.
What isn't working is my query to count the number of nulls...Here is my query
select count(*)
from XYZ
where @cName is null
Ive tried declaring cName as a sysname, and varchar, neither work.
Also, I've tried using COL_NAME(OBJECT_ID('XYZ'), someColumnIndx)...I think this would be the better choice just because I wouldn't need to use a cursor, but both aren't working.
I am a bit new to MSSQL and don't know my way around the system tables. Is there a sys table that would give me this info so I wouldn't need to perform a query?
Any info would be great.
View 4 Replies
View Related
Jan 17, 2005
I'm trying to create an Insert query and I'm having difficulty in 2 areas:
First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.
INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP
Second, I've tried the following"conversions" in the SELECT statement, to no avail:
CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)
Any pointers much appreciated...
View 2 Replies
View Related
Aug 19, 2014
i want to obtain two columns in a query but i don´t know how to.ex.
col1 col2
1 A
1 B
2 A
1 C
I need something like this.
col1 col2
1 A, B, C
2 A
Col1 = Table Z
Col2 = Table Y
View 6 Replies
View Related
Oct 27, 2014
I have multiple columns around 10...i am writing an update statement which doesn't update any of the rows
update table
set column11=isnull(columnA,0)+isnull(columnb,0)+...
where quarter='Q3'
also tried...
update table
set column11=SUM(COALESCE(column1,0) + COALESCE(column2,0))
where quarter='Q3'
both doesn't update column11.
View 4 Replies
View Related
Jun 2, 2008
This might be a question with an extremely easy answer.. I don't know but here I go.
I want a report with lets say
|A | B | C |
----------------
I can easily figure out the sql statements to find the columns A, B and C individually but how do I combine them?
so lets say I have
select cola as A from table1 where ....
select colb as B from table2...
They are not from the same table so I cannot combine them either (I cannot do select cola, colb from table1 etc.. )
How would I do this? Am I missing something?
View 5 Replies
View Related
