I have a table named tbOrders. I want to swap the data between 2 of its columns named OrderNumber and CaseNumber.
Both the columns are varchar datatype. How can i swap the data between the two columns?
have the following table:
RecordKey MonthYear SD1 SD2 SD3
1 Jan 2008 6 AA 0
2 Feb 2008 10 BB 3
3 Mar 2008 12 CC 8
etc....
I need to transform it to this:
1 2 3
Jan 2008 Feb 2008 Mar 2008
6 10 12
AA BB CC
0 3 8
Can this be done automatically in SQL?
Ok .. a challenge ...
How do you swap two columns based on some condition without using a cursor ( I mean a set based solution).
Hi,
I'm pretty new to SQL and certainly new to SQL Server, so this is no doubt a simple question, but how can I take a row of data (say row 117) and swap it with another specified row of data? I know it sounds odd, but the key for each row is automatically generated so I don't want to mess with that, so I just want to swap the other fields.
Here's an example of what I mean:
----------------------------------------------
ID | headline | content | newstype | date |
----------------------------------------------
117 | test | asdfgasd| industry | 06/06|
118 | test2 | adsfg as| company | 06/06|
----------------------------------------------
What i've discovered is that the order of data in the rows isn't correct, so I want to maintain the ID fields seeing as they're in numeric order, but then use that ID to grab the rest of the row (117 for example) and replace the data for 118 with 117's data. Then do the same with 118's original data....
I need to do this because all the related rows in my table were posted on the same date, so other than that they appear on screen based on where they appear in the table, which doesn't match what the client wants...
Sorry for the long post, but as a beginner with SQL I wanted to make sure I made sense!
I need to swap two rows in a table so that i can give the user a choice to move the row data up and down. The top and bottom row values may not be in the serial order. I am using MS Sql 2000. Is there any way to do that or its really difficult big process. I really stuck with this problem can you help me out, please friends.. Your help will greatly appreciated..
Thank you very much
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
I am a newbie to Sql server and i am having a task where my current table with always two rows existing and ID column as primary key looks like below:
ID PlateNo Type Image Name
27 455 User img1.jpg
32 542 Alternative img2.jpg
And i want a sql query to modify my table so that the data should be like as shown below:
ID PlateNo Type Image Name
27 542 Alternative img2.jpg
32 455 User img1.jpg
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
TableName: EmployeeCloumns: EmployeeID EmployeeName If I do "Select * From Employee", is there a way to do something like this...
Loop through each result IterateThroughControls.ID &= dbTable & dbColumnName & "<br />"EndSo that the end result would assign ID values...EmployeeEmployeeIDEmployeeEmployeeName
I am looping through all the databases for a table and trying to insert data into two columns in that table. So evrything seems right. but when i run the program it says
Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'OrganizationID', table 'DesMoines_DEV.dbo.Organization'; column does not allow nulls. INSERT fails.
I think writing a dynamic query that produces a series of insert statements is wrong... Instead a dynamic query that execute insert statements one by one may work... But I couldn't figure how to approach..
Below is the code
SET @dbLoop = CURSOR FOR
SELECT name
FROM sys.Databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND State_desc = 'ONLINE'
[Code] ....
I am looking solutions to import csv data into my SQL database table. BUT we want to collect the data from specific columns in the csv file, (NOT the whole csv file) into SQL Database Table.
View 4 Replies View RelatedI have an table with duplicate data. I need to delete the duplicate records based on the RequestId.
I want to delete the records based on the RequestID 1001, Delete the duplicates in 1002, 1003 .
DECLARE @table TABLE ([Employee_SID] [int] NOT NULL,
[CalculatedTotalSalesYear] [numeric](19, 2) NULL,
[CalculatedTotalSalesYearAnnualized] [numeric](19, 2) NULL,
[RequestID] [int] NOT NULL)
[Code] ....
Expected Result
select * from @table
Hey all
I have a bulk insert situation that would be nice to be able to pull off. I have a flat file with 46 columns that are to go into a table. The table, I want to have a 47th column to be updated later on by means of a stored proc saying if the import into the system was sucessful or not. I have the rowterminator set as '"' thinking that would tell SQL to begin on the next row, leaving the importstatus column null but i still receive an error.
First of all, is this idea possible within this insert statement. Secondly, if so, what would be the syntax to tell the insert statement to skip that particular column. It is the last column listed in the table so it just needs to start on the next row after it inserts the last bit of data in the flatfile.
If this is not possible, is it possible to bulk insert into a temp table?
Thanks
IF (SELECT OBJECT_ID('t1')) IS NOT NULLDROP TABLE t1GOCREATE TABLE t1 (c1 INT, c2 INT)DECLARE @n INTSET @n = 1WHILE @n <= 454BEGININSERT INTO t1 VALUES (@n, @n)SET @n = @n + 1ENDSELECT name, indid, CASE indidWHEN 0 THEN 'Table'WHEN 1 THEN 'Clustered Index'ELSE 'Nonclustered Index'END AS Type,dpages, rowcntFROM sysindexesWHERE id = OBJECT_ID('T1')name indid Type dpages rowcnt---- ----- ---- ------ ------NULL 0 Table 2 454I have a table containing 454 rows of two columnsof type INT with each being 4 bytesc1 int = 4 bytes+c2 int = 4 bytes=8 bytes per rowIf I entered 454 rows : 454 * 8 = 3,632 byteseach SQL Page is 8KB = 8 * 1024 bytes= 8,192 bytesa data page header takes the first 96 bytesleaving 8096 bytes for data and row offsets.Each record uses a row offset at the end of the pageconsisting of 2 bytes. 454 * 2 = 908 bytes.8096 - 3632 - 908 = 3,556 bytes. Should this befree data bytes?For a heap table, does SQL add an internal uniqueidentifiercolumn also? or my question is when does SQL adda uniqueidentifier? I am reading Inside SQL 2000 andtrying to understand a few things.A uniqueidentifier of 4 bytes gets added when a clustered indexexists but it is NOT a UNIQUE clustered index. AND onlyif duplicate record is added those two records only geta uniqueidentifier value.But in my example it's a heap table with no indexes. Evenon a heap table with no indexes a ROWID or Uniqueidentifierget added? Based on the INSERT statement above allvalues are unique.So what am I missing to understand why 453 rowsmake one data page to be used whereas 454 rowsmake two data pages to be used?Thank you
View 1 Replies View RelatedHi ,
My Input is a flat file source and it has spaces in few columns in the data . These columns are linked to another table as a foreign key and when i try loading them in a relational structure Foreigh key violation is occuring , is there a standard method to replace these spaces .
what approach should i take so that data gets loaded in a relational structure.
for example
Name Age Salary Address
dsds 23 fghghgh
Salary description level
2345 nnncncn 4
here salary is used in this example , the datatype is char in real scenario
what approach should i take to load the data in with cleansing the spaces in ssis
Sorry for the confusing subject. Here's what im doing:I have a table of products. Products have N categories andsubcategories. Right now its 4. But there could be more down theline so it needs to be extensible.So ive created a product table. Then a category table that has manycategories of products, of which a product can belong to N number ofthese categories. Finally a ProductCategory "match" table.This is pretty straigth forward. But im getting confused as to how towrite views/sprocs to pull out rows of products that list all theproducts categories as columns in a single query view.For example:lets say productId 1 is Cap'n Crunch cereal. It is in 3 categories:Cereal, Food for Kids, Crunchy food, and Boxed.So we have:Product----------------1 Capn CrunchCategories-----------------1 Cereal2 Food for Kids3 Crunchy food4 BoxedProductCategories------------------1 11 21 31 4How do I go about writing a query that returns a single result set fora view or data set (for use in a GridView control) where I would havethe following result:Product results---------------------------------ProductId ProductName Category 1 Category 2Category 3 Category N ...------------------------------------------------------------------------1 Capn Crunch Cereal Food for Kids Crunchy foodBoxedAm I just thinking about this all wrong? Sure seems like it.Cheers,Will
View 1 Replies View RelatedHi all,
I am facing a problem on validating the data from a flat file while inserting the data into the destination table of sql server 2005 database. In my package, i have to validate the input data whether the values are coming as null or not, before inserting into the destination database. The flat file may not contain data for all NOT NULL columns. I have to find out that row(s) and reject the record. If the rows are coming as Null for the Not Null columns, the OLEDB Destination throws OLEDB exception for the constraint.
To resolve this, i have an script component in data flow, to check whether the input data is coming as null. I have added the output column of boolean type to the script component, it will be assigned to TRUE when there is null for the Not Null column in the script.
And in the follwing conditional split, i am checking the flag for TRUE to reject the record.
Is there any other way to handle this validation?
Regards
Madhav
Running SQL Server 2005, trying to develop an SSRS report to basically pivot a table of data with multiple columns.
Here's the basic source table:
Day Cases Referrals Vends
1 291 0 0
2 293 1 0
3 293 1 1
And I want to display it as:
Day 1 2 3
Cases 291 293 293
Referrals 0 1 1
Vends 0 0 1
I thought I could use a matrix for this but I can't seem to get it worked out. Is this even possible?
The Day number is meant to represent the day of the month and the user would input a start and ending date parameter.
I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.
View 8 Replies View RelatedHi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!
View 4 Replies View RelatedHi:
I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks
MA2005
hi, we have got a new server at work running SBS 2003. the old server was SBS 2000 and it has a database on it. Both are running the SQL server program. Can anyone give me instructions to transfer these over (i have no idea how). i realise this has probably been asked before but i dont have the time to search so if someone could link a thread with instructions that would be good. cheers.
View 2 Replies View RelatedWhat is a good method/mechanism to swap the position of multiplecolumns?For instance, tblXZY has the followings columns and respectivepositions:tblXZY======xyzUUID 1fn 2ln 3phone 4email 5city 6state 7....Now, I need to make city as 2 and state as 3. BTW, the tblXYZ tablehas data there. Copy/select all the data into a new table withdesired column position would require constraints re-mapping etc.,which seems quite a bit hassle.Thanks.
View 6 Replies View RelatedI have setup logshipping in sql 2005. i have a primary and secondary server only.
Suppose for maintenace i bring primary down. . i have to manually bring the secondary server online. usnig restore databse with restore.
1) so far so good. But what now. How can i re-sync the old primary with the new primary?
2) can i return the original secondary back to standby mode so that the primary can resume its role and the log shipping process proceeds.
please need rescue- complex update logic
this is my table
1
2
3
4
5
EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44
fld444
fld5
fld55
fld555
1111
A
B
C
7
8
9
G
H
I
J
K
L
M
N
2222
N
M
L
K
J
I
H
G
F
E
D
C
B
A
3333
1
2
3
A
B
C
C
E
Y
I
O
W
Y
P
i need to update for example the eployee 1111 with employee 3333
but with swap ( take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333
in field- fld2,fld22,fld222 )
Code Snippet
---update eployee 1111 with employee 3333
-so
if i put the value 2
than ------------------ swap value between 2 employee
set empid1= 1111
set empid2=3333
value_swap=2
if value_swap=2
than
update fld2,fld22,fld222
with fld2,fld22,fld222
------------------- take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333
--------------------in field- fld2,fld22,fld222
value_swap
=1
=2
=3
=4
=5
EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44
fld444
fld5
fld55
fld555
1111
A
B
C
A
B
C
G
H
I
J
K
L
M
N
2222
N
M
L
K
J
I
H
G
F
E
D
C
B
A
3333
1
2
3
7
8
9
C
E
Y
I
O
W
Y
P
Code Snippet
---update eployee 2222 with employee 1111
-so
if i put the value 5
than ------------------ swap value between 2 employees
set empid1= 1111
set empid2=2222
value_swap=5
if value_swap=5
than
update fld5,fld55,fld555
with fld5,fld55,fld555
------------------- take the value of employee 1111 in field- fld5,fld55,fld555 and swap value between employee 3333
--------------------in field- fld5,fld55,fld555
=1
=2
=3
=4
=5
EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44
fld444
fld5
fld55
fld555
1111
A
B
C
7
8
9
G
H
I
J
K
W
Y
P
2222
N
M
L
K
J
I
H
G
F
E
D
C
B
A
3333
1
2
3
A
B
C
C
E
Y
I
O
L
M
N
TNX FOR ALL THE HELP I GET IN THIS Forum
I've 2 measures that I would like to swap according to which member in a dimension are selected by the user:Ex.
[Measure].[A]
[Measure].[B]
Dimension called TEMP with 2 members (HOT and COLD).So if the user choose HOT it should show [Measure].[A] and if the user choose COLD it should show [Measure].[B]If the dimension is not included in the analysis by the user or if no dimension attribute are choosed then it should default to [Measure].[A].How to do that as Calculated Script in the SSAS cube ?
Hi,
I have two tables
Table Source
{
category varchar(20),
LastUpdate datetime
}
Table Destination
{
SubjectId varchar(20),
SubjectDate datetime,
category varchar(20),
LastUpdate datetime
}
Please note that the number columns are different in each table.
I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.
And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.
Is there anyway to bulk insert into these columns.
Your quick response will be appreciated
~Mohan Babu
Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
I need swapping values in rows of tables based on primary.
I will explain my scenario with the below example.
ID Name Value
--- -------- --------
1 name1 202
2 name1 203
3 name2 203
4 name2 202
5 name3 204
In the above example, I want to swap Value 202 and 203 but its not straight forward. I want to swap in a way that, I need to group by Name column and check if 203 comes first in the group then swap else don't.
In the above example, I need to swap the values for name2 but not for name1. Below query is performing a straight forward swap.
UPDATE TblTest
SET Value
=
CASE
WHEN
Value
=202
THEN203
ELSE202
END
WHERE
Valuein(202,203)
We are hosting a 140 GB database on SQL Server Version 7 and Windows2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to theOS) and a 4 GB swap file. (The PeopleSoft CIS application will notpermit us to upgrade to SQL 2K.) We recently upgraded the server from4 to 8 cpus and the SAN disks from 10K to 15K drives. But we stillhave heavy SAN disk usage, sometimes at 100%, and read queues oftenaveraging 4 and peaking at 12.The CPUs are loaded at only 20-50%. (The politics are such that it iseasier to throw hardware at the problems.)We are looking into archiving, converting from RAID 5 to RAID 10, andat splitting the mdf file into several file groups in an attempt toget more disk heads into play. (We are also looking at rewriting theapplication to reduce the read volume and frequency.) Does anyone haveany other ideas?Incidentally, does swapfile get used when the physical memory equalsthe OS maximum? If the OS can only see 2 GB and we have 2 GB (actually4 GB) of memory, is the 4GB local swap file on the C drive unused?Thanks in advance for any assistance.
View 1 Replies View RelatedHello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
View 2 Replies View RelatedHere's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS):
Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms.
This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match.
Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear.
Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package.
Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 12 buffers were considered and 12 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
View 12 Replies View Related