Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789
Hi guys,
anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.
so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".
so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?
regards
terence chua
I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.
Here is an example:
-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END
-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc
--- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))
-- this call will succeed
INSERT INTO testTable EXEC testProc
Hi,
I have a series of queries which have doubled in the amount of time they take to execute since moving to SQL Server 2005. The queries being performance tested are utilising hardware that is very similar to that of the comparison SQL Server 2000 server. They have 6 CPUs exactly the same and we have swapped RAM around to eliminate that difference.
There are 4 parts to the query suffering performance degredation.
1. Create temporary results table
2. Create (using SELECT INTO) and populate temporary working table 1 - 212,263 rows
3. Create (using SELECT INTO) and populate temporary working table 2 - 5,102 rows
4. Insert into temp results table matches found in temp work table 1 and temp work table 2 - 382 rows
On 2000, the queries take a total of 15 secs. On 2005, they take 30 seconds. Part four of the query takes approx 17 secs on its initial run. However, if i truncate the temp results table and re-run just the last query it only takes 1 sec. Query Plan caching?
I have reviewed the forum for a solution to the problem but with no luck. Many of the solutions presented appear to relate to permanant user tables. Solutions such as UPDATE STATISTICS and recompiling stored procedures have no positive effect. When reviewing the query plans, there are very little differences. Some expected between versions right?
The following code snippet is the query from part 4.
Code Snippet
INSERT #MatchingResults
(Table1IDNo, Table2IDNo, MatchRunNo)
SELECT DISTINCT #Table2.IDNo AS Table2IDNo,
#Table1.IDNo AS Table1IDNo,
1 AS MatchRunNo
FROM #Table1
INNER JOIN #Table2
ON ( #Table2.LastName = #Table1.LastName )
AND ( #Table2.AddressDetails = #Table1.AddressDetails )
AND ( #Table2.Country = #Table1.Country )
AND ( ( #Table2.FirstName = #Table1.FirstName) OR ( #Table1.FirstName = '' ) )
AND ( ( #Table2.Title = #Table1.Title ) OR ( #Table1.Title = '' ) )
The query plan shows a hash join on both servers. I have tried removing the distinct statement and forcing a Loop Join (query hint).
I have also run SQL Profiler. The only differences there appear to be with the "SELECT StatMan" statements.
On 2000, part of the query duration is 1719 and is as follows:
Code SnippetSELECT statman([AddressDetails],[LastName],[FirstName],[Title],[Country],@PSTATMAN)
FROM (SELECT TOP 100 PERCENT [AddressDetails],[LastName],[FirstName],[Title],[Country] FROM [dbo].[#TMCT04042007101009_________________________________________________________________________________________________000000000096] WITH(READUNCOMMITTED,SAMPLE 3.675520e+001 PERCENT) ORDER BY [AddressDetails],[LastName],[FirstName],[Title],[Country]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)
On 2005, part of the query duration is 5188 and is as follows:
Code Snippet
SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [AddressDetails] AS [SC0] FROM [dbo].[#TMCT04042007101009_________________________________________________________________________________________________00000000000E] WITH (READUNCOMMITTED,SAMPLE 7.946877e+001 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
Its clear that the sampling rate is higher. I assume this could have something to do with it. Can this be modified?
Thank-you for your help in advance..
Cheers
Tim
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View Relatedhello group,i want to denormalize data in a temporary table for a secondbusiness-system which cant read related dataswhat i have:table Partner: (Id_Partner, NamePartner, ...)table PartnerProduct: (Id_PartnerProduct, NamePartnerProduct, ...)table Partner2PartnerProduct: (Id, Id_Partner, Id_PartnerProduct)Partner to PartnerProduct ist n:m relation (thats why i needPartner2PartnerProduct)and the temporary tableTempPartner: (Id_Partner, NamePartnerProducts)whereas NamePartnerProducts should contains a comma separated list ofthe PartnerProduct-Nameswhat i need:a trigger or combination of trigger and SP whichoperate like the following:if on Partner2PartnerProduct is a Insert or Update made, then Deletethe row in TempPartner which has the Id of the Partner, and thencreate a new row which contains all n:m joined PartnerProduct-Names init.i hope the intention and problem are clear.thanx for answers,hans
View 1 Replies View RelatedHow to use temporary table in SSIS ?
View 2 Replies View RelatedI'd like to select data out of an oracle table, and UPDATE a sybase table. So far I've got as far as the following:
1. create a package
2. add 2 connection managers, set retainsameconnection=true on both just to be sure.
3. add an executesql command which creates a temporary table
create table #temp_wm ( instru_id int)
4. add a dataflow task with:
i) an oledb source with a select statement: "select instru_id from mytable"
ii) an oledb destination with open rowset: #temp_wm
added an external column called instru_id in the inputs and outputs tab
when i run, the create temp table task work, the select works, but the insert into fails. If I change the select statement to:
"select instru_id from mytable where 1=0" it all executes fine.
So everythings copacetic as long as i don't need to actually insert any real records = brilliant!
1. HAS ANYONE OUTTHERE SUCCESSULLY USED SSIS TO INSERT DATA INTO A SYBASE TEMPORARY TALBE - MAYBE ITS JUST NOT POSSIBLE??
2. Any idea how I can fix my setup?
I'm usign Sybase ASE OLE DB Drivers
Note: i also tried ## temp tables, no difference.
This is the error output.
[OLE DB Destination [255]] Error: An OLE DB error has occurred. Error code: 0x80004005.
[OLE DB Destination [255]] Error: The "input "OLE DB Destination Input" (268)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (268)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (255) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp
both works fine , then what is the difference
waiting for ur reply
Banti"
Hi,
I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.
This works only for the first ExitCoilID then I need to RESEED.
Here is my code:
DECLARE
@EntryCoilCnt AS INT,
@ExitCoilID AS INT,
@SubtractedFromEntyCoilCnt AS INT
DECLARE
@ExitCoilID_NotProcessed TABLE
(ExitCoilID int)
INSERT INTO @ExitCoilID_NotProcessed
SELECT DISTINCT ExitCoilID
FROM
dbo.TrendEXIT
where
ExitCoilID is not null and
ExitCnt is null
order by
ExitCoilID
DECLARE
@ExitCoilID_Cnt_Index TABLE
(ExitCoilID int, ExitCnt int IDENTITY (1,1))
IF @@ROWCOUNT > 0
BEGIN
DECLARE ExitCoilID_cursor CURSOR FOR
SELECT ExitCoilID FROM @ExitCoilID_NotProcessed
ORDER BY ExitCoilID
OPEN ExitCoilID_cursor
FETCH NEXT FROM ExitCoilID_cursor
INTO @ExitCoilID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @ExitCoilID_Cnt_Index
SELECT ExitCoilID
FROM dbo.TrendEXIT
WHERE
ExitCoilID = @ExitCoilID
ORDER BY
EntryCoilID, Cnt
select * from @ExitCoilID_Cnt_Index
--truncate @ExitCoilID_Cnt_Index
--DBCC CHECKIDENT ('@ExitCoilID_Cnt_Index', RESEED, 1)
FETCH NEXT FROM ExitCoilID_cursor
INTO @ExitCoilID
END
CLOSE ExitCoilID_cursor
DEALLOCATE ExitCoilID_cursor
select * from @ExitCoilID_Cnt_Index
END --IF @@ROWCOUNT <> 0
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies View RelatedI want Compare two Table data and insert changed field to the third table ...
View 9 Replies View RelatedHi all,
I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise.
Thanks in advance,
Scott Chang
///////////--SQLQueryCroomLabData.sql--///////////////////////////
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
VALUES (2, 'MW2', 'Dichloroethene', 1.00)
VALUES (3, 'MW2', 'Trichloroethene', 20.00)
VALUES (4, 'MW2', 'Chloroform', 1.00)
VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
VALUES (6, 'MW6S', 'Acetone', 1.00)
VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
VALUES (9, 'MW6S', 'Chloroform', 1.00)
VALUES (10, 'MW6S', 'Methylene Chloride', 1.00
VALUES (11, 'MW7', 'Acetone', 1.00)
VALUES (12, 'MW7', 'Dichloroethene', 1.00)
VALUES (13, 'MW7', 'Trichloroethene', 1.00)
VALUES (14, 'MW7', 'Chloroform', 1.00)
VALUES (15, 'MW7', 'Methylene Chloride', 1.00
VALUES (16, 'TripBlank', 'Acetone', 1.00)
VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
VALUES (19, 'TripBlank', 'Chloroform', 0.76)
VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
//////////Parse///////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
////////////////Execute////////////////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Do I need to use a cursor?
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 RelatedHow to perform a table JOIN with the INSERT SQL statment, just show a join example between 2 tables will do.
View 2 Replies View RelatedHow to perform a table JOIN with the INSERT SQL statment, just show a join example between 2 tables will do.
View 3 Replies View RelatedHi,
I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.
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]....
I would like to compare values in the same table and get the single record with different values in the multiple columns.For table tab1, ID is my key column. If type1 is active (A) then i need to update X else blank on Code1 column and if type2 is active (A) then i need to update X else blank on code2 column. Both type1 and type2 comes from same table for same ID..Below is the example to understand my scenario clearly....
declare @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')
[code]....
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...
Or using a table-valued function to return a temp table as the result.
I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.
Hello,
I have a table called #table1 which is populated as in the example below.
I would like to write a selectinsert statement based on #table1 that populates #table2 like in the #table2 example.
Note #table2 is a fixed table that follows the structure below.
Can any of you T-SQL gurus help me with my problem?
Any help will be most appreciated.
Thanks
---------------------------------------------
/*
Please paste T-SQL into query window
*/
---------------------------------------------
CREATE TABLE #table1
--max of 5 orders
(
custID nvarchar(6),
dateorder [datetime] NULL,
order1 nvarchar(2),
order2 nvarchar(2),
order3 nvarchar(2),
order4 nvarchar(2),
order5 nvarchar(2)
)
GO
SET ANSI_PADDING OFF
Insert into #table1
select '012345','2008-04-19 00:00:00.000' , '01', '06', '05', null, null UNION all
select '012345','2008-04-20 00:00:00.000' , '01', '07', '05', '07', '03' UNION all
select '012345','2008-04-21 00:00:00.000' , '01', '06', null, null, null UNION all
select '012345','2008-04-22 00:00:00.000' , '01', '02', '05', '07', null UNION all
select '012345','2008-04-23 00:00:00.000' , '03', '06', null, null, null UNION all
select '987654','2008-04-21 00:00:00.000' , '19', '21', null, null, null UNION all
select '987654','2008-04-22 00:00:00.000' , '01', '02', '05', '16', null UNION all
select '987654','2008-04-23 00:00:00.000' , '03', '06', null, null, null
select * from #table1
--This is the table i would like to insert my data into
CREATE TABLE #table2
--max of 5 orders in 1 day
--it does not matter what date the date order was made the 1st date would appear in dateorder1 and so on...
(
custID nvarchar(6),
dateorder1 [datetime] NULL,
order1_1 nvarchar(2),
order1_2 nvarchar(2),
order1_3 nvarchar(2),
order1_4 nvarchar(2),
order1_5 nvarchar(2),
dateorder2 [datetime] NULL,
order2_1 nvarchar(2),
order2_2 nvarchar(2),
order2_3 nvarchar(2),
order2_4 nvarchar(2),
order2_5 nvarchar(2),
dateorder3 [datetime] NULL,
order3_1 nvarchar(2),
order3_2 nvarchar(2),
order3_3 nvarchar(2),
order3_4 nvarchar(2),
order3_5 nvarchar(2),
dateorder4 [datetime] NULL,
order4_1 nvarchar(2),
order4_2 nvarchar(2),
order4_3 nvarchar(2),
order4_4 nvarchar(2),
order4_5 nvarchar(2),
dateorder5 [datetime] NULL,
order5_1 nvarchar(2),
order5_2 nvarchar(2),
order5_3 nvarchar(2),
order5_4 nvarchar(2),
order5_5 nvarchar(2)
)
Insert into #table2
select '012345','2008-04-19 00:00:00.000' , '01', '06', '05', null, null, '2008-04-20 00:00:00.000' , '01', '07', '05', '07', '03','2008-04-21 00:00:00.000' , '01', '06', null, null, null,'2008-04-22 00:00:00.000' , '01', '02', '05', '07', null,'2008-04-23 00:00:00.000' , '03', '06', null, null, null UNION all
select '987654','2008-04-21 00:00:00.000' , '19', '21', null, null, null ,'2008-04-22 00:00:00.000' , '01', '02', '05', '16', null,'2008-04-23 00:00:00.000' , '03', '06', null, null, null , null, null, null, null, null, null, null, null, null, null, null, null
select * from #table2
drop table #table1
drop table #table2
----------------------------------------
we have a table below
TABLE1
ID Roll Name Amount . . . . . . so on
---------------------------------------
1 2 Alex 500
2 5 Jones 600
3 2 Ales 400
and we have TABLE 2
ID Roll Name Amount . . . . . . . so on
In both the Tables ID Field is Identity Field and rest all the columns are identical in both the tables.I want to perform a query in such a way that SQL Should,
1) Remove all data from TABLE2 First
2) Select those records from TABLE1 where Roll = 2
3) Then, Insert the results to TABLE 2
So we should get the result similar to below:
ID Roll Name Amount . . . . . . . so on
---------------------------------------
1 2 Alex 500
3 2 Ales 400
how can I do this in a same sql statement.
I have two databases in SQL Server and they both have almost the same tables. I want to insert the data of a table from the first database into a table with the same name, but from the second database. How can i do this?
I have to tables like given below Landing table "A" (Data load will happen over here, No primary keys mentioned over here) table "B" .Now I want to move the data from A to B.I have made use of below query insert into B select * from A...Landing table "A" has huge no of records, MS SQL server is taking huge amount of time.any alternative way to make this insertion process faster?
View 6 Replies View RelatedI am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
hi all, if i have a function which it returns a table and i need to work with the table retured many times in the stored procedure, then should i use a temporary table or a table variable to store the returned table ? or it's there a better way in doing that?
View 1 Replies View RelatedUSE [Database]
GO
/****** Object: StoredProcedure [dbo].[InDELKK1] Script Date: 09/22/2013 18:31:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InDELKK1]
[code]...
I want to insert the results of a temporary table into a table in sqlserver .The results of the temporary table I get,but I can't manage it to put it into Delkk (= a table in my sql server db)
While @val <= @Cont
Select top 1 @MaxVanOrderdatum = MaxVanOrderdatum ,
@AankoopBdr = AankoopBdr ,
@Klantnummer = klantnummer ,
@Tal = Tal from @Temp
[code]...
what is the difference between Temporary table and Table variable ,need one example with both(#t,@t) to know the scope
View 10 Replies View RelatedI have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?