query equivalent: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

Losing temporal tables

Hi, i want to know if there's any particular reason why a temporal
table can be dropped before closing the session.
I'm having the following problem: i create the temp table when a form
of my application is created, work with it and then drop it on the
form's close event.
It works fine most of the time, but from time to time the table seems
to be dropped before i close the form because i'm having a: #MyTable
doesn't exists error message.
Any ideas?
Working against SQL2K, W2K Server, from a W2K Pro machine.
Thanx
If your connection is getting dropped at any time you will loose the temp
table. Temp tables are really designed for a brief life span. If you need
to hold certain information for long periods of time like that you may want
to consider using real tables. Or better yet maybe a RS on the client.
Andrew J. Kelly SQL MVP
"Guillermo Casta?o A" <guillermoc74@.hotmail.com> wrote in message
news:9350d78d.0409170946.33db2291@.posting.google.c om...
> Hi, i want to know if there's any particular reason why a temporal
> table can be dropped before closing the session.
> I'm having the following problem: i create the temp table when a form
> of my application is created, work with it and then drop it on the
> form's close event.
> It works fine most of the time, but from time to time the table seems
> to be dropped before i close the form because i'm having a: #MyTable
> doesn't exists error message.
> Any ideas?
> Working against SQL2K, W2K Server, from a W2K Pro machine.
> Thanx

Losing Temp Tables Between Tasks

I have a control flow setup with 5 tasks.

1. Create Temp Table (Execute SQL)
2. Load Temp Table (Execute SQL)
3. Data Flow Task using the temp table
4. Data Flow Task using the temp table
5. Drop Temp Table (Execute SQL)

Tasks 3 & 4 are currently setup to run parallel/concurrently - meaning they both branch directly off of task #2. They both also lead to task #5. Also, my connection manager is set to retain its connection.

Here's the problem. When executing this flow, task #3 will sometimes fail, other times it will succeed - without me changing anything between runs (I simply end debugging and run it again). When it does fail it is always because it cannot find the temp table. One thing to note is that task #4 (running at the same time as #3 and using the same temp table) always finishes first because it does quite a bit less work. Last thing to note: if I set up task 4 to run after 3 it works everytime, but of course it takes much longer for the total package to execute.

How is it that the task can find the temp table on some runs, but not on others? If this is just quirky behavior that I am going to have to live with for the time being, is there a way to force a task to re-run X number of times before giving up and moving on?

Thanks,
David Martin

Temp tables are only guaranteed to remain in existence as long as they are referenced. Is it possible that the connection is being dropped and reopened by SSIS for some reason between tasks?

Or that there is some time during the processing where Sql Server cannot detect the temp table is still being referrenced, so drops it even if the connection is still open?

Seems like using temp tables is risky if you don't have full and explicit control over their existence. I want to use them also but not if I cannot guarantee they will be there when I need them!

|||

Make sure the "RetainSameConnection" property is set to "True" for the connection. This will allow the Execute SQL tasks to share the connection so the temp table can be accessed.

Frank

sql

losing data from tables

Hi!
Is there any known problem with MSSQL 7.0 losing data from tables?
I have an application that is used by 150 seperate customer and each
customer have about 10 users. The customers have theirs database at a
server administrated by me. The application access its database through
ODBC. In maintenanse plan I have a short-time backup of the databases
three times a day (8:30, 11:30, 14:30) and a 7 days backup every day at
19:00.
Sometime a stange malfunction occur. Two tables are empty. The last 7
days backup from yeasterday is ok but when they started using the
application next morning the two tables are empty. I is always the same
two tables. This malfunction happens one to two times per year.
any proposal?
Best regards,
Bertil MorefältBertil,
To know what is happening you'd need to be able to view the activity for the
database. This can be done by a trace (Profiler) and or by viewing the
activities contained in the transaction log (third-party log tool). Might
check out:
http://www.lumigent.com/products/le_sql.html
for the future. This type of tool may allow you to "undo" the deletes as
well.
HTH
Jerry
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:OxDh9qbyFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi!
> Is there any known problem with MSSQL 7.0 losing data from tables?
> I have an application that is used by 150 seperate customer and each
> customer have about 10 users. The customers have theirs database at a
> server administrated by me. The application access its database through
> ODBC. In maintenanse plan I have a short-time backup of the databases
> three times a day (8:30, 11:30, 14:30) and a 7 days backup every day at
> 19:00.
> Sometime a stange malfunction occur. Two tables are empty. The last 7 days
> backup from yeasterday is ok but when they started using the application
> next morning the two tables are empty. I is always the same two tables.
> This malfunction happens one to two times per year.
> any proposal?
> Best regards,
> Bertil Morefält|||Hi Bertil,
It's really strange that two tables will become empty and I believe some
operations must be done. You may follow the Jerry's suggestion using
Profiler to trace the SQL actions.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I've recently had some data issues with my MS SQL Server 2000. From
time to time, we will lose some data from tables here and there. I
don't know what's causing it, but it's a pretty decent size database.
We won't loose a whole tables' worth, but some data.
About 50 tables, some of the tables, such as our: vendors, leads,
contacts, manufacturers can have up to 12,000 records, but that's about it.
About Jerry's suggestion.. Does Microsoft have any profilers for their
own products or do you always have to go third party? Thanks.
Marc F.
Michael Cheng [MSFT] wrote:
> Hi Bertil,
> It's really strange that two tables will become empty and I believe some
> operations must be done. You may follow the Jerry's suggestion using
> Profiler to trace the SQL actions.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||You can use SQL Profiler which comes with SQL Server.
-Sue
On Fri, 20 Jan 2006 15:31:59 -0500, Marc Ferguson
<marc@.digitalalias.net> wrote:
>Hi,
>I've recently had some data issues with my MS SQL Server 2000. From
>time to time, we will lose some data from tables here and there. I
>don't know what's causing it, but it's a pretty decent size database.
>We won't loose a whole tables' worth, but some data.
>About 50 tables, some of the tables, such as our: vendors, leads,
>contacts, manufacturers can have up to 12,000 records, but that's about it.
>About Jerry's suggestion.. Does Microsoft have any profilers for their
>own products or do you always have to go third party? Thanks.
>Marc F.
>
>Michael Cheng [MSFT] wrote:
>> Hi Bertil,
>> It's really strange that two tables will become empty and I believe some
>> operations must be done. You may follow the Jerry's suggestion using
>> Profiler to trace the SQL actions.
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no rights.|||Hey Sue,
Thanks. I didn't know it had that. I'm playing with it now.. is there
any way for this to automatically run in the background or do I always
have to start it up?
Marc F.
Sue Hoegemeier wrote:
> You can use SQL Profiler which comes with SQL Server.
> -Sue
> On Fri, 20 Jan 2006 15:31:59 -0500, Marc Ferguson
> <marc@.digitalalias.net> wrote:
>
>>Hi,
>>I've recently had some data issues with my MS SQL Server 2000. From
>>time to time, we will lose some data from tables here and there. I
>>don't know what's causing it, but it's a pretty decent size database.
>>We won't loose a whole tables' worth, but some data.
>>About 50 tables, some of the tables, such as our: vendors, leads,
>>contacts, manufacturers can have up to 12,000 records, but that's about it.
>>About Jerry's suggestion.. Does Microsoft have any profilers for their
>>own products or do you always have to go third party? Thanks.
>>Marc F.
>>
>>Michael Cheng [MSFT] wrote:
>>Hi Bertil,
>>It's really strange that two tables will become empty and I believe some
>>operations must be done. You may follow the Jerry's suggestion using
>>Profiler to trace the SQL actions.
>>
>>Sincerely yours,
>>Michael Cheng
>>Microsoft Online Partner Support
>>When responding to posts, please "Reply to Group" via your newsreader so
>>that others may learn and benefit from your issue.
>>=====================================================>>This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Marc,
Sure...You can run the same thing that Profiler runs using
server side traces - produces a trc file that you can open
in profiler or you can view as well as import into a table
using fn_trace_gettable.
The quickest way to get started with it is to just use
Profiler to create the trace you want. Then in Profiler, go
to File and select Script Trace. This will create the server
side trace for you - all you would really need to change on
the script is the destination file for the trace. You can
execute traces in jobs if you want.
If you execute the entire script you generated with
Profiler, it will start up the trace. You can see in the
script how the tracing is done with the trace stored
procedures. The stored procedures for traces are named
sp_trace_xxx.
There are also a few fn_trace_xxx trace functions you can
use as well. To get information on all traces that are
running, use:
SELECT * FROM :: fn_trace_getinfo(default)
You get the trace id when you execute this which can help
you in using the other functions, stored procedures that use
the trace id for a parameter (such as sp_trace_setstatus).
Hope that helps get you going with it -
-Sue
On Fri, 20 Jan 2006 16:57:31 -0500, Marc Ferguson
<marc@.digitalalias.net> wrote:
>Hey Sue,
>Thanks. I didn't know it had that. I'm playing with it now.. is there
>any way for this to automatically run in the background or do I always
>have to start it up?
>Marc F.
>Sue Hoegemeier wrote:
>> You can use SQL Profiler which comes with SQL Server.
>> -Sue
>> On Fri, 20 Jan 2006 15:31:59 -0500, Marc Ferguson
>> <marc@.digitalalias.net> wrote:
>>
>>Hi,
>>I've recently had some data issues with my MS SQL Server 2000. From
>>time to time, we will lose some data from tables here and there. I
>>don't know what's causing it, but it's a pretty decent size database.
>>We won't loose a whole tables' worth, but some data.
>>About 50 tables, some of the tables, such as our: vendors, leads,
>>contacts, manufacturers can have up to 12,000 records, but that's about it.
>>About Jerry's suggestion.. Does Microsoft have any profilers for their
>>own products or do you always have to go third party? Thanks.
>>Marc F.
>>
>>Michael Cheng [MSFT] wrote:
>>Hi Bertil,
>>It's really strange that two tables will become empty and I believe some
>>operations must be done. You may follow the Jerry's suggestion using
>>Profiler to trace the SQL actions.
>>
>>Sincerely yours,
>>Michael Cheng
>>Microsoft Online Partner Support
>>When responding to posts, please "Reply to Group" via your newsreader so
>>that others may learn and benefit from your issue.
>>=====================================================>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>|||Hi Sue,
Thanks for that info. I was wondering if there are any tutorials or
docs on Profiler? I have "Sams Teach Yourself Microsoft SQL Server 2000
in 21 Days", but it only mentions profiler. I was playing with it and a
lot of the events and their displayed data doesn't make sense to me.
I'm trying them one-by-one to see what kind of result they show after I
do something related to our database. As you probably know.. it's
becoming a long process. :D
Marc F.
Sue Hoegemeier wrote:
> Hi Marc,
> Sure...You can run the same thing that Profiler runs using
> server side traces - produces a trc file that you can open
> in profiler or you can view as well as import into a table
> using fn_trace_gettable.
> The quickest way to get started with it is to just use
> Profiler to create the trace you want. Then in Profiler, go
> to File and select Script Trace. This will create the server
> side trace for you - all you would really need to change on
> the script is the destination file for the trace. You can
> execute traces in jobs if you want.
> If you execute the entire script you generated with
> Profiler, it will start up the trace. You can see in the
> script how the tracing is done with the trace stored
> procedures. The stored procedures for traces are named
> sp_trace_xxx.
> There are also a few fn_trace_xxx trace functions you can
> use as well. To get information on all traces that are
> running, use:
> SELECT * FROM :: fn_trace_getinfo(default)
> You get the trace id when you execute this which can help
> you in using the other functions, stored procedures that use
> the trace id for a parameter (such as sp_trace_setstatus).
> Hope that helps get you going with it -
> -Sue
> On Fri, 20 Jan 2006 16:57:31 -0500, Marc Ferguson
> <marc@.digitalalias.net> wrote:
>
>>Hey Sue,
>>Thanks. I didn't know it had that. I'm playing with it now.. is there
>>any way for this to automatically run in the background or do I always
>>have to start it up?
>>Marc F.
>>Sue Hoegemeier wrote:
>>You can use SQL Profiler which comes with SQL Server.
>>-Sue
>>On Fri, 20 Jan 2006 15:31:59 -0500, Marc Ferguson
>><marc@.digitalalias.net> wrote:
>>
>>Hi,
>>I've recently had some data issues with my MS SQL Server 2000. From
>>time to time, we will lose some data from tables here and there. I
>>don't know what's causing it, but it's a pretty decent size database.
>>We won't loose a whole tables' worth, but some data.
>>About 50 tables, some of the tables, such as our: vendors, leads,
>>contacts, manufacturers can have up to 12,000 records, but that's about it.
>>About Jerry's suggestion.. Does Microsoft have any profilers for their
>>own products or do you always have to go third party? Thanks.
>>Marc F.
>>
>>Michael Cheng [MSFT] wrote:
>>
>>Hi Bertil,
>>It's really strange that two tables will become empty and I believe some
>>operations must be done. You may follow the Jerry's suggestion using
>>Profiler to trace the SQL actions.
>>
>>Sincerely yours,
>>Michael Cheng
>>Microsoft Online Partner Support
>>When responding to posts, please "Reply to Group" via your newsreader so
>>that others may learn and benefit from your issue.
>>=====================================================>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>|||Marc,
SQL Magazine had some good articles on Profiler - you can
try searching for those at: www.sqlmag.com
This web cast may help:
Support WebCast: SQL Server 2000 Profiler: What's New and
How to Effectively Use It
http://support.microsoft.com/default.aspx?id=325263
And you can find Profiler tips at:
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
-Sue
On Mon, 23 Jan 2006 09:49:02 -0500, Marc Ferguson
<marc@.digitalalias.net> wrote:
>Hi Sue,
>Thanks for that info. I was wondering if there are any tutorials or
>docs on Profiler? I have "Sams Teach Yourself Microsoft SQL Server 2000
>in 21 Days", but it only mentions profiler. I was playing with it and a
>lot of the events and their displayed data doesn't make sense to me.
>I'm trying them one-by-one to see what kind of result they show after I
>do something related to our database. As you probably know.. it's
>becoming a long process. :D
>Marc F.
>Sue Hoegemeier wrote:
>> Hi Marc,
>> Sure...You can run the same thing that Profiler runs using
>> server side traces - produces a trc file that you can open
>> in profiler or you can view as well as import into a table
>> using fn_trace_gettable.
>> The quickest way to get started with it is to just use
>> Profiler to create the trace you want. Then in Profiler, go
>> to File and select Script Trace. This will create the server
>> side trace for you - all you would really need to change on
>> the script is the destination file for the trace. You can
>> execute traces in jobs if you want.
>> If you execute the entire script you generated with
>> Profiler, it will start up the trace. You can see in the
>> script how the tracing is done with the trace stored
>> procedures. The stored procedures for traces are named
>> sp_trace_xxx.
>> There are also a few fn_trace_xxx trace functions you can
>> use as well. To get information on all traces that are
>> running, use:
>> SELECT * FROM :: fn_trace_getinfo(default)
>> You get the trace id when you execute this which can help
>> you in using the other functions, stored procedures that use
>> the trace id for a parameter (such as sp_trace_setstatus).
>> Hope that helps get you going with it -
>> -Sue
>> On Fri, 20 Jan 2006 16:57:31 -0500, Marc Ferguson
>> <marc@.digitalalias.net> wrote:
>>
>>Hey Sue,
>>Thanks. I didn't know it had that. I'm playing with it now.. is there
>>any way for this to automatically run in the background or do I always
>>have to start it up?
>>Marc F.
>>Sue Hoegemeier wrote:
>>You can use SQL Profiler which comes with SQL Server.
>>-Sue
>>On Fri, 20 Jan 2006 15:31:59 -0500, Marc Ferguson
>><marc@.digitalalias.net> wrote:
>>
>>Hi,
>>I've recently had some data issues with my MS SQL Server 2000. From
>>time to time, we will lose some data from tables here and there. I
>>don't know what's causing it, but it's a pretty decent size database.
>>We won't loose a whole tables' worth, but some data.
>>About 50 tables, some of the tables, such as our: vendors, leads,
>>contacts, manufacturers can have up to 12,000 records, but that's about it.
>>About Jerry's suggestion.. Does Microsoft have any profilers for their
>>own products or do you always have to go third party? Thanks.
>>Marc F.
>>
>>Michael Cheng [MSFT] wrote:
>>
>>Hi Bertil,
>>It's really strange that two tables will become empty and I believe some
>>operations must be done. You may follow the Jerry's suggestion using
>>Profiler to trace the SQL actions.
>>
>>Sincerely yours,
>>Michael Cheng
>>Microsoft Online Partner Support
>>When responding to posts, please "Reply to Group" via your newsreader so
>>that others may learn and benefit from your issue.
>>=====================================================>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>>

losing data from tables

Wednesday, March 28, 2012

lopp for insert into

Hello,

I have 10 tables (T1Orj,T2Orj,…T10Orj) and I need to find modified rows from each table and insert them to T1Bak, T2Bak, …T10Bak. Although original and bak tables have the common fields , the original tables have more fields than bak tables, and T1Orj, T2Orj, … T10Orj tables have different table structures.

I can go head and write a insert into query for each table, I am just wondering Is there any way I can do this in a loop for all tables?

You can use a dataadapter to do this:DataSet ds =newDataSet();

SqlDataAdapter adp =newSqlDataAdapter("select * from authors; select * from sales","server=srv;Integrated Security=true;database=pubs");

adp.Fill(ds);

In this case the dataset ends up with two tables,
one for authors and one for sales.

If you want you can then go and rename them, as they end up with names like "Table", "Table1", "Table2", etc.

but they come out in the order you put in ....

then you can do

loops oops

Hi,
How can I walk through a result of a select query? I have two tables.
TableA(id, name1, name2, notes), TableB(id, grade). I have to bulk insert
some data, (given: name1, name2, notes and grande too). The "bulk insert"
inserts datas in one step. So first I insert the values a temporary table
(TableTmp). Then I have to walk through this TableTmp and at each row I
have to do this:
1, insert name1, name2, notes into TableA
2, get back the actual @.@.identity
3, insert grade into TableB where is the last identity.
I would like to make it in sql query analizer...
I have problem with the loop. I have no idea how to make it.
Is it not overcomplicated? Is there any simple solution?
Thank you for help
chris
Message posted via http://www.webservertalk.comI don't think you'll need a loop. Usually it's best to avoid loops and row
by row processing.
Unfortunately you haven't given us any clues about keys or constraints or
shown us the table where this data comes from. So here is a wild guess:
INSERT INTO TableA (col1, col2, col3)
SELECT DISTINCT col1, col2, col3
FROM Unspecified
INSERT INTO TableB (id, col4, col5, col6)
SELECT DISTINCT
A.id /* the IDENTITY col from A */ ,
U.col4, U.col5, U.col6
FROM Unspecified AS U
JOIN TableA AS A
ON U.col1 = A.col1
AND U.col2 = A.col2
AND U.col3 = A.col3
David Portas
SQL Server MVP
--|||Mary,
To me it sounds like your database design is a bit odd.
You have the details of a student(?) in TableA, and the grade that the
student got in TableB. Presumably if you actually have:
TableA (students): id, name1, name2, notes...
TableC (subjects): id, code, description...
TableB (grades): studentid, subjectid, grade
then you could import your data into a temporary table... and then make sure
that all your subjects and students are listed:
insert into TableA (name1, ...)
select t.name1, ...
from TableTemp t
where not exists (select * from TableA a where a.name1 = t.name1 and a.name2
= t.name2 and ...)
(and similar for TableC)
And then insert the grades:
insert into TableC (studentid, subjectid, grade)
select stud.id, subj.id, t.grade
from TableTemp t JOIN TableA stud on stud.name1 = t.name1 and stud.name2...
JOIN TableC subj on subj.code = t.code ...
The '...' are the other fields and stuff that you're looking to identify
students by.
Of course, it's much nicer to have a separate file of "studentcode",
"subjectcode", "grade", if you can fetch the details like that.
Rob|||Thanx.
It seems working..Thank you very much

Loops and building comma delimited strings

The problem:

I have 2 tables, with a one to many relationship - lets say customers, and order items.

Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:

1, 2, 3

And if the next order item for that customer has a quantity of 4, the reference number value is

4, 5, 6, 7

And the final item with quantity of 2:

8, 9

Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.

In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.

If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:

4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).

I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.

This is what I have so far. The print lines and hard coded values are for debugging purposes only.

DECLARE @.NumberingType varchar(10)
DECLARE @.TotalSum int
DECLARE @.DoorLineItemID int
DECLARE @.Quantity int
DECLARE @.SeedInt int

SET @.SeedInt = 1

SELECT @.TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345

DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1

OPEN UpdateRefCursor

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity
DECLARE @.RefNumberLine varchar(1024)
SET @.RefNumberLine = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN

WHILE @.SeedInt <= @.Quantity
BEGIN

SET @.RefNumberLine = @.RefNumberLine + CONVERT(varchar, @.SeedInt, 101) + ', '
SET @.SeedInt = @.SeedInt + 1

END
PRINT @.RefNumberLine

SET @.SeedInt = @.Quantity + @.SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @.SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @.Quantity + @.SeedInt, 101)

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity

END

CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor

This returns the same delimited string for X number of items. So I'm getting this:

1,2,3
1,2,3
1,2,3

When I really want the results described above.

What am I doing wrong?

Thanks!

You really need to post a table structure and some data for us to use to try this out. That's a lot of variables with no data to reference to try out.|||solved. Thanks for your input.

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

looping to drop temp tables

Hello,

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

Use TempDb

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

sql

looping to drop temp tables

Hello,

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

Use TempDb

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

Looping through tables in a db

Hello - I am somewhat new to stored procedures, so please be patient. I need to know how to go about wring a stored proc to loop through all of the tables in a db and delete the records in them.

I'm assuming a stored procedure is the best way to do this. If there is a better way, please let me know

I think I'm on the right track with this below

/* Create in each database that it is used in */

CREATE PROC usp_DBCCCheckTable

/* Declare Variables */

DECLARE @.v_table sysname,

@.v_SQL NVARCHAR(2000)

/* Declare the Table Cursor (Identity) */

DECLARE c_Tables CURSOR

FAST_FORWARD FOR

SELECT name

FROM sysobjects obj (NOLOCK)

WHERE type = 'U'

OPEN c_Tables

Try

CREATE PROCEDURE DeleteYourTableRecords
AS
BEGIN
declare @.SQL_Str nvarchar(max)
set @.SQL_Str=''
SELECT @.SQL_Str=@.SQL_Str +'delete from ' + name +';' from sys.tables
EXECUTE sp_executesql @.SQL_Str
END
GO|||

I cant dare running this on my machine so here you go with PRINT statement. just uncomment EXEC if you dare.

Sorry i dont like CURSORS so here you go ... hope this will help

DECLARE @.TABLE_NAME VARCHAR(500)

DECLARE @.CMD VARCHAR(MAX)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE'

ORDER BY

OBJECT_ID(TABLE_NAME)

WHILE @.@.ROWCOUNT > 0

BEGIN

SET @.CMD = 'TRUNCATE TABLE ' + @.TABLE_NAME

PRINT @.CMD

--EXEC(@.CMD)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

OBJECT_ID(TABLE_NAME) > OBJECT_ID(@.TABLE_NAME)

END

|||

Be sure you want to "DELETE" rather than "TRUNCATE"

Here's the code to do it. Don't run this unless you REALLY REALLY want to delete all the data from all your tables.

Code Snippet

select 'delete from [' + U.Name + '].[' + O.Name + '];' as [CMD]

into #CMDS

from sysobjects O

inner join sysusers U on U.UID = o.UID

where O.xType = 'U'

declare @.cmd varchar(255)

while exists(select * from #cmds)

begin

select top 1 @.cmd = cmd from #cmds

exec(@.cmd)

delete from #cmds where cmd = @.cmd

end

drop table #cmds

|||

In most cases, it is not as simple as deleting from each table in the database. If there are foreign key references then you need to delete data in a particular order unless you have cascading actions (it depends on the schema and it doesn't work for all types of relationships). Otherwise, you will get errors when you try to delete rows that are referenced by other tables. And truncate table as suggested in other replies is more restrictive. Lastly, what happens to any business logic in triggers? Do you need to unnecessarily execute them?

So what are you trying to do exactly? Are you trying to cleanup the database? It is better to just recreate the db from scratch using the scripts from your source code control system.

|||

I think that it will be fairly easy to just delete the rows in the tables. The way that my company's software is set up, there aren't many foreign keys. It's a long story and a dba would have a fit looking at it. I'm fairly used to now though. We do most of our linking through code.

Anyway, when we install a new system, we go through and delete all of our test data out of the tables and sometimes that can take awhile. I thought, since I'm trying to get better at stored procedures anyway, that it would be useful to write one to delete the rows out of the tables.

Looping through tables in a db

Hello - I am somewhat new to stored procedures, so please be patient. I need to know how to go about wring a stored proc to loop through all of the tables in a db and delete the records in them.

I'm assuming a stored procedure is the best way to do this. If there is a better way, please let me know

I think I'm on the right track with this below

/* Create in each database that it is used in */

CREATE PROC usp_DBCCCheckTable

/* Declare Variables */

DECLARE @.v_table sysname,

@.v_SQL NVARCHAR(2000)

/* Declare the Table Cursor (Identity) */

DECLARE c_Tables CURSOR

FAST_FORWARD FOR

SELECT name

FROM sysobjects obj (NOLOCK)

WHERE type = 'U'

OPEN c_Tables

Try

I cant dare running this on my machine so here you go with PRINT statement. just uncomment EXEC if you dare.

Sorry i dont like CURSORS so here you go ... hope this will help

DECLARE @.TABLE_NAME VARCHAR(500)

DECLARE @.CMD VARCHAR(MAX)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE'

ORDER BY

OBJECT_ID(TABLE_NAME)

WHILE @.@.ROWCOUNT > 0

BEGIN

SET @.CMD = 'TRUNCATE TABLE ' + @.TABLE_NAME

PRINT @.CMD

--EXEC(@.CMD)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

OBJECT_ID(TABLE_NAME) > OBJECT_ID(@.TABLE_NAME)

END

|||

Be sure you want to "DELETE" rather than "TRUNCATE"

Here's the code to do it. Don't run this unless you REALLY REALLY want to delete all the data from all your tables.

Code Snippet

select 'delete from [' + U.Name + '].[' + O.Name + '];' as [CMD]

into #CMDS

from sysobjects O

inner join sysusers U on U.UID = o.UID

where O.xType = 'U'

declare @.cmd varchar(255)

while exists(select * from #cmds)

begin

select top 1 @.cmd = cmd from #cmds

exec(@.cmd)

delete from #cmds where cmd = @.cmd

end

drop table #cmds

|||

So what are you trying to do exactly? Are you trying to cleanup the database? It is better to just recreate the db from scratch using the scripts from your source code control system.

|||

Looping through tables

Hi, I'm really new to SQL server so I need some assistance.
I have an SQL server database where there are some tables that are
named like this:
XXCCYY
where XX is the table name, CC is a two digit company code to identify
the correct company (eg 01 or 12) and YY are the two last numbers in
the year (01 for 2001 etc).
I would like to create a single table (or a view) in which I bring in
data from all tables that have a certain name (the XX part) and add a
column with the company code to all records.
How can I do this?Well, start with
CREATE TABLE #TableNames (name AS SYSNAME)
GO
INSERT INTO TableNames
SELECT t1.name
FROM sysobjects t1
INNER JOIN sysindexes t2
ON t1.id = t2.id
WHERE t2.indid <= 1
AND t2.rows > 0
AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
AND t1.name LIKE 'XX%'
GO
Now , loop throu the names and build a dynamix sql to insert the data , make
sure that all tables have the same structure
<joakim.andersson@.permobil.se> wrote in message
news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...
> Hi, I'm really new to SQL server so I need some assistance.
> I have an SQL server database where there are some tables that are
> named like this:
> XXCCYY
> where XX is the table name, CC is a two digit company code to identify
> the correct company (eg 01 or 12) and YY are the two last numbers in
> the year (01 for 2001 etc).
> I would like to create a single table (or a view) in which I bring in
> data from all tables that have a certain name (the XX part) and add a
> column with the company code to all records.
> How can I do this?
>|||if want use view
create view viewname as
select "C1",* from XXCCYY
union all
select "C2",* from X1CCYY
union all
select "C3",* from X1CCYY
where c1,c2,c3 is company code
--soonyu
"joakim.andersson@.permobil.se" wrote:
> Hi, I'm really new to SQL server so I need some assistance.
> I have an SQL server database where there are some tables that are
> named like this:
> XXCCYY
> where XX is the table name, CC is a two digit company code to identify
> the correct company (eg 01 or 12) and YY are the two last numbers in
> the year (01 for 2001 etc).
> I would like to create a single table (or a view) in which I bring in
> data from all tables that have a certain name (the XX part) and add a
> column with the company code to all records.
> How can I do this?
>|||Hi Uri,
That was really helpful and I've done the create table part and it
worked like a charm. BUT I'm not good at all at the programming part so
could you please give me a hint at how to make that loop as well. The
SQL part I can handle - just not the programming.
Uri Dimant skrev:
> Well, start with
> CREATE TABLE #TableNames (name AS SYSNAME)
> GO
> INSERT INTO TableNames
> SELECT t1.name
> FROM sysobjects t1
> INNER JOIN sysindexes t2
> ON t1.id = t2.id
> WHERE t2.indid <= 1
> AND t2.rows > 0
> AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
> AND t1.name LIKE 'XX%'
> GO
> Now , loop throu the names and build a dynamix sql to insert the data , make
> sure that all tables have the same structure
>
>
> <joakim.andersson@.permobil.se> wrote in message
> news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...
> > Hi, I'm really new to SQL server so I need some assistance.
> >
> > I have an SQL server database where there are some tables that are
> > named like this:
> > XXCCYY
> > where XX is the table name, CC is a two digit company code to identify
> > the correct company (eg 01 or 12) and YY are the two last numbers in
> > the year (01 for 2001 etc).
> >
> > I would like to create a single table (or a view) in which I bring in
> > data from all tables that have a certain name (the XX part) and add a
> > column with the company code to all records.
> >
> > How can I do this?
> >|||Jaokim
Lookup CURSORS in the BOL, it has great examples there
"Joakim" <joakim.andersson@.permobil.se> wrote in message
news:1150888778.837850.12330@.c74g2000cwc.googlegroups.com...
> Hi Uri,
> That was really helpful and I've done the create table part and it
> worked like a charm. BUT I'm not good at all at the programming part so
> could you please give me a hint at how to make that loop as well. The
> SQL part I can handle - just not the programming.
>
> Uri Dimant skrev:
>> Well, start with
>> CREATE TABLE #TableNames (name AS SYSNAME)
>> GO
>> INSERT INTO TableNames
>> SELECT t1.name
>> FROM sysobjects t1
>> INNER JOIN sysindexes t2
>> ON t1.id = t2.id
>> WHERE t2.indid <= 1
>> AND t2.rows > 0
>> AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
>> AND t1.name LIKE 'XX%'
>> GO
>> Now , loop throu the names and build a dynamix sql to insert the data ,
>> make
>> sure that all tables have the same structure
>>
>>
>> <joakim.andersson@.permobil.se> wrote in message
>> news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...
>> > Hi, I'm really new to SQL server so I need some assistance.
>> >
>> > I have an SQL server database where there are some tables that are
>> > named like this:
>> > XXCCYY
>> > where XX is the table name, CC is a two digit company code to identify
>> > the correct company (eg 01 or 12) and YY are the two last numbers in
>> > the year (01 for 2001 etc).
>> >
>> > I would like to create a single table (or a view) in which I bring in
>> > data from all tables that have a certain name (the XX part) and add a
>> > column with the company code to all records.
>> >
>> > How can I do this?
>> >
>|||Thank you very much for the help. It took me a while to get it right
but now it's working fine.
Brgds Joakim
Uri Dimant skrev:
> Jaokim
> Lookup CURSORS in the BOL, it has great examples there
>
> "Joakim" <joakim.andersson@.permobil.se> wrote in message
> news:1150888778.837850.12330@.c74g2000cwc.googlegroups.com...
> > Hi Uri,
> >
> > That was really helpful and I've done the create table part and it
> > worked like a charm. BUT I'm not good at all at the programming part so
> > could you please give me a hint at how to make that loop as well. The
> > SQL part I can handle - just not the programming.
> >
> >
> > Uri Dimant skrev:
> >
> >> Well, start with
> >>
> >> CREATE TABLE #TableNames (name AS SYSNAME)
> >> GO
> >> INSERT INTO TableNames
> >> SELECT t1.name
> >> FROM sysobjects t1
> >> INNER JOIN sysindexes t2
> >> ON t1.id = t2.id
> >> WHERE t2.indid <= 1
> >> AND t2.rows > 0
> >> AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
> >> AND t1.name LIKE 'XX%'
> >>
> >> GO
> >>
> >> Now , loop throu the names and build a dynamix sql to insert the data ,
> >> make
> >> sure that all tables have the same structure
> >>
> >>
> >>
> >>
> >>
> >> <joakim.andersson@.permobil.se> wrote in message
> >> news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...
> >> > Hi, I'm really new to SQL server so I need some assistance.
> >> >
> >> > I have an SQL server database where there are some tables that are
> >> > named like this:
> >> > XXCCYY
> >> > where XX is the table name, CC is a two digit company code to identify
> >> > the correct company (eg 01 or 12) and YY are the two last numbers in
> >> > the year (01 for 2001 etc).
> >> >
> >> > I would like to create a single table (or a view) in which I bring in
> >> > data from all tables that have a certain name (the XX part) and add a
> >> > column with the company code to all records.
> >> >
> >> > How can I do this?
> >> >
> >

Looping through tables

Well, start with
CREATE TABLE #TableNames (name AS SYSNAME)
GO
INSERT INTO TableNames
SELECT t1.name
FROM sysobjects t1
INNER JOIN sysindexes t2
ON t1.id = t2.id
WHERE t2.indid <= 1
AND t2.rows > 0
AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
AND t1.name LIKE 'XX%'
GO
Now , loop throu the names and build a dynamix sql to insert the data , make
sure that all tables have the same structure
<joakim.andersson@.permobil.se> wrote in message
news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...
> Hi, I'm really new to SQL server so I need some assistance.
> I have an SQL server database where there are some tables that are
> named like this:
> XXCCYY
> where XX is the table name, CC is a two digit company code to identify
> the correct company (eg 01 or 12) and YY are the two last numbers in
> the year (01 for 2001 etc).
> I would like to create a single table (or a view) in which I bring in
> data from all tables that have a certain name (the XX part) and add a
> column with the company code to all records.
> How can I do this?
>if want use view
create view viewname as
select "C1",* from XXCCYY
union all
select "C2",* from X1CCYY
union all
select "C3",* from X1CCYY
where c1,c2,c3 is company code
--soonyu
"joakim.andersson@.permobil.se" wrote:

> Hi, I'm really new to SQL server so I need some assistance.
> I have an SQL server database where there are some tables that are
> named like this:
> XXCCYY
> where XX is the table name, CC is a two digit company code to identify
> the correct company (eg 01 or 12) and YY are the two last numbers in
> the year (01 for 2001 etc).
> I would like to create a single table (or a view) in which I bring in
> data from all tables that have a certain name (the XX part) and add a
> column with the company code to all records.
> How can I do this?
>|||Hi Uri,
That was really helpful and I've done the create table part and it
worked like a charm. BUT I'm not good at all at the programming part so
could you please give me a hint at how to make that loop as well. The
SQL part I can handle - just not the programming.
Uri Dimant skrev:
[vbcol=seagreen]
> Well, start with
> CREATE TABLE #TableNames (name AS SYSNAME)
> GO
> INSERT INTO TableNames
> SELECT t1.name
> FROM sysobjects t1
> INNER JOIN sysindexes t2
> ON t1.id = t2.id
> WHERE t2.indid <= 1
> AND t2.rows > 0
> AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
> AND t1.name LIKE 'XX%'
> GO
> Now , loop throu the names and build a dynamix sql to insert the data , ma
ke
> sure that all tables have the same structure
>
>
> <joakim.andersson@.permobil.se> wrote in message
> news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...|||Jaokim
Lookup CURSORS in the BOL, it has great examples there
"Joakim" <joakim.andersson@.permobil.se> wrote in message
news:1150888778.837850.12330@.c74g2000cwc.googlegroups.com...
> Hi Uri,
> That was really helpful and I've done the create table part and it
> worked like a charm. BUT I'm not good at all at the programming part so
> could you please give me a hint at how to make that loop as well. The
> SQL part I can handle - just not the programming.
>
> Uri Dimant skrev:
>
>|||Hi, I'm really new to SQL server so I need some assistance.
I have an SQL server database where there are some tables that are
named like this:
XXCCYY
where XX is the table name, CC is a two digit company code to identify
the correct company (eg 01 or 12) and YY are the two last numbers in
the year (01 for 2001 etc).
I would like to create a single table (or a view) in which I bring in
data from all tables that have a certain name (the XX part) and add a
column with the company code to all records.
How can I do this?|||Well, start with
CREATE TABLE #TableNames (name AS SYSNAME)
GO
INSERT INTO TableNames
SELECT t1.name
FROM sysobjects t1
INNER JOIN sysindexes t2
ON t1.id = t2.id
WHERE t2.indid <= 1
AND t2.rows > 0
AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
AND t1.name LIKE 'XX%'
GO
Now , loop throu the names and build a dynamix sql to insert the data , make
sure that all tables have the same structure
<joakim.andersson@.permobil.se> wrote in message
news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...
> Hi, I'm really new to SQL server so I need some assistance.
> I have an SQL server database where there are some tables that are
> named like this:
> XXCCYY
> where XX is the table name, CC is a two digit company code to identify
> the correct company (eg 01 or 12) and YY are the two last numbers in
> the year (01 for 2001 etc).
> I would like to create a single table (or a view) in which I bring in
> data from all tables that have a certain name (the XX part) and add a
> column with the company code to all records.
> How can I do this?
>|||if want use view
create view viewname as
select "C1",* from XXCCYY
union all
select "C2",* from X1CCYY
union all
select "C3",* from X1CCYY
where c1,c2,c3 is company code
--soonyu
"joakim.andersson@.permobil.se" wrote:

> Hi, I'm really new to SQL server so I need some assistance.
> I have an SQL server database where there are some tables that are
> named like this:
> XXCCYY
> where XX is the table name, CC is a two digit company code to identify
> the correct company (eg 01 or 12) and YY are the two last numbers in
> the year (01 for 2001 etc).
> I would like to create a single table (or a view) in which I bring in
> data from all tables that have a certain name (the XX part) and add a
> column with the company code to all records.
> How can I do this?
>|||Hi Uri,
That was really helpful and I've done the create table part and it
worked like a charm. BUT I'm not good at all at the programming part so
could you please give me a hint at how to make that loop as well. The
SQL part I can handle - just not the programming.
Uri Dimant skrev:
[vbcol=seagreen]
> Well, start with
> CREATE TABLE #TableNames (name AS SYSNAME)
> GO
> INSERT INTO TableNames
> SELECT t1.name
> FROM sysobjects t1
> INNER JOIN sysindexes t2
> ON t1.id = t2.id
> WHERE t2.indid <= 1
> AND t2.rows > 0
> AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
> AND t1.name LIKE 'XX%'
> GO
> Now , loop throu the names and build a dynamix sql to insert the data , ma
ke
> sure that all tables have the same structure
>
>
> <joakim.andersson@.permobil.se> wrote in message
> news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...|||Jaokim
Lookup CURSORS in the BOL, it has great examples there
"Joakim" <joakim.andersson@.permobil.se> wrote in message
news:1150888778.837850.12330@.c74g2000cwc.googlegroups.com...
> Hi Uri,
> That was really helpful and I've done the create table part and it
> worked like a charm. BUT I'm not good at all at the programming part so
> could you please give me a hint at how to make that loop as well. The
> SQL part I can handle - just not the programming.
>
> Uri Dimant skrev:
>
>|||Thank you very much for the help. It took me a while to get it right
but now it's working fine.
Brgds Joakim
Uri Dimant skrev:
[vbcol=seagreen]
> Jaokim
> Lookup CURSORS in the BOL, it has great examples there
>
> "Joakim" <joakim.andersson@.permobil.se> wrote in message
> news:1150888778.837850.12330@.c74g2000cwc.googlegroups.com...sql

Friday, March 30, 2012

Losing temporal tables

Losing Temp Tables Between Tasks

losing data from tables

losing data from tables

losing data from tables

Wednesday, March 28, 2012

lopp for insert into

loops oops

Loops and building comma delimited strings

looping to drop temp tables

looping to drop temp tables

looping to drop temp tables

Looping through tables in a db

Looping through tables in a db

Looping through tables

Looping through tables

query equivalent

Blog Archive

About Me