May 14, 2013 at 12:07 pm
Hi,
I have a query where it returns exact same value for all fields except 1 field.
Example
CREATE TABLE #Temp1 ( recid int identity (1,1),SiteID INt,TankID INT,TNo INT,PDate DateTime,Gallons INT,Status1 VarCHAR(10))
INSERt INTO #Temp1 (SiteID ,TankID ,TNo ,PDate ,Gallons ,Status1)
SELECT 1,2,3,'12-08-2012',2,'On'
UNION
SELECT 1,2,3,'12-08-2012',2,''
SELECT * FROM #Temp1
DROP TABLE #Temp1
The above selects are results from different tables. Is there any way I can eliminate Status1 ='' while inserting in the table ?
May 14, 2013 at 12:36 pm
PSB (5/14/2013)
Hi,I have a query where it returns exact same value for all fields except 1 field.
Example
CREATE TABLE #Temp1 ( recid int identity (1,1),SiteID INt,TankID INT,TNo INT,PDate DateTime,Gallons INT,Status1 VarCHAR(10))
INSERt INTO #Temp1 (SiteID ,TankID ,TNo ,PDate ,Gallons ,Status1)
SELECT 1,2,3,'12-08-2012',2,'On'
UNION
SELECT 1,2,3,'12-08-2012',2,''
SELECT * FROM #Temp1
DROP TABLE #Temp1
The above selects are results from different tables. Is there any way I can eliminate Status1 ='' while inserting in the table ?
Not really sure what you are asking. I think you are asking if you can skip certain columns for some rows while inserting. NO, that won't work. If you specify the columns you are going to insert and then don't provide values how would sql know which column(s) to skip? Also in your example you are using a union. Any query that has a union MUST have the same number of columns and matching datatypes. If you want to make it NULL instead of an empty string just use the literal NULL instead of ''. Does that answer your question?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2013 at 4:52 pm
Can you insert Alll rows into the Temp Table
And Then Delete the Records on Status1= ''
And then SELECT the Data
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply