SQL Server 2012 :: Convert All The Column Of Table To ToBase64String?

Feb 17, 2015

I want to convert all the column of table to ToBase64String.

Public Class ScriptMain
Inherits UserComponent
Dim md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider()
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim columnContents As Byte() = UnicodeEncoding.Unicode.GetBytes(Row.Col001 + Row.Col002 + Row.Col003 + Row.Col004 + Row.Col005 + Row.Col006 + Row.Col007 + Row.Col008 + Row.Col009 + Row.Col010 + Row.Col011 + Row.Col012 + Row.Col013 + Row.Col014)
Dim hash As Byte() = md5.ComputeHash(columnContents)
Dim hashString As String = Convert.ToBase64String(hash, Base64FormattingOptions.None)
Row.RowChecksum = hashString
End Sub
End Class

View 9 Replies


SQL Server 2012 :: Table With Column Data - Convert Varchar To Int

Jul 8, 2015

I have a table with column "Data" as VARCHAR, with entries like below.


I want to select the data by converting varchar to int for numeric values and for alphanumeric it should display as it is.

SELECT CAST(dataAS INT) FROM record_tab

getting below error

Conversion failed when converting the varchar value 'A1'

View 9 Replies View Related

SQL Server 2012 :: Convert Rows To Columns Using Dynamic PIVOT Table

Feb 3, 2015

I have this query:

SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],

[Code] ....

I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.

This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).

The question is how do I PIVOT and preserve the percentage of ownership?

View 3 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

SQL Server 2012 :: Format Value Of A Column And Insert It Into Another Column Of The Same Table?

Sep 18, 2014

A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.

I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.

The text before ;# (35106) should be inserted in to another column of the same table.

Below is the table structure:

create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int

View 2 Replies View Related

SQL Server 2012 :: Convert Column Data Into Row Data?

Dec 12, 2014

I have data like this in a table

ID test_date Score
001 4/1/2014 80
001 5/4/2014 85
001 6/1/2014 82

I want to convert the data into a row like this:

ID test_date1 score1 test_date2 score2 test_date3 Score3
001 4/1/2014 80 5/4/2014 85 6/1/2014 82

How can I do that with T-SQL?

View 1 Replies View Related

SQL Server 2012 :: Update Table From Variable Table Column?

Oct 6, 2014

I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.

This is my statement:

UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)

View 2 Replies View Related

SQL Server 2012 :: How To Update One Column Value To Another In Same Table

Jun 21, 2014

my table payment_details structure is

payment_id payment_code
1 null
2 null
3 null
4 null

here payment_id is a primary key and i need to update the whole payment_id column to payment_code column.so i just tried the below query

update payment_details
set payment_code = payment_no
where payment_code is null

but it shows subquery error?

View 3 Replies View Related

SQL Server 2012 :: Table Variable Update Column

May 29, 2014

I am writing a query to update table variable. It is throwing me some error.

I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table

update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID

View 9 Replies View Related

SQL 2012 :: Adding Column To A Table Over Linked Server

Apr 22, 2015

I have a situation that I need to add a field to a table over linked server. The specifications of this is dynamic and it is being done in TQL / Stored procedures and this can not change. My code is generating the statement just fine and if I copy paste it to a new SSMS window and execute it WORKS.. The problem is I need to dynamically generate the statement (I am doing that just fine, I THINK). THEN I need to execute the statement IN THE SPROC, this part is not working.

Here is the code:

SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE '
+ @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)

The above Creates this when I expose it via a PRINT statement:

addb15.[FSParallel].dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int'

After I create the statement I use:


And this returns the following error:

Msg 2812, Level 16, State 62, Procedure ETLDynamicImport, Line 244
Could not find stored procedure 'FSParallel.dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int''.

<hr noshade size='1' width='250' color='#BBC8E5'>

View 1 Replies View Related

SQL Server 2012 :: How To Add A Primary Key For Existing Column In The Table

Oct 19, 2015

How to add a primary key for existing column in the table

View 8 Replies View Related

SQL Server 2012 :: Find Out If Whole Column Of Data In A Table Is Empty?

Dec 2, 2013

I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:

If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END

However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.

View 9 Replies View Related

SQL Server 2012 :: Find Rows Where Value In Column Not Found In Another Row In Same Table

Jul 16, 2014

Can't seem to make this SQL query work!

Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...

I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row

So, given the data

**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB

I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.

I have tried this and it doesn't work...

SELECT t1.Key1, t1.Key2, t1.Type
FROM Table1 t1
AND t1.Type LIKE 'TypeA'
AND t1.Key1 NOT IN
FROM Table1 t2
WHERE t1.Key1 = t2.Key2
AND t1.Key1 <> t2.Key1
AND t2.Type LIKE 'TypeB')

View 2 Replies View Related

SQL Server 2012 :: Table Partitioning Based On Date Column

Aug 25, 2014

We have a database and have 6-7 growing tables. All the tables have Primary and foreign key relation. I want to do partition based on the date column.

I need 3 partitions

First partition has to hold present data
second partition need to hold the previous year data (SAS storage)
Third partition need to hold all the old data and need to be in the archive database

I understand that first we need to disable the constraints (Indexes PK & FK)
Then create partition function and partition schema
Then Create the Constraints again

View 9 Replies View Related

SQL Server 2012 :: New Column In Existing Table And Uploading Data

Jan 13, 2015

Need to change the datatype of existing column which has huge data.

I'm performing below steps

1. Create new column with correct datatype in the same table
2. copy data into new column
3. drop indexes on column
4. <<<>>>
now the existing column also has many SP dependent and I do not wish to drop them.
5. rename existing column to xxx
6. rename new column to correct column
7. drop old column
8. make required indexes

View 9 Replies View Related

SQL Server 2012 :: How To Add Column To Multiple Table Using Single Script

Feb 12, 2015

I am looking a script which allow me add single coilumn to multiple table of my database.

For Example :-

I am having 4 table

1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table

Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.

Alter table EMP
Add Rowchecksum varbinary(8000)

View 1 Replies View Related

SQL Server 2012 :: Select Rows With Sum Of Column From Joined Table?

May 2, 2015

I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.

What I tried, but what returns NULL for purchasedamount:

SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2

How can I achieve what I need?

Here are my table definitions and data:

/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,


View 0 Replies View Related

SQL Server 2012 :: Non-Clustered Column Store Index On Table

Jun 18, 2015

I have created NONCLUSTERED index on table but my report is taking more time that's why i created columnstore NONCLUSTERED index on the same table but i have one query, if any table have row and column level index(same columns in index) . Which index query will consider.

View 1 Replies View Related

SQL Server 2012 :: Update Temp Table Where The Column Names Are Unknown

Dec 26, 2013

In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.

So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:

UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))

However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.

how can I get the column names of a Temp table dynamically while doing an Update?

View 4 Replies View Related

SQL Server 2012 :: Optimize PIVOT Table To Include Unlimited Column And QTY Of SKU

Jan 24, 2014

Reformatting data in a PIVOT Table or find a better way to display.



KUM    1   12345
KUS    2   12345
SUK    1   12345
KHN    4   12345
DRE    1   12345

[Code] ....

Number of SKU's in order could be over 1000.

Looking to change my current pivot table to allow an unlimited number of SKU's and add QTY.

Data I am looking to get.  MAX of 15 SKUS Per line.

ORDERID    SKU1    QTY1    SKU2    QTY2    SKU3    QTY3    SKU4    QTY4    SKU5    QTY5    SKU6    QTY6    SKU7    QTY7    SKU8    
QTY8    SKU9    QTY9    SKU10   QTY10   SKU11   QTY11   SKU12   QTY12   SKU13   QTY13   SKU14   QTY14   SKU15   QTY15  
12345  KUM 1   KUS 2   SUK 1   KHN 4   DRE 1   HGF 2   FDE 1   CDS 1   GYT 1   POI 3   LKH 2   TTT 4   JHG 8   YUI 2   WQE 1  
12345  PMN 1   BVC 1   ABD 1  

[Code] ....


SELECT     PKGCUSTOM4, [1] AS [SKU1], [2] AS [SKU2], [3] AS [SKU3], [4] AS [SKU4], [5] AS [SKU5], [6] AS [SKU6], [7] AS [SKU7], [8] AS [SKU8], [9] AS [SKU9], [10] AS [SKU10],
                      [11] AS [SKU11], [12] AS [SKU12], [13] AS [SKU13], [14] AS [SKU14], [15] AS [SKU15], [16] AS [SKU16], [17] AS [SKU17], [18] AS [SKU18], [19] AS [SKU19],

[Code] ....

View 6 Replies View Related

SQL Server 2012 :: Normalize Values From Column Split Using Tally Table?

Apr 23, 2014

I have a table that has the following structure:

EntryID int,
Categories varchar(200)

values look like:


