May 25, 2009 at 9:42 am
I would like to write up a query that inserts specific column data from one table to another after a given time interval. Say after every 5min.
I have this select into but I think it only works when the column names are identical. In this case they can't be identical.
SELECT name,account
INTO [dbo].[@Account]
FROM dbo.Sales
May 25, 2009 at 10:00 am
Hi martin
The SELECT INTO only works if the destination table does not yet exist. If you want to insert the data into an existing table you have to use "INSERT INTO ... SELECT ... FROM ...". The names of the columns doe not matter. The data types and the ordinal position of the source columns have to match with destination columns. You can specify the destination column order in your INSERT clause as well as the source column order in your SELECT clause:
INSERT INTO myDest (Col1, Col2)
SELECT OtherCol1, CONVERT(INT, OtherCol2)
FROM anySource
Greets
Flo
May 25, 2009 at 10:01 am
You typically use an insert..select to insert and in that case, you can specify the columns. If you look in Books Online, you will see examples for the insert statement.
As far as the 5 minutes, you would need some type of loop or scheduler to make this happen. The WAITFOR statement might help as well, depending on your requirements.
If you provide more information, and code you've tried to write, we can help more.
May 25, 2009 at 10:05 am
You're asking two different questions here. First "how to run a query every 5 minutes?" Answer: put the query in a stored procedure, then write a SQL Agent Job that calls that stored procedure. Now schedule the Job to run every 5 minutes. (You don't actually need a stored procedure to do this, but it make maintenance easier).
And secondly:
martin.edward (5/25/2009)
I have this select into but I think it only works when the column names are identical. In this case they can't be identical.SELECT name,account
INTO [dbo].[@Account]
FROM dbo.Sales
Well, several things: first, "@Account" is a very bad name for a table, because that "@" at the front is normally used to indicate a a variable name. You, and everyone else who will have to work with it will find this immensely confusing and error-prone. Additionally, if you ever try to do anything XML-related with that table, you will find that the "@" is going to cause tons of confusion because that is normally how you specify attribute-addressing in XML. I strongly recommend that you change it.
Secondly, SELECT INTO always makes a new table. If the table named already exists, then SELECT INTO will fail with an error. Since you are running this every 5 minutes, you will have to be sure to remove the table when you are done, or the next run will fail.
Finally, your statement "I have this select into but I think it only works when the column names are identical. In this case they can't be identical.". Is confusing to us. SELECT INTO makes what ever columns that you name. Since you named the columns "name,account", it will create the columns [name] and [account] from the columns of the same name from the SALES table.
If your are saying that you want to rename the columns in the SELECT INTO, then you do it by adding " As [someName]" after each column, like this:
SELECT
name As [newName]
, account As [newAccount]
INTO [dbo].[Account]
FROM dbo.Sales
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 25, 2009 at 10:18 am
That code is going to create a table called @account in your database! You’ll need to call a sql EXEC command. You’ll need to set @account variable to increment/have time stamp in it, otherwise that code is going to throw an error after you call it the first time, as Flo indicates.
If you are wanting to create a new table then you need to do something like:
DECLARE @account nvarchar(50)
SELECT @account = 'temp' + CONVERT(NVARCHAR(20),GETDATE(),126)
DECLARE @Sql nvarchar(max)
SELECT @Sql = 'SELECT Name as OtherName, Account as OtherAccount into [' + @account + '] FROM Sales'
exec(@Sql)
May 26, 2009 at 10:14 am
Thanks,
I have created a stored procedure and scheduled it on a job running every 3 minutes. problem is it keeps overwriting data over and over so I tweaked the query to this to prevent the overwrites
DECLARE @SEQNO INT;
DECLARE @UID INT;
SET @SEQNO = (SELECT MAX (SEQNO) FROM dbo.JOBCOST_JOBLINESVIEW WHERE LINETYPE = 'S')
SET @UID = (SELECT MAX (U_ID) FROM dbo.EIM_SUBJOB)
INSERT INTO dbo.SUBJOB_DATES (U_subjobid, U_transdate)
SELECT U_SUBJOB,U_TRANSDATE
FROM dbo.SUBJOB where @UID = @SEQNO
This does not resolve this however.
May 26, 2009 at 10:24 am
What is the problem?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2009 at 10:26 am
What does it not resolve? We can't see your system or the code, so it's hard for us to answer when you don't provide information.
What is it doing, or not doing, that you need to happen. Show us what the table looks like, sample data, or explain in detail what you are trying to accomplish.
May 26, 2009 at 11:16 pm
Hello,
What am trying to do is copy data from table A to another table B, all new data from the table A should be copied to table B, this should happen after a certain period of time such that all new data on A is copied to B without duplicates.
This is what am trying to do with the query above. But it not working.
May 26, 2009 at 11:30 pm
martin.edward (5/26/2009)
Hello,What am trying to do is copy data from table A to another table B, all new data from the table A should be copied to table B, this should happen after a certain period of time such that all new data on A is copied to B without duplicates.
This is what am trying to do with the query above. But it not working.
Yes, that's why we asked you what the problem was. You haven't told us yet.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2009 at 7:00 am
...so the job is working, but... It's your query that's not inserting the expected results? There wouldn't be an "overwrite" for an INSERT statement. An "overwrite" would only occur if you were writing an UPDATE statement, or the query was deleting records, or truncating the table. The INSERT would either throw a duplicate record error if there was a PK or UIDX on the table, or would INSERT a duplicate row.
You could always use an INSERT TRIGGER, but this would write the data to table B whenever an INSERT occurs and might not meet your requirments.
June 4, 2009 at 2:05 am
Hi,
maybe u should post a few lines of data from the input, and then explain what u want to see after each run.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply