Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Temporal Tables

Author  Topic 

jfm
Posting Yak Master

145 Posts

Posted - 2013-08-01 : 06:33:10
Hi all,

I want to cross to tables using temporal tables, so I dont need to create a third table to do the extraction.

Im using the following query:

select s.*, f.date_col
from table_S as s
into table_new
left join table_F as f
on f.id_col = s.id_col

and i need to cross with another table, to subtract the duplicates in table_3

select s.*
from table_new as f
left join table_S as s
on f.id_col = s.id_col


I have to issues:

1. I dont know how to work with temporal tables.
2. I will like to use: group by, but i dont know if i can do it without using the column names.


Any tips?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 07:37:51
what do you mean by "need to cross with another table"? your requirement is not clear from explanation above.

the posted select is also wrong. it should be


select s.*, f.date_col
into table_new
from table_S as s
left join table_F as f
on f.id_col = s.id_col


if you can post some data and explain what you want we might be able to help


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 2013-08-01 : 08:40:59
Thank you.

Well, in Table_S i have all my data, but i need to copy date_col from File_F into File_S.

I will need to match the Id's from Table_S and Table_F and copy the date_col from Table_F to Table_S. But just the up do the existing Id's in Table_S.

So my new_table_S will be the current Table_S + Id_col (From file_f)

By using this query:

select s.*, f.date_col
into table_new
from table_S as s
left join table_F as f
on f.id_col = s.id_col

My File_S , hat had 2 million rows now has 3m rows

Thank you


quote:
Originally posted by visakh16

what do you mean by "need to cross with another table"? your requirement is not clear from explanation above.

the posted select is also wrong. it should be


select s.*, f.date_col
into table_new
from table_S as s
left join table_F as f
on f.id_col = s.id_col


if you can post some data and explain what you want we might be able to help


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

WAmin
Starting Member

16 Posts

Posted - 2013-08-01 : 10:26:14
Looking at your query it should solve your problem, no?

I am not sure about your server specs, and not sure if you are running this query on production server?

What i will suggest
1- Try creating new table in advance.
2- Use task->Export data, it will give you visual confirmation how many records are transferred.
3- If it is production server then run your query in batches.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-01 : 12:05:08
quote:
Originally posted by jfm

By using this query:

select s.*, f.date_col
into table_new
from table_S as s
left join table_F as f
on f.id_col = s.id_col



Saample data sould be great, but is the query you posted not working?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 12:30:30
Sorry your last explanation is confusing. Unless you explain with some sample data and output nobody will be able to help you out

Table_S i have all my data, but i need to copy date_col from File_F into File_S
whats File_F and File_S? What has Table_S to do in it?

I will need to match the Id's from Table_S and Table_F and copy the date_col from Table_F to Table_S. But just the up do the existing Id's in Table_S.

first part sounds like UPDATE action to me
Cant understand the second statement though!
So my new_table_S will be the current Table_S + Id_col (From file_f)
This again sounds like addition of new column (DDL) + update

so each statement speaks of different scenario IMO which is really confusing



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -