T-SQL (SS2K8) :: Merge Standardized Data Dynamically?
Sep 2, 2014
I have table of standardized data that I'm merging into from an Import table.
This import table may have the [ContactName]. I one row in the import table has the contact name then every row would have the contact name.
What I'd like to be able to do is something like this in the UPDATE portion of the Merge (I don't care about the insert phase, since I can insert a null [ContactName] since I'm not overwritting an existing [ContactName]
MERGE INTO [dbo].[Standardized] AS [target]
USING [dbo].[ImportDestination]
AS [Source]
ON [Source].[Key] = [Target].[Key]
WHEN MATCHED
THEN UPDATE
SET[ContactName] = CASE WHEN source.[ContactName] IS NOT NULL THEN source.[ContactName] else target.[ContactName];
Is anything like that possible and if the code above works it would only work on a row by row bases. Is it possible to figure out if any [ContactName] has data and if so possibly overwrite an old [ContactName] with a null?
View 1 Replies
ADVERTISEMENT
May 19, 2015
DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)
DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'
Actually the year stating form july.
Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.
So what I need is dynamically I want to delete the data every year prior to current year.
View 4 Replies
View Related
Jun 26, 2014
A user is wanting to run a query against the current 3 active databases. These change every month on the 1st e.g. Test-06-14. Therefore I want to set up a dynamic query which will always use the current database. I believe I am almost there but I cannot set the USE @DatabaseName dynamically yet.
DECLARE @DB_Name varchar(100)
DECLARE @DatabaseName varchar(100)
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE database_cursor CURSOR FOR
[Code] ....
View 9 Replies
View Related
Sep 5, 2014
I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic.
i.e. some time 5 columns and some time 10 columns.
Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.
Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.
During each load we can drop table, No issue and we can handle this through SSIS Package.
View 2 Replies
View Related
Aug 28, 2014
I have an SSIS package that puts together a series of queries to check all text fields in all tables of a database for a set of "invalid" characters. I know the root query works as we have been running it manually for quite a while. However, now that I have changed it to build dynamically, I cannot figure out why it does not work.
Here is the code. The EXEC (@sSQL) returns 3 records. It just so happens that these 3 records are the only ones in the database with a '?' in the field. The very bottom statement returns 0 records. I cannot figure out what the difference is. When I do PRINT (@sSQL), it looks fine to me, except that @Pattern is fully printed.
DECLARE @Pattern NVARCHAR(MAX)
DECLARE @sSQL NVARCHAR(MAX)
DECLARE @1 nvarchar(max)
DECLARE @2 nvarchar(max)
DECLARE @loop int
[code].....
View 9 Replies
View Related
Nov 9, 2014
I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically
drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)
[Code] .....
Now my next step is verify if the variable is blank or not how do i do that ?
How do i verify all of the columns one after the other .
I am after the statement like this dynamically
-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '
I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.
View 4 Replies
View Related
May 21, 2015
I have two tables:
tblServer
tblInstance
tblServer represents a server, tblInstance represents any SQL Server instances present on the server. They're related thru srvID.
I'd like to write a query that gives me servers with all instances, on one row. This dataset will populate a List in an SSRS report.
I have this but it's clunky and does not provide for more than 2 instances on the server. I'd like this to account for any number of instances all on one row without having to hard code multiple joins:
with serverInfo as
(
select
srv.srvType as Type
,srv.srvName as ServerName
,srv.srvIP as ServerIP
,ins.instNetName as InstanceNetName
[Code] .....
View 3 Replies
View Related
May 27, 2015
UPDATE Report
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.
View 4 Replies
View Related
May 27, 2015
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId
[Code] ....
But CASE statement is not working...
View 7 Replies
View Related
May 27, 2014
I've One table that contains all current appointments booked as below.
RNappt_idclinic_idderived_location_idprovider_idappt_date_timeappt_date_endtime
1102186011435/27/14 8:305/27/14 9:00 AM
2102196011435/27/14 9:305/27/14 10:00 AM
3102206011435/27/14 10:305/27/14 11:00 AM
4102216011435/27/14 14:005/27/14 2:30 PM
5102226011435/27/14 16:005/27/14 4:30 PM
6102236011435/27/14 16:305/27/14 5:00 PM
for Same Clinic,location and Provider there's a schedule data for same day 24 hours
having every five mintues as below:
clinic_idderived_location_idprovider_idstart_timeendtimeunblock
601143 8:01 8:040
6011438:05 8:100
601143
60114313:0113:041
601143
60114313:5514:001
601143 1
60114317:5518:000
60114323:550:001
for rows having no start and endtime assume it as regular intervals.
So i need to show available appointment with duration one hour with the available schedule which is for every five minutes
Like My first appointment for today starts at 8:30 but 8- 8:30 is unblock so there could be an appointment but as this chunk is less than 60 so need to create it
For Schedule table below is what i've to create for temporary basis as this will be available in nightly load in a table.
DECLARE @num int=5
,@LASTtime TIME =CAST('23:55' as TIME)
,@Time TIME =CAST('00:00' as TIME)
,@Timeprev TIME =CAST('00:00' as TIME)
WHILE ( @Time<>@LASTtime)
BEGIN
[code]....
View 8 Replies
View Related
Jul 8, 2015
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)
[Code] ....
View 7 Replies
View Related
Aug 22, 2012
I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.
In the long sql, here is a part of it,
When Matched and
((Student.SchoolID <> esis.SchoolID
OR
Student.GradeLevel <> esis.GradeLevel
OR
Student.LegalName <> esis.LegalName
OR
Student.WithdrawDate <> esis.WithdrawDate
Student.SPEDFlag <> esis.SPEDFlag
OR
Student.MailingAddress <> esis.MailingAddress)
Then update
Set Student.Schoolid=esis.schoolid,
.....
My question is how about if the column has null values in it.for example
if schoolID is null in production table is null, but in staging table is not null, will the <> return true.or if either side of <> has a null value, will it return true.
I don't want it to omit some records and causing the students records not get updated.If not return true, how to fix this?
View 9 Replies
View Related
Mar 16, 2014
I am trying to insert new records into the target table, if no records exist in the source table. I am passing user specific values for insert, but it does not insert any values, nor does it throw any errors. The insert needs to occur in the LOAN_GROUP_INFO table, i.e. the target table.
MERGE INTO LOAN_GROUP_INFO AS TARGET
USING (SELECT LGI_GROUPID FROM LOAN_GROUPING
WHERE LG_LOANID = 22720
AND LG_ISACTIVE = 1)
AS SOURCE
[Code] .....
View 8 Replies
View Related
Jul 23, 2014
Can we insert into multiple table using merge statement?I'm using SQL Server 2008 R2 and below is my MERGE query...
-> I'm checking if the record exist in Contact table or not. If it exist then I will insert into employee table else I will insert into contact table then employee table.
WITH Cont as
( Select ContactID from Contact where ContactID=@ContactID)
MERGE Employee as NewEmp
Using Cont as con
[code]...
View 2 Replies
View Related
Apr 16, 2014
I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).
I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.
CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL
[code]....
View 4 Replies
View Related
Oct 21, 2014
Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City
, A3z103st State
[code]...
View 1 Replies
View Related
Sep 14, 2015
I would like to have a 'counter' table which will hold the last used number and return a new number. This is my schema:
if object_id('tempdb.dbo.#Counter', 'u') is not null drop table #Counter
go
create table #Counter (
Id int not null
)
go
if exists (select * from #Counter)
update #Counter
set Id = Id + 1
output inserted.Id
else
insert into #Counter (Id)
output inserted.Id
select 1
If the table is empty it returns 1 else it returns the next number (Id + 1). But this query is not atomic (i guess...?) so it could evaluate that the #Counter table is empty and then try to insert into the table, but inbetween someone else executes the insert also. Could this query be rewritten with the merge statement so that the whole operation is atomic?
View 9 Replies
View Related
Feb 27, 2014
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
View 6 Replies
View Related
Jul 20, 2005
Hisuppose that I have a set named 'my_set' wich contain'aaa','bbb','ccc'and I want to alter with an sql command the structure to get 'ddd' inaddition. How can I do that?
View 5 Replies
View Related
Feb 26, 2008
Hi,
Curently I am using a DTS package which is used to import data from Excel sheet into sql dollar table.
Now, the no. of Excel sheets is more than one and everytime the DTS package and VB Code has to be updated and sql dollar tables has to be increased to the no. of Excel sheets available.
The DTS package being executed by VB Code(.EXE).
How can I modify the DTS package and VB Code so that the import can be done dynamically irrespective of no. of Excel Sheets.
If anybody have a solution for this ,
Kindly let me know.
Regards,
Srinivas Alwala
View 3 Replies
View Related
Jul 17, 2015
I have a situation where i have a transactional fact table which consists of date, row type, order number and value. Simple example below
Date, RowType, OrderNo, Value
01-May, New, A1, 100
01-Jun, Change, A1, -10
01-Jul, Invoiced, A1, -90
What I need to be able to do is somehow select based on a day, the total value of open orders. I have tried to do this in the database but it becomes fixed and quite cumbersome (this is a simplified example in reality i have line information and line component information).I am not hugely skilled with MDX and SSAS but know there are some semi-additive functions i want somebody to be able to pick a day and have the total value of only open orders.
View 2 Replies
View Related
Jun 19, 2007
I am looking a way to export SQL Server 2005 DB tables to Access file dynamically. Like if i have added or removed any tables from SQL DB then when i run SSIS package it should export that table with data to Access file. is there any easy way to do this. if it is not them please someone please tell me which controls should i look at and which technique i should use to do this.
View 4 Replies
View Related
Mar 11, 2008
In my SSIS package, I have a data flow task
I am loading a CSV file into a SQL table (OLE DB destination)
I have a couple of CSV files to be loaded
Instead of creating a separate task for each file , can I combine them together into a single task
I was thinking about using a ForEach container
This approach works if the number of columns in all the CSV files is same
But in my case it is not
So what I want is a script task that dynamically modifies the mappings
Can I do this?
I was browsing the net and I found certain code which uses IDTSExternalMetadataColumn90, MapOutputColumn etc.
But the code was creating a new package for each mapping
I couldn't understand the code
So can you please help me with this?
My script task should modify the mappings in my data flow task
For e.g.
If I have 3 columns in my CSV and 3 columns in DB, they should be mapped in the same order
P.S. I do not want to use configurations
View 5 Replies
View Related
Mar 1, 2006
Hi,
Does anyone know if it is possible to point data that underwent the "merge join" transformation (in one data flow) to the following data flow? I don't want to recreate all that merging, sorting and calling the same sources again in the following data flow if the data that I am using exists in the previous data flow. The merged data is simply too big to export to an excel file, so does anyone have any ideas? Thanks!
View 8 Replies
View Related
May 24, 2008
Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose,
I have four data tables with the same structure but records may be
different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I
want to that two records by filtering all tables and sum of rates and
want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance.
View 2 Replies
View Related
Dec 1, 2014
I am using SSIS package for pulling the data(last 2 months data).
Since the data size is huge, i have to split the data into hourly basis and pull the data.
how i can make this dynamic? Right now i am changing the hours manually after package execution.
View 9 Replies
View Related
Dec 5, 2014
Is it possible to dynamically grow a data file for TempDB?
I have some TSQL that I want to execute (this is raw tsql, not something I'd typically share) when I get an alert that TempDB is growing.
SET NOCOUNT ON;
DECLARE @TempDB TABLE (FileID tinyint, name varchar(50), size bigint)
DECLARE @TotalSize decimal(19,4), @TotalFiles int, @CurrentTempDBSize bigint, @SQL varchar(1000), @DBName varchar(50)
SET @SQL = ''
INSERT INTO @TempDB
[code]....
ENDThe code runs fine, but doesn't actually increase the size of the file.
View 7 Replies
View Related
Apr 20, 2015
I want to set the default value of the parameter based on the query result, when the current_status parameter is set as "opened" I want a certain date value to be displayed in the next parameter (start date), if it is set as closed I want a different date value to be displayed in the next parameter (start date), so I created the dataset with the query given below and assigned the default value to the query fields start and end date, however my Report parameters are not changing as expected.
dataset query:
IF @current_status = 'Opened'
SELECT DISTINCT cast(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS date) AS Start_Date1, cast(MAX(estcompletion_date) AS DATE) AS End_date
FROM <Table Name>
ELSE IF @current_status = 'Closed'
SELECT distinct cast(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS date) AS Start_Date1, cast(getdate() AS DATE) AS End_date FROM <Table Name>
View 4 Replies
View Related
May 23, 2008
Hi All..
I have an issue where I'm trying to export data from Sql Server tables (or from a result set in a SP or view) into Excel Spreadsheets. Normally I would use a simple data flow to do this. However, I need to do this on-the-fly because the schema of the Sql data is not static. The table could be a different one or the result set would have column schema that is not always the same.
The constant in all of this is that the spreadsheet columns and the table (or result set) column schema is identical. It's just that the column count and column names are not defined at design time, but would need to be defined at runtime.
Going from Excel to Sql Server is simple as I used a Script Task and the SQLBulkCopy class to dynamically transfer the data. However, BOL says that it's only one way (Data to Sql Server). Basically I need the to go the opposite direction now.
I have all of the information (SQL Table server, database, schema, and name and the Excel file path and name) already set up in variables and running through a ForEach container and I can dynamically change the variable information. I just need to figure out how to dynamically map the columns, create the spreadsheet file, and load the data into the spreadsheet. I'm sure this has been tossed around before. If someone could point me in the right direction I would most grateful.
Thanks.
Lee.
View 5 Replies
View Related
Apr 29, 2008
Hello:
I have an OLEDB source that uses a stored procedure which pivots records and returns me data with columns which are dynamic (Changing every time). How can I export this data with dynamic number of columns to excel destination?
Thanks
Jatin
View 3 Replies
View Related
Mar 18, 2008
hello guys,
I am having trouble with using disable property in the expression for data flow task. Here is the issue as explained below-
lets say i have 3 tables TableA, TableB, TableC from which i need to export data. So i create a table (TableList) where I save these table names and a unique id to these tables. e.g.
TableList will have-
TableName TableID
TableA 1
TableB 2
TableC 3
in the ssis package select these tableNames & Ids from tableList in Execute SQL Task. And assign the result set to a variable object (@TableList.
Then i use For Each Loop Container (For Each ADO Enumerator) , to loop through these tablesnames & iDs
Inside this loop container, i define three data flow tasks one for each table. So i have DataFlowTaskA (For TableA), DataFlowTaskB(For TableB), DataFlowTaskC (For TableC).
Now for a given table selected in the iteration, only the corresponding DataFlow Task should be exeuted. e.g. For the 1st iteration, if TableA is selected then only DataFlowTaskA should be executed and DataFlowTaskB& C should be skipped.
In order to achieve this, I am using a 3 variable @FlagA, @FlagB, @FlagC (type Boolean) one for each Table. and use the value of these flags for the "Disable" property of the data flow task (so @FlagA will be used for Disable property in the Expression for Data FlowTaskA, and so on..)
SotThe First Step inside the Loop, I use Script Task. (Input for the script task: read variable is @TableID and Read/Write varaibles are these 3 flags)
In this script task, I initialize these flags to true or false appropriately. So this is what i do
If (Dts.Variables("TableID").Value.ToString = "1") Then
Dts.Variables("@FlagA").Value = False
Else
Dts.Variables("@FlagA").Value = True
End If
If (Dts.Variables("TableID").Value.ToString = "2") Then
Dts.Variables("@FlagB").Value = False
Else
Dts.Variables("@FlagB").Value = True
End If
So in the 1st iteration, (if TableA comes) @FlagA=False and B&C will be True.
So the Disable property for DataFlowTask will be set false and for others it will be set to True. Thus, only DataFlowTaskA will be executed.
And this action should be repeated for each input table. this is the logic.
However only for the 1st iteration(say TableA is selected) it behaves as above. i.e. DataFlowTaskA is executed and DataFlowTaskB & C are skipped. But in the 2nd iteration(say TableB is selected) , it again executes DataFlowTaskA and doesnt exeute B & C (where it should have executed B & skipped A&C).
I do set daelay validation to true for all these but it still it doesnt working as expected. Even I checked the values for all the flags for each iteration and they seem to get the correct values. But somehow Diable propery in the expression not behaving as it should.
Am i missing anything. Do i need to set any other property to make this work.
I apprecite any help.
Thanks
View 3 Replies
View Related
Mar 14, 2014
I have data in the below format .
NameValuecategory
AAA510
BBB510
CCC510
DDD512
EEE512
FFF512
I want the result in the below format
NAMEValuecategory
AAA,BBB,CCC510
DDD,EEE,FFF5120
I have tried stuff but all six values(AAA...FFF) are coming in one row , however i need them as per the category.
View 2 Replies
View Related
Apr 16, 2014
I have an source file and i have to load it into the data base by changing datatype of the columns in ssis
View 1 Replies
View Related