what I want to do is extract the Categories column to a normalized separate table:

345,'B' ....

I found the split using the tally table useful to split one-by-one, but how can it be applied when you are referring to a table?

View 2 Replies View Related

SQL Server 2012 :: Sorting By Alphanumeric Table Column Of NVARCHAR Datatype

Sep 24, 2015

I am trying to sort my sql resultset by an alphanumeric column of a table which is of NVARCHAR datatype. The sample data is given below:

CREATE TABLE #Activities(activityName NVARCHAR(100))

INSERT INTO #Activities VALUES('Field phase S14-04932-01')
INSERT INTO #Activities VALUES('Phase reporting')
INSERT INTO #Activities VALUES('Phase running')

[Code] ....

The output of the query is like this:

E1 0DAA1
E2 0DAA2

[Code] .....

The output what I require is this:

E1 0DAA1
E2 0DAA2

[Code] ....

View 9 Replies View Related

Convert Row Of Table To Column

Jan 17, 2007

tblname rowno colname colvalue
emp 1 Title Mr
emp 1 firstname XYZ
emp 1 lastname M
I want to get as

title first_name lastname

View 1 Replies View Related

SQL Server 2012 :: Querying A Supersession Two Column Table With Multiple Supersessions In Both Columns

Jan 29, 2014

I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:

Supersessions Table

RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191


The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?

View 9 Replies View Related

SQL Server 2012 :: Parameter Datatypes Linked To Underlying Table Column Datatype

Jun 5, 2014

In Oracle when i create any procedure i define parameter datatype linked to under lying table.

For ex
create procedure testprocedure
(param1 customer.name%type,
param2 customer.salary%type )

Here i have defined param1 and param2 with datatype of name and salary of customer table respectively.

This way i do not need to worry about modifying param1 and param2 datatypes when datatypes of name & salary of customer table changes in future.

How can i accomplish this in SQL server.

View 2 Replies View Related

SQL Server 2012 :: Number Of Column Mismatch In Source File And Staging Table

Apr 14, 2015

I am having one store procedure which use to load data from flat file to staging table dynamically. everything is working fine.Staging_temp table have single column.All the data stored in that single column below is the sample Data.



View 2 Replies View Related

Sql Query Summerize The Detail Table In Order To Convert Its Trans_type Into Column

Jan 15, 2008

I have detail table like

date item_id grade in out
------------- --------------- ------- -------- ----------
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8

Result View

Item_id A B O T Total

001 10 0 8 -10 8
002 0 0 1 0 1
003 0 8 20 -10 18

where grade could be any Alphabet, the column of result query could varies
each grade contain sum of (in-out) of all item_id of detail table.


View 4 Replies View Related

SQL Server 2012 :: How To Convert Varchar Value To INT

Apr 25, 2014

I am new to T-SQL development and here's what I am trying to do.

declare @cmd varchar(255)
set @cmd = 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)'

while (@cmd <=13)
insert into #dbcheck values (@cmd)
Set @cmd = @cmd + 1

but it is giving me an error

'Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)' to data type int.'

Note - #dbcheck has only one column id int

View 3 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.



View 4 Replies View Related

SQL Server 2012 :: Convert Parts Of String To Int

May 2, 2014

I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".

I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.

CREATE TABLE #tmp(duration VARCHAR(20))

VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec

And this is what I have so far

WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0

[Code] ...

I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.

View 3 Replies View Related

SQL Server 2012 :: Convert Integer To Date

Oct 16, 2014

In VBA, CLng(Now) will return the integer portion of a date CLng(Now) returns 41928, CDate(41928) then returns 10/16/2014. Is there something equivalent in SQL Server that will allow me to convert an integer value to a date?

In short, how can I convert a 100 year date to Gregorian (any format)?

View 3 Replies View Related

SQL Server 2012 :: Convert Integer To Time?

Dec 3, 2014

I've got a column that holds a time value in Integer form as a number from midnight (IE military time) with no leading 0s or colons.

0 = 00:00
30 = 00:30

1700 = 1700 etc...

How can I convert this to a real time field?

View 9 Replies View Related

SQL Server 2012 :: Convert Varchar To Time

Mar 9, 2015

I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is 335:55:20.97 duration time. My code is below.

CPUTM = 335:55:20.97 duration time
CPUZIPTM = 0:00:01.96 duration time

select * FROM [SMF_DATA].[dbo].[System_Management_Facility]
WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM))) > '02:00:00.00')

View 2 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved