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


ADVERTISEMENT

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 :: String Format To Be Formed Out Of A Column

Nov 3, 2015

I am trying to find a solution to get the result set to fetch a particular string format from a table in my database, which has a column of NVARCHAR data type

CREATE TABLE #ActivityComments(Comments NVARCHAR(MAX))

INSERT INTO #ActivityComments VALUES('This is the study code for Field Phase S14-04932-01')
INSERT INTO #ActivityComments VALUES('Phase reporting has the study S15-04931-01 which is obselete')
INSERT INTO #ActivityComments VALUES('Phase running study code S14-04932-02 is not valid')

The output of the query should be like:

S14-04932-01
S15-04931-01
S14-04932-02

Is there any way possible to achieve this..

View 7 Replies View Related

SQL 2012 :: Insert Entire Line Into One Column Of The Table

Oct 21, 2014

I convert EBCDIC file to text file at C:orderorder.txt. There are about 1400000 rows. The length of line is 1500.

How to insert entire line into one column of the table? (only one column, named as [LineDetail])

View 1 Replies View Related

SQL 2012 :: Sybase To Server Migration - Date Column Format

Jun 18, 2014

I am currently working for the Sybase to Sql Server migration project and have been able to test migrate few tables using SSIS. After migration, i was doing some data comparision and could see the date formats are different between Sybase and Sql server. However, there are no issues with data like day in sybase becoming month in Sql server except the formats are different.

Do I need to act on this date formats? Not sure if this would cause any issues in front end application that will consume sql server date data.

View 1 Replies View Related

SQL 2012 :: Insert ID Column Value Into Table Depending Upon Input Filename

May 19, 2015

I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.

Lets Say
Location1 : C:LoadFilesImport
Location2 : D:LoadFilesImport

Location one has three different set files starting with

First Set: AP_1, AP_2,AP_3,
Second Set: VD_1, VD_2, VD_3,
Third Set: BK_1,BK_2,BK_3,

This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101

AP_1 goes to Table1
AP_2 goes to Table2
AP_3 goes to Table3

If the file name Starts with VD_ , custiD values should be as 201
If the file name Starts with BK_ , custiD values should be as 301

after processing all these files in first location, the SSIS looks for files in second location and does the same?

--How to achieve CustID depending upon file name ?

View 0 Replies View Related

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

Jun 19, 2008

Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View 6 Replies View Related

Cannot Insert The Value NULL Into Column SnapshotDataID, Table ReportServerTempDB.dbo.SessionData; Column Does Not Allow Nul

May 3, 2007

I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks

View 1 Replies View Related

Insert Column Concatenated For Each Column In Another Table

Jun 13, 2012

I have a table where one column is an id card number (col1) and another is another column (col2). In col2 I have various values for each col1 ie col1 can have various values of col2. How can i insert col1 concatenated for each col1 in another table?

View 2 Replies View Related

SQL 2012 :: Date Format In Text Column With Many Other Inputs

Feb 10, 2014

How to convert MM/YYYY to MM/DD/YYYY and also YYYY to 01/01/YYYY.

This column is text column and has many other inputs.

View 4 Replies View Related

SQL 2012 :: Select And Format A Text In A Varchar Column

May 9, 2014

One purchased app stored user's multiple lines input into a varchar column in which including char(13) and char(10).

My app need to select this value and format to multiple lines text in one text box.

How to code to output it?

View 5 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 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

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

Insert Into Column From Another Table Column

Nov 27, 2014

I came from DW admin background and new to SQL programming. I have 3 tables:

CUSTOMER - cust_id, cust_name, cust_state
SALES_HEADER - sales_id, cust_id, sales_amount
SALES_DETAIL - prod_id, sales_id, prod_name, prod_price

I am trying to insert into SALES_HEADER table from CUSTOMER and SALES_DETAIL tables. Here are the SQL queries that I am trying to insert.

insert into dbo.SALES_HEADER (SALES_ID,SALES_AMOUNT)
select SALES_ID, sum(PROD_PRICE)
from BOBJ.dbo.SALES_DETAIL
group by SALES_ID;

The above query is working good & to get cust_id from CUSTOMER:

Update dbo.SALES_HEADER
set CUST_ID = (select CUST_ID from dbo.CUSTOMER
where dbo.SALES_HEADER.CUST_ID = dbo.CUSTOMER.CUST_ID)
where CUST_ID is null;

This query is not working & getting message - 6 rows updated successfully.

But using select * from dbo.SALES_HEADER, CUST_ID is still showing as NULL.

What did I do wrong and is there a way to write single query rather than two queries.

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 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 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:

EXEC @AlterSQL

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 2008 :: How To Get Column From Another Table And Insert Into Other Table

May 18, 2015

I have two table 'Cal_date' and 'RPT_Invoice_Shipped'.Table cal_data has column month_no, start_date and end_date. And table RPT_Invoice_Shipped has columns Day_No, Date, Div_code, Total_Invoiced, Shipped_Value, Line_Shipped, Unit_Shipped, Transaction_Date.

I am using below insert statment to insert data in RPT_Invoice_Shipped table.

insert into [Global_Report_Staging].[dbo].[RPT_Invoice_Shipped]
(Day_No, Date, Div_code, Total_Invoiced, Transaction_Date)
select , CONVERT(DATE,Getdate()) as Date, LTRIM(RTRIM(div_Code)),
sum(tot_Net_Amt) as Total_Invoiced, (dateadd(day, -1, convert(date, getdate())))
from [Global_Report_Staging].[dbo].[STG_Shipped_Invoiced]
WHERE CONVERT(DATE,Created_date )=CONVERT(DATE,Getdate())
group by div_code

while inserting in column Day_No in RPT_Invoice_Shipped table, I have to use formula (Transaction_Date-start_date+1) where Transaction_Date from STG_Shipped_Invoiced and start_date from Cal_date table. I was using datepart (mm, Transaction_Date) so it gives month_no, and this month_no we can join with month_no of Cal_date table and fetch start_date from Cal_date table, so that we can use start_date for formula (Transaction_Date-start_date+1).

But I am getting difficulty to arrange this in above query. how to achieve this?

View 1 Replies View Related

SQL Server 2012 :: Update A Column Using Value Of Another Column

Sep 9, 2015

I have a student table like this studentid, schoolID, previousschoolid, gradelevel.

I would like to load this table every day from student system.

During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.

My question in my merge statement something like below

Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid

When matched then update

set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;

My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?

View 4 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
WHERE t1.Key2 IS NULL
AND t1.Type LIKE 'TypeA'
AND t1.Key1 NOT IN
(SELECT Key1
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 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,

[code].....

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 :: Table With Column Data - Convert Varchar To Int

Jul 8, 2015

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

1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68

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

Transact SQL :: Using Bulk Insert - Invalid Column Number In Format File Error

Jun 5, 2015

I try to import data with bulk insert. Here is my table:

CREATE TABLE [data].[example](
 col1 [varchar](10) NOT NULL,
 col2 [datetime] NOT NULL,
 col3 [date] NOT NULL,
 col4 [varchar](6) NOT NULL,
 col5 [varchar](3) NOT NULL,

[Code] ....

My format file:

10.0
7
1  SQLCHAR 0  10 "@|@" 2 Col2 ""
1  SQLCHAR 0  10 "@|@" 3 Col3 ""
2  SQLCHAR 0  6 "@|@" 4 Col4 Latin1_General_CI_AS

[Code] .....

The first column should store double (in col2 and col3) in my table

My file:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS
2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS

My command:
bulk insert data.example
from 'R:epoolexample.csv'
WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)

Get error:
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".

I changed some things as:
used ";" and "," as column delimiter
changed file type from UNIX to DOS and adjusted the format file with "
" for row delimiter

Removed this line from format file
1  SQLCHAR 0  10 "@|@" 2 Col2 ""
Nothing works ....

View 7 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.

--ORDERDETAIL TABLE

SKUO   QTYO    ORDERIDO

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] ....

CURRENT PIVOT ONLY GOES TO 150 - BELOW

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:

541,'A,B,C'
345,'B,C'
234,'A,C'
657,'D,E'
435,'D'

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

541,'A'
541,'B'
541,'C'
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







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