How To Append Multiple Queries To A Temp Table?
Apr 4, 2006
Hello. I'm having some difficulty trying to output the results of two seperate queries into the same temporary table.
Does anyone know if it is possible to use the UNION operator to output the results into a temporary table?
Select * From TableA
UNION
Select * From TableB
<---output result to temporary table here--->
Alternatively, is it possible to output the results of two queries in to the same temporary table without the UNION clause?
The following statement fails on the second SELECT INTO due to the fact that #MyTempTable already exists.
Select * INTO #MyTempTable FROM TableA
Select * INTO #MyTempTable FROM TableB
Thanks in advance.
View 2 Replies
ADVERTISEMENT
Nov 20, 2006
The dynamic sql is used for link server. Can someone help. Im getting an error
CREATE PROCEDURE GSCLink
( @LinkCompany nvarchar(50), @Page int, @RecsPerPage int )
AS
SET NOCOUNT ON
--Create temp table
CREATE TABLE #TempTable
( ID int IDENTITY, Company nvarchar(50), AcctID int, IsActive bit )
INSERT INTO #TempTable (Name, AccountID, Active)
--dynamic sql
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT a.Name, a.AccountID, a.Active
FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a
LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b
ON a.AccountID = b.oaAccountID
WHERE oaAccountID IS NULL
ORDER BY Name ASC'
EXEC sp_executesql @sql
--Find out the first and last record
DECLARE @FirstRec int
DECLARE @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
--Return the set of paged records, plus an indication of more records or not
SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @LastRec) AS MoreRecords
FROM #TempTable
WHERE ID > @FirstRec AND ID < @LastRec
Error:
Msg 156, Level 15, State 1, Procedure GSCLink, Line 22
Incorrect syntax near the keyword 'DECLARE'.
View 3 Replies
View Related
May 20, 2014
I am using SQL 2008 r2. I have two SQL Queries from 2 different database but they share one server. I need to linked these two SQL Queries as they share the same Primary key which CustomerID see example below
Query 1
Database::Student
Select StudentID , FName, LName
From Student
Query 2
Database ::Finance
Select StudentID,Tution
FROM Payment
I need to be able combine two query which come from two database but they share one server.I would like to use two temp tables so that I can perform a left / right join to retrieve the data by linking two queries using primary id which they both share ( StudentID)
Summary : I have two DB's on the same server. I have two simple select queries for each DB which work correctly.
View 3 Replies
View Related
Feb 22, 2007
I have to select several field values from a table and need to assign them to different variables in my SP.Here's what I do now:
declare @ReceiverEmail nvarchar(50)
SET @ReceiverEmail=(SELECT Email FROM Users WHERE UserCode=@UserCodeOwner)
declare @UsernameSender nvarchar(50)
SET @UsernameSender=(SELECT Username FROM Users WHERE UserCode=@UserCodeOwner)As you can see I have to search the Users table twice: once for the Email and a second time for the Username...and all that based on the SAME usercode...:SSo, is there an option where I only have to search the table once and return the Email and UserName fields and assign them to my variables (without using a temp table....)?
View 4 Replies
View Related
Feb 18, 2004
What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?
Any ideas?
Thanks in advance.
View 6 Replies
View Related
Mar 21, 2007
The first query returns me the results from multiple databases, thesecond does the same thing except it puts the result into a #temptable? Could someone please show me an example of this using the firstquery? The first query uses the @exec_context and I am having achallenge trying to figure out how to make the call from within adifferent context and still insert into a #temp table.DECLARE @exec_context varchar(30)declare @sql nvarchar(4000)DECLARE @DBNAME nvarchar(50)DECLARE companies_cursor CURSOR FORSELECT DBNAMEFROM DBINFOWHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')ORDER BY DBNAMEOPEN companies_cursorFETCH NEXT FROM companies_cursor INTO @DBNAMEWHILE @@FETCH_STATUS = 0BEGINset @exec_context = @DBNAME + '.dbo.sp_executesql 'set @sql = N'select top 10 * from products'exec @exec_context @sqlFETCH NEXT FROM companies_cursor INTO @DBNAMEENDCLOSE companies_cursorDEALLOCATE companies_cursor-------------------------------------------------------------------------------------CREATE TABLE #Test (field list here)declare @sql nvarchar(4000)DECLARE @DBNAME nvarchar(50)DECLARE companies_cursor CURSOR FORSELECT NAMEFROM sysdatabasesWHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULLORDER BY NAMEOPEN companies_cursorFETCH NEXT FROM companies_cursor INTO @DBNAMEWHILE @@FETCH_STATUS = 0BEGINset @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'INSERT INTO #Testexec (@sql)FETCH NEXT FROM companies_cursor INTO @DBNAMEENDCLOSE companies_cursorDEALLOCATE companies_cursorSELECT * from #TestDROP TABLE #Test
View 1 Replies
View Related
Jun 30, 2015
I have a temp table with the following columns and data
drop table #temp
create table #temp (id int,DLR_ID int,KPI_ID int,Brnd_ID int)
insert into #temp values (1,2343,34,2)
insert into #temp values (2,2343,34,2)
insert into #temp values (3,2343,34,2)
[Code]....
I use the rank function on that table and get the following results
select rank() over (order by DLR_ID,KPI_ID,BRND_ID ) Rown,* from #temp
I am interested only in Rown and Id columns. For each Rown number, I need to get the min(ID) in the second column and the duplicate ID should be in 3rd column as shown below.If i have 3 duplicate IDs , I should have 3 rows with 2nd column being the min(id) and 3rd column having one of the duplicate ids in ascending order(as shown in Rown=6)
View 7 Replies
View Related
Jan 4, 2015
I was messing around with stored procedures and I was wondering if creating a SP that populates a single table for reporting is a good idea?
I have ~10 queries that I have to currently run manually and was hoping to drop them into a physical table and then leverage that single table to pull into excel.
Some of my queries use virtual tables or CTE's, this is to get the aggregate set correctly.
Essentially I am working out of a data warehouse and would like to eventually get all my queries in one SP or something similar and then call that query for a insert.
Speaking of which could you create a SP that has several selects than with that output drops the records into a single table by using an insert into query so the data from the all the queries would line up into the right columns?
View 1 Replies
View Related
Oct 17, 2013
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CC'
group by clientnumber;
OUTPUT currently looks like this:
1234 2 0 0
1234 0 1 0
1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?:
1234 2 1 4 Y
View 3 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
Aug 9, 2007
I run two different types of backups on the same database.
A monthly full and nightly diffs appending to the same full file - file 'A'
A weekly full with 10 min trans log backups appending to same file - file 'B'- during working hours.
2 strategies, 2 backup files.
These are new strats that have gone live this week - tuesday in fact. The monthly and weekly both ran fine on tuesday as did the nightly diff and all the TS backups througout the day. last night - Weds - the nightly diff failed with the following error:
"
Executed as user: <USERNAME>. Cannot perform a differential backup for database "objectstore", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
"
Whilst the error is perfectly legible I dont understand what its implying - cant I add multiple Diff backups to the same full backup OR is my weekly / 10 mins on the same database (but to a diff backup file) ballsing things up for me somehow? If this is the case how come everything ran fine on tuesday night?
Any help gets beers.
THanks
Alastair Jones.
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
View 8 Replies
View Related
Jul 29, 2015
I need to grab data from teradata(using odbc connection).. i have no issues if its just bunch of joins and wheres conditions.. but now i have a challenge. simple scenario, i have to create volatile table, dump data into this and then grab data from this volatile table. (Don't want to modify the query in such a way i don't have to use this volatile table.. its a pretty big query and i have no choice but create bunch of volatile tables, above scenarios is just mentioned on simple 1 volatile table ).
So i created a proc and trying to pass this string into teradata, not sure if it works.. what options i have.. (I dont have a leisure to create proc in terdata and get it executed when ever i want and then grab data from the table. )
View 2 Replies
View Related
Jun 18, 2004
Strange one here - I am posting this in both SQL Server and Access forums
Access is telling me it can't append any of the records due to a key violation.
The query:
INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;
Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.
There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.
I can manually append the records using cut and paste with no problems.
I have tried re-linking the tables.
Any ideas?
Thanks,
Brad
View 4 Replies
View Related
Jul 22, 2012
All, Using access 2003 frontend and sql server 2008 backend. I have an append query to insert 80000 from one table to an empty table. I get an error:
"Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and didn't add 36000 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations."
I know this error normally comes if there are dups in a field that doesnt allow.
View 1 Replies
View Related
Jan 29, 2013
I've never used a stored procedure before - let alone created one. how to append records from table A to table B.
View 1 Replies
View Related
Oct 11, 2004
Hi I have a table with the following structure:
Table1
-------
Dept
Filed1
Filed2
Field3
Field4
Field5
I have another table with the following structure
(Basically this table will contain a subset of coloumns of Table1)
Table2
-------
Dept
Field1
Field2
Now using a query I would like see all the records with all coloumns in Table1 plus all the records in Table2 appended
i.e
if Table1 row is
IT F1 F2 F3 F4 F5
and if Table2 row is
IT F11 F22
Sales F12 F23
I would like to see a result set with the following structure
Resultset
IT F1 F2 F3 F4 F5
IT F11 F22 NULL NULL NULL
Sales F12 F23 NULL NULL NULL
Can some body explain me how to do this with a query. I tried using union but it requires identical coloumns on both ends( Ofcourse, we can acheive this by having Field3,Field4 and Field5 as blank columns in Table 2 but I don't wanna do that as my original tables are too huge to handle this).
Any input is appreciated.
Thanks,
Sai
View 1 Replies
View Related
Oct 21, 2004
Is there a way to do so on the fly in SQL Server 2000? In other words, a field has the latest update date for the table and we wish to use this date as part of the table name. If so, please provide an example.
ddave
View 1 Replies
View Related
Jun 24, 1999
I think this is a very simple question, however, I don't know the
answer. What is the difference between a regular Temp table
and a Global Temp table? I need to create a temp table within
an sp that all users will use. I want the table recreated each
time someone accesses the sp, though, because some of the
same info may need to be inserted and I don't want any PK errors.
thanks!!
Toni Eibner
View 2 Replies
View Related
Dec 30, 2006
How to I make it so that a Authenticated User to a website can append a record to a SQL table. I have watched the video on the asp.net website about using database on a web, it shows how to allow a user to change a record in a database, but nothing I have seen so far shows how they can append a record.
What I am trying to do: I am building a website for a ATV club using Visual Studio 2005 and c#. I am setting up users or members on the site (club members will have a user account, while all others will be un-authenticated users. I am setting up a classifieds area where members can post items for sale, items they are looking for, etc.
I am planning to use roles to allow authenticated users access to a webpage located in a restricted directory. There I want to place a XHTML page which would allow the users to post their classified ads for free. I will have another page that will allow everyone to view the ads not just club members. I want to make this as easy to maintain as possible. I don’t plan on having all the postings come through me to be placed on the web, I want it all automated.
View 2 Replies
View Related
Nov 23, 2007
I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.
The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?
Thanks in advance.SMc
View 2 Replies
View Related
Jul 20, 2005
In Access I have a macro that, each night, takes a table with aprimary key defined in it, and deletes all the rows. Then itimports/appends records from a fixed width text file. In this way,since the table is not deleted and recreated, the primary key is keptintact.What would be the equivalent SQL method for doing this in an automatedway? I've tried letting DTS import the table from Access, but theprimary key is lost. Is there some way to "empty" a table instead ofdropping it, and then append new records so that the table will end uphaving the primary key I want it to have?Thanks.Larry- - - - - - - - - - - - - - - - - -"Forget it, Jake. It's Chinatown."
View 4 Replies
View Related
Aug 15, 2007
Hi,
Im creatting an SSIS project that uses an Data Flow OLE DB Source to read data from an SQL Table and import it into a Destination table using Data Flow OLE DB Destination. but now everytime I run the project it appends all the rows not the new data rows only. How can I make the application so that it appends only the new data from a source table to a destination table. Is there maybe another Data Flow Control that can copy source table to destination and the next time it runs it only copy new rows. or any other way to do this using SSIS.
Your assistance will be highly appreciated.
View 4 Replies
View Related
Dec 13, 1999
Hello,
I was wondering if there was a different approach I should take in appending data to a table...
My destination table has about 94+ million records in it, and I have been taking two approaches to getting new files into this table:
1) I do a data pump task in a DTS to import the file to a trans (temp) table, which is truncated every time, and then do an INSERT INTO statement from the temp table to my destination table.
The import to the trans table only takes a few minutes (about 1 - 2 million records per file, but have short record lenghts,) but when I do the INSERT INTO statement, it takes upwards of 6 hrs to append.
2) I have tried doing a bulk insert task, going directly to the destination table (which defeats the purpose of my trans table to check out the data prior, but I feel the data is clean at this point.)
I am running the bulk insert right now, and it's been running for over 3 hours...so I'm going to assume this will take just as long as the INSERT INTO statement does like I did before.
My destination table does not have any indexes in it at all, and I don't need to do any transformations to the data when bringing it into SQL since the data is clean. Also, I have a default value constraint on one of my fields on the destination table.
Plus there are other ppl and applications hitting the server which could impact the overall processing, but nothing out of the ordinary is going on the server today. I know there are only so many ways to get a file into a table...but maybe someone knows a different way I should try this.
Thanks for anyone's suggestions!
Kael.
View 1 Replies
View Related
Sep 22, 1998
Any known problems with using an Access Append query to add data to an SQL table?
View 1 Replies
View Related
May 12, 2004
I'm new to SQL server. I want to add or append a unique set of rows to a destination table from a source table, they are essentially the same table by definition. The source table is updated every hour via DTS, all rows deleted and new set added. Both tables have the same primary key. Approximately 40 unique rows are created each hour and I would think the best approach would be to append the new rows to the destination table. I think an Append query will run into a primary key conflict.
In Access, I did this within VB by checking the max value of the primary key and then running the append for any values greater than that.
In SQL, I'm not sure if this should be done as a stored procedure or if there is an easier approach altogether.
View 2 Replies
View Related
Aug 4, 2006
What I would LIKE to do is noted in the subject line. What I'm findingis that "edit SQL" appears to only be an option if I am creating atable. If I select "append to" the option to edit SQL shades itself asunavailable.The reason I'd like this is that there is a datum in the flat file thatindicates whether that record should be appended to that table notedabove. There are other ways of dealing with this "problem" but it wouldbe nice to be able to control it using SQL, in the DTS import/exportwizard.If the source of my data is an SQL table, I can generate an SQL queryto specify what fields to import in an append, to check for existingvalues, etc...Is there a way around this? I can reserve a table for data transfers,regularly overwrite it with new data from text file inputs, and use SQLto insert select fields from that transfer table to other databasetables. (From this "transfer" table, data needs to be inserted intofour separate tables in our database).I hope this is clear. If it CAN'T be done this way, it's okay...just alittle ugly with the need to re-create the transfer table.
View 3 Replies
View Related
Feb 23, 2008
I have a series of .csv files created by a parts system. The .csv filename is in the format partnumber.csv The csv file contains a date column and 6 other fields. Each csv file is about 4000 records and there are approx 7000 .csv files that get re-generated once a week.
I'm using c# SqlBulkCopy object to import the csv file into a temp table. No problem there. It works realy fast.
What I need now is a way to move the data from the temp table to the final table and append the partnumber. I'm thinking it would be easy to pass the partnumber in as a paramerter to a t-sql query but not sure how to write the query itself.
I also want to check if the partnuber/datestamp combination from the temp table already exists in the final table and skip it if it does. So I suppose I ultimately need an update query.
Once I have that query written it's easy to import a .csv, launch the update query, wipe the temp table and repeat with the next .csv file.
Temp Table Layout: DateStamp(datetime), Field1 ... Field6(all real's)
Final Table Layout: PartNumber(varchar), DateStamp(datetime), Field1 ... Field6(all real's)
TIA.
View 4 Replies
View Related
Aug 29, 2013
I have the following query and I would like to combine it into one query instead of using several temp tables.
IF OBJECT_ID('Tempdb..#a') IS NOT NULL DROP TABLE #a
SELECT *
INTO #a
FROM #Web_table w WITH(NOLOCK)
WHERE w.generated_date IS NOT NULL
IF OBJECT_ID('Tempdb..#b') IS NOT NULL DROP TABLE #b
[code]....
View 2 Replies
View Related
Jul 25, 2005
I have an existing table I need to add data to. The data is in a text file, and the existing table already has data in it (I don't want to delete this I want to add to it).
I used Microsoft's import utility but this created a seperate table with generic fieldnames (column01, column02, ect). Is there a step in this wizard I missed?
View 2 Replies
View Related
Oct 18, 2015
I want to append the column to the transaction table(60 million records in it.) ..
Our transaction table is being used in production.. but i have very less amount of time ..
Instead of alter table.. (IF we use the alter to take backup of table and do the processing it will take more time). Is there any way to append the column to the transaction table ..
View 8 Replies
View Related
May 6, 2008
Hi Guys,
I have to work with a poorly designed table :(, that has columns
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
Description varchar(8000) null,
ProductTypevarchar(255) null,
The reason it is poorly designed is the table is used to hold questions and answers, all with a 1:1 relationship. Instead of having ID, ProductType, Question, Answer they have unfortunately adopted the approach of the above i.e:
id 1
thisID 3
parentid nuLL
DESCRIPTION: this is a question
id 20
thisID 3_1
parentID 3
DESCRIPTION: this is the answer to the question above
So I am writing a sproc that does this using a temp table. I got this far:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Spencer
-- =============================================
ALTER PROCEDURE [dbo].[GetFAQs]
-- Add the parameters for the stored procedure here
@ProductType varchar(255)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE TEMP
(
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
DescriptionQvarchar(8000) null,
DescriptionAvarchar(8000) null,
ProductTypevarchar(255) null,
)
SELECT
ID,
thisID,
parentID,
Title,
DescriptionQ,
DescriptionA,
ProductType
FROM
A2Z
WHERE
ProductType = @ProductType AND parentID IS NULL
END
GO
This gets all my questions for that product type.
What I need to do is load the questions into my temp table and then run through the a2z table again gaining the answers to the questions (the parentid holds the question ID). The answers then will also get loaded into the temp table.
Any bright sparks out there that can help me?
Cheers
View 9 Replies
View Related
Nov 15, 2007
Hi
I have two queries, one which takes 1 second to execute and the other which takes less that a second to execute.
I need to join them up similar to the following but when I do it take forever for them to execute.
select *
from table
where column not in (select column from table2)
It is quite complex and I have a couple of views included in the queries but it executes quickly on my dev database and when I run it on the live one it takes forever. Granted there is alot more data on the live database but the individual queries only take a second to run.
Does anyone know why there would be such a time increase whenever I join the queries and run them on my live database?
Any help would be appreciated.
Thanks,
Frankie
View 4 Replies
View Related
Aug 11, 2004
*************************************************************
<code> tags added by moderator. Please use <code> and
</code> tags to bracket comments to make code readable.
*************************************************************
I am a beginner to this whole .net thing so my code is probablly scary, but anyway, I am looking for a way to insert records into a database and then get the last ID. I am sure there is a much better way to do this, but here is the code that I have that ins't quite working:
'SQL Insert data'
Dim Conn As SqlConnection
Dim Rdr As SqlDataReader
Dim strConn As String = AppSettings("doConnect")
Dim strSQL As String = "INSERT INTO USERS (username, password, accountNum) VALUES ('" + Email.Text.Trim() + "', '" + password.Text.Trim() + "', '" + Session("accountNum").Trim() + "')"
Conn = New SqlConnection(strConn)
Dim Cmd As New SqlCommand(strSQL, Conn)
Conn.Open()
Cmd.ExecuteNonQuery()
'SQL Get Last ID
Dim ConnGet As SqlConnection
Dim RdrGet As SqlDataReader
Dim strConnGet As String = AppSettings("doConnect")
Dim strSQLGet As String = "select MAX(id) as maxid from USERS"
ConnGet = New SqlConnection(strConn)
Dim CmdGet As New SqlCommand(strSQL, Conn)
ConnGet.Open()
If CmdGet.ExecuteScalar() <> 0 Then
RdrGet = CmdGet.ExecuteReader()
While RdrGet.Read()
Session("theID") = RdrGet("name")
End While
Else
Session("theID") = 0
End If
View 1 Replies
View Related
