SQL Tools :: Adding Column To A Table Causes Copying Data Into Temp Table
Sep 23, 2015
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1] and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
[MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
View 7 Replies
ADVERTISEMENT
Nov 23, 2007
Hello guys..
Can u plz help me by giving me an idea how i can copy the temp table data to permanent table
Thanks,
sohails
View 1 Replies
View Related
Feb 7, 2007
I have the following tablestblGroupsGroupID intGroupName nvarchar(50)tblGroupMembersGroupID int (FK)UserID intI need a stored proc which:returns the groupID and name of all the groups of which userid 5 is a member AND also return the number of members that a group has (so the numbers of records in tblGroupMembers with a specific groupID)I have 2 sp's:myspGetGroupMembersCount which takes as input a groupID and has as output an integer valuemyspGetGroupsforUser which must return the entire resultsetSo say that userID 5 is a member of GroupID 6,18 and 22the following must be the resultset:UserID GroupID GroupName Members5 6 bla 132 5 18 yes 17 5 22 whatever 200 I think I need to call myspGetGroupMembersCount from within myspGetGroupsforUser and add it to the resultset (I dont want to work with a temptable)...but I dont know how...
View 2 Replies
View Related
Jul 20, 2005
For some reason in Enterprise Manager for SQL Server 2000, I cannotput the following line into a trigger:select * into #deleted from deletedWhen I hit the Apply button I get the following error:Cannot use text, ntext, or image columns in the 'inserted' or'deleted' tablesThis seems like a weird error, since I am not actually doing anythingto the inserted or deleted tables, I am just trying to make a tempcopy.I have another workaround but I am just curious why this happens.Thanks,Rebecca
View 3 Replies
View Related
Nov 8, 2006
I have a real table with an identity column and a trigger to populate this column.
I need to import / massage data for data loads from a different format, so I have a temp table defined that contains only the columns that are represented in the data file so I can bulk insert.
I then alter this table to add all the other columns so that it reflects all the columns in the real table. I then populate all the values so that this contains the data I need.
I then want to insert into the real table pushing the data from the temp table, however this gives me errors stating that the query returned multiple rows.
I specified all the columns in the insert grouping as well as on the select from the temp table.
ANY thoughts / comments are appreciated. This is beginning to drive me nuts.
Rob
View 5 Replies
View Related
Nov 2, 2015
I have a SQL VIEW with col1, col2, col3. I need to add a new column to the view col4 coming from a TABLE in SQL Server.
View 4 Replies
View Related
Jun 6, 2005
Hello,
I am receiving the following error:
Column name or number of supplied values does not match table definition
I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:
SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp
INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'
The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
Thanks,
Greg.
View 2 Replies
View Related
May 12, 2015
I am trying to insert a single row in Temp table #InventoryItems . The temp table is mentioned in a curosor childcur_inventory. While looping through the cursor I have mentioned nested if else condition . In the Else condition where I have mentioned PRINT "Hello World" I want to insert a single row in the temp #InventoryItems. I trying to use Select Top 1 but the cursor is looping indefinitely trying to insert multiple record
I want to insert only one record with null values.
DECLARE childcur_inventory CURSOR FOR SELECT Structure_Number, State, Neighbor_State, Border_Bridge_Structure_Number FROM #InventoryItems
OPEN childcur_inventory
FETCH childcur_inventory INTO @Structure_Number, @State, @Neighbour_State, @Border_Bridge_Structure_Number
[Code] .....
View 5 Replies
View Related
Oct 22, 2007
I am transferring data from oracle and getting below error message.
I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion
[OLE DB Source 1 [853]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".
View 4 Replies
View Related
Jul 20, 2005
I need to update a table by copying a column from another table(having the samestructure, but on another database), from the record having the sameprimary key.1 - What is the correct query?2 - I tried copying them record by record, but the datatype is ntext,(it displays <longtext> in the result pane), and trying to update it results in thefollowing errormessage:The text, ntext, and image data types are invalid in this subquery oraggregateexpression.I tried variations of the following:UPDATE TABLESET column0 = (SELECTcolumn0FROManotherDB.dbo.TABLEWHEREanotherDB.dbo.TABLE.column1 = column1)WHEREanotherDB.dbo.TABLE.column1 = column1
View 1 Replies
View Related
Feb 15, 2005
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.
any ideas on how i could do this easily?
thanks
View 1 Replies
View Related
Feb 9, 2006
Hello,
Probabaly a silly question yet as a DOTNET developer, I'm trying to simulate DTS when for example, I don't have permission to perform DTS on a production server.
In particular and interested regards caching of rows before the service decides to flush the buffer and write to the target table. Safe to assume DTS is cursor based?
View 1 Replies
View Related
Jul 20, 2005
Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO
View 2 Replies
View Related
Oct 25, 2015
I have a temp table like this
CREATE TABLE #Temp
(
ID int,
Source varchar(50),
Date datetime,
CID varchar(50),
Segments int,
Air_Date datetime,
[code]....
Getting Error
Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.
View 4 Replies
View Related
Aug 14, 2015
Below is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
View 5 Replies
View Related
Feb 22, 2006
I need to copy the following columns from my Employee table in my Performance DB to my Employee table in my VacationRequest DB: CompanyID, FacilityID, EmployeeID, FirstName, LastName, [Password] = 'nippert', Role = 'Employee' I tried the advice on this website but to no avail:http://www.w3schools.com/sql/sql_select_into.asp
View 1 Replies
View Related
Feb 20, 2008
select * into dbo.ashutosh from attribute where 1=2
"USE WHERE 1=2 TO AVOID COPYING OF DATA"
//HERE "ASHUTOSH" IS THE NEW TABLE NAME AND "ATTRIBUTE" IS THE TABLE WHOSE REFERENCE IS USED//
//the logic is to use where clause with 1=2 which will never be true and hence it will not return any row//
View 3 Replies
View Related
Jan 2, 2008
I have "inherited" a project working on a SQL 2000 database. The project calculates commissions based on data from an invoice header table and an invoice details table. The goal is to extract data from the primary database tables, perform limited manipulations, and store the resultant data into a table in a different database for reference and reporting. I have the query complete that extracts and manipulates the data, but I am stuck in trying to add this data to the final storage/reporting table. I would also like to do error checking to be certain that a record is not "re-inserted" from the source data to the destination table. Thanks in advance, Barry
View 3 Replies
View Related
Oct 5, 2015
I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.
Actual Data
Expected Result
1000
1-Aug
1000
1-Feb
1000
1-Jul
1000
[code]....
View 3 Replies
View Related
Dec 19, 2007
WE have a job that loads data from an Oralce DB into our SQL Server 2000 DB twice a day. The schedule has just changed so that now there is a possibility of having my west coast users impacted when it runs at 5 PM PST and my east coast users impacted when it runs at 7 AM EST. As a workaround, I have developed a DTS package that loads the data into temp tables instead of the real tables. IE. Oracle -> XTable_temp instead of Oracle -> XTable. The load sometimes takes about an hour to an hour and a half to load, so this solution works great, but I want to then lock the table, delete it and rename the temp table to table X. The pseudo code would be:
Begin Transaction
Lock Table XTable
Drop XTable
Alter Table XTable_temp rename to XTable
Release Lock XTable
End Transaction
Create XTable_temp
I see two issues with this solution. 1) I think if I can lock XTable that the lock would be released when the table is dropped and the XTable_temp was being renamed. 2) I can't find a command to rename a table.
Any ideas on a process that might help?
TIA,
A
View 5 Replies
View Related
May 27, 2004
I need to copy a column which contains a list of integers to a new column. Can someone please tell me how I can do this using a query statement? Thanks in advance.
View 13 Replies
View Related
Feb 8, 2007
There are several easy ways to accomplish what I am trying to do in a simpler way however (long story short) this is something I need to learn for a much larger task in the near future.
O.K. I have a table named clients I'm trying to get started copying columns from the same table one by one. Lets say original table has these columns:
Database kokomain -- table dbo.clients
ID(pk) fname lname idate
I've created a blank temp table with the exact same schema and named it 'temp'. I've populated the id,fname and lname fields with the statement:
Insert Into kokomain.dbo.temp (cid,fname,lname) select c.cid,c.fname,c.lname from kokomain.dbo.clients c
Now, how could I add the idate field to the table as a separate query? I've tried several variations of things like...
INSERT INTO kokomain.dbo.temp (idate) select c.idate from kokomain.dbo.clients c where c.idate in (select idate from kokomain.dbo.temp)
No dice.
The correct statement would be greatly appreciated
Thanks.
View 4 Replies
View Related
Feb 21, 2007
Hi everyone, I'm fairly new to sql and right now I am struggling with a script. I am trying to extract data from a normal table into a temporary table, update it in the temporary table, then put it back into the normal table. I'll display my code, let me know what you think, any suggestions are appreciated. Thanks a lot.
Create table scripts (
UserID int,
UserName char(50),
ScrRan char(50),
StartTime datetime default getdate(),
EndTime datetime);
Create table errors (
ID int,
UserName char(50),
UserLogin char(50),
ErrorNumber int,
Message char(100),
TimeOfError datetime default getdate());
declare @error int
declare @msg varchar(100)
declare @startTime datetime
declare @endTime datetime
select @startTime = getDate()
SELECT *
INTO #Temp
FROM Publisher
WHERE pub_Name = 'Scene Publishing'
UPDATE #Temp
SET pub_Name = UPPER(pub_Name)
SELECT *
INTO Publisher
FROM #Temp
--Begins Error Checking Routine
select @error = @@error
IF @error <> 0
BEGIN
select @msg ='error: ' + convert(varchar(7), @error) +
''
insert into errors values (@@SPID, USER, USER_NAME(), @error,
@msg, getDate())
END
ELSE
BEGIN
select @endTime = getDate()
insert into scripts values (@@SPID, SYSTEM_USER, @startTime, @endTime)
END
select * from errors
select * from scripts
lost and loaded.
View 2 Replies
View Related
Oct 19, 2007
I am trying to update a table in one database with data from a temporary table which i created in the tempdb.
I want to update field1 in the table with the tempfield1 from the #temp_table
The code looks something like this:
Use master
UPDATE [dbname].dbo.table
SET [dbname].dbo.table.field1 = [tempdb].dbo.#temp_table.tempfield1
WHERE ( [dbname].dbo.table.field2= [tempdb].dbo.#temp_table.tempfield2
AND [dbname].dbo.table.field3= [tempdb].dbo.#temp_table.tempfield3
AND [dbname].dbo.table.field4= [tempdb].dbo.#temp_table.tempfield4)
I get the following error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield2" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield3" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield4" could not be bound.
What is wrong?
View 1 Replies
View Related
Jul 13, 1999
I'm having difficulty bulk-copying a data file, using a format file, into a SQLServer 6.5 table with an identity column.
The data file is tab-delimited, and does not include the identity column (I want it to be automatically generated).
When I execute the following:
bcp DB1.dbo.WORK_TABLE in WorkTable.txt -Usa -P -fWorkTable.fmt -SSERVERX'
I receive this message:
Starting copy...
BCP copy in failed
I've had no problems loading a similar table without the identity column.
Can anybody help? Here is the table definition & bcp format:
CREATE TABLE WORK_TABLE
(
PersonID int NOT NULL IDENTITY (1,1)
,FirstName char(25) NULL
,MiddleName char(25) NULL
,LastName char(25) NULL
,Company char(50) NULL
,City char(25) NULL
,State char(2) NULL
,Phone char(25) NULL
)
-----
6.0
8
1 SQLCHAR 0 0 "" 1 PersonID
2 SQLCHAR 0 25 "" 2 FirstName
3 SQLCHAR 0 25 "" 3 MiddleName
4 SQLCHAR 0 25 "" 4 LastName
5 SQLCHAR 0 50 "" 5 Company
6 SQLCHAR 0 25 "" 6 City
7 SQLCHAR 0 2 "" 7 State
8 SQLCHAR 0 25 "
" 8 Phone
-----
View 2 Replies
View Related
Nov 18, 2004
I need to copy data from one table into another table. The field names are NOT the same:
I need to INSERT
Table 1 fields:
cdescurl
cname
cdescription
cimageurl
category
cprice
INTO
Table 2:
product_url
name
description
image_url
category
price
How do I do this? Thank you in advance for any help you can give me!
View 2 Replies
View Related
Apr 9, 2014
Below are my temp tables
--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL
[Code] ....
I want to loop through the data from #Base_Resource and do the follwing logic.
1. get the Resourcekey from #Base_Resource and insert into #Resource table
2. Get the SCOPE_IDENTITY(),value and insert into to
#Resource_Trans table's column(StringId,value)
I am able to do this using while loop. Is there any way to avoid the while loop to make this work?
View 2 Replies
View Related
Jun 18, 2007
I created a temp table in my stored procedure and then added a new identity column to it.
However, I am not able to use this new column immediately, it says column not found.
SELECT * INTO #temp FROM table_name
ALTER TABLE #temp ADD __Identity int IDENTITY(1,1)
SELECT * FROM #temp WHERE __Identity >= 10
Here __Identity column is not found. If I just did SELECT * FROM #temp without the where clause, the final result does have the __Identity column correctly added to the table. Why can't I query it?
Thanks!
View 4 Replies
View Related
Jun 19, 2005
Hi,
I am trying to create a temp table with an identity column. Here is the code that I am using...
SELECT UserId, IDENTITY(int, 1, 1) AS colId
INTO #User
FROM MyUserTable
WHERE UserId = 1
I am getting an error though.
Server: Msg 8108, Level 16, State 1, Line 9
Cannot add identity column, using the SELECT INTO statement, to table
'#User', which already has column 'UserId' that inherits the identity
property.
Is there any way to work around this?
Thanks for your help.
View 1 Replies
View Related
Jul 10, 2007
Dear All,I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.trying to use:exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'I get:Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.There is no mistype, the table name and column name are correct.Can temp table's column names not be altered?If yes, how?Thanks in advance!
View 7 Replies
View Related
Aug 24, 2006
can anyone help me figure out why when i run the following storedprocedure i get the error:(1460 row(s) affected)Msg 245, Level 16, State 1, Procedure SP_SALESTRENDS, Line 40Conversion failed when converting the varchar value 'X' to data typeint.SP:--STORED PROCEDURE FOR INVOICE TRENDS:--To use Stored Procedure use the following code:--EXEC SP_INSPECTIONSUMRY (MONTH), (OFFICE)--(OFFICE) CAN BE: BGR FOR BANGOR, SP FOR SOUTH PORTLAND, NH FOR NEWHAMPSHIRE, UNH FOR UNH--(REPORT) CAN BE: PRODUCT CODE FOR REPORT BROKEN OUT BY PRODUCT CODE-- EXEC SP_SALESTRENDS BGR, INVOICED, 2006, XALTER PROCEDURE SP_SALESTRENDS@OFFICE VARCHAR(30),@REPORT VARCHAR(30),@VARYEAR INT,@CODE VARCHAR(30)ASIF @REPORT='INVOICED'SELECT YEAR(I.INVOICEDAT) AS VARYEAR, MONTH(I.INVOICEDAT) AS VARMONTH,SUM(I.STOTAL) AMOUNT, P.PERSON, P.PRODUCT, C.DESCRIPTNINTO #TEMP_SALESTRENDSFROM OPENQUERY(PROJECTS, 'SELECT PROJECT, INVOICEDAT, STOTALFROM INVSUMYR') ILEFT JOIN(SELECT *FROM OPENQUERY(PROJECTS, 'SELECT NUMBER, PRODUCT, PERSONFROM PROJMAST')) PON (LTRIM(I.PROJECT)=LTRIM(P.NUMBER))LEFT JOIN(SELECT PC, DESCRIPTNFROM OPENQUERY(PROJECTS, 'SELECT PC, DESCRIPTNFROM PRODCODE')) CON (C.PC=P.PRODUCT)GROUP BY YEAR(I.INVOICEDAT), MONTH(I.INVOICEDAT), P.PERSON, P.PRODUCT,C.DESCRIPTNORDER BY VARYEAR, VARMONTH-- INVOICED REPORT BROKEN OUT BY OFFICEIF @REPORT='INVOICED' AND @CODE=1 AND @VARYEAR=1234 AND@OFFICE='NORRIS'SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @CODE!=1 AND @VARYEAR=1234SELECT VARYEAR, VARMONTH, SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT=@CODEGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED'AND @CODE!=1 AND @VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT=@CODE AND VARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='NORRIS' AND @CODE=1 AND@VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE VARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L')GROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L') AND VARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4')GROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4') ANDVARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P')GROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P') AND VARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('U', 'Z', 'R', 'V')GROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('U', 'Z', 'R', 'V') AND VARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTH--END OF SALES TRENDS STORED PROCEDUREthanks.
View 4 Replies
View Related
Oct 31, 2004
hi all,
i have a dts that copies table contents from remore server to local server.
remote table:
ItemID|name
1 |name1
2 |name22
3 |name33
local table:
ItemID|name
1 |name1
2 |name2
i want that the dts will only copy the new rows( aka. row with ItemID = 3) and leave the other rows a they are.
can any one help me create such a dts.
please help!
View 1 Replies
View Related
May 1, 2007
Hi All
Iam using sql 2005 and am new to it, I want to copy a table and data from server to another, how is this possible?
Many Thanks in advance....
View 2 Replies
View Related
