exists table - SQL Server Forums

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

exists table

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-10 : 11:52:46

HI,
having a problem with a data loading from 2005 to 2000. Im using export & import wizard. But the problem comes when the user drops a table on 2005. Anyway to check if table exista then use the E&I to load the data.
I cant use the BCP as xp_shellcmd is disable.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 12:30:08

select * from information_schema.tables

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-10 : 12:37:03

not sure what u r saying. pls re-read my Q.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 13:27:16

[code]IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableNameHere')
PRINT 'Dating is excellent!'
ELSE
PRINT 'Revert... Revert...'[/code]

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-10 : 16:16:40

no!!!
i have over 100 tables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-10 : 16:21:01

quote:
Originally posted by funketekun

no!!!
i have over 100 tables.

The answer is still the same!!!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 16:29:27

I think Gonxia649/escaleraroyal is just lazy...

-- Prepare collection of used table names
DECLARE	@UsedTableNames TABLE (RowID INT IDENTITY(1, 1), TableName SYSNAME NOT NULL, IsPresent TINYINT NOT NULL DEFAULT 0)

-- Populate used table names
INSERT	@UsedTableNames
	(
		TableName
	)
SELECT	'Table1' UNION ALL
SELECT	'Table2' UNION ALL
SELECT	'Table3'

-- Set IsPresent to 1 if table exists
UPDATE		w
SET		w.IsPresent = 1
FROM		@UsedTableNames AS w
INNER JOIN	INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_NAME = w.TableName

DECLARE	@RowID INT,
	@TableName SYSNAME

SELECT	@RowID = MIN(RowID)
FROM	@UsedTableNames
WHERE	IsPresent = 1

WHILE @RowID IS NOT NULL
	BEGIN
		SELECT	@TableName = TableName
		FROM	@UsedTableNames
		WHERE	RowID = @RowID

		EXEC	... {Do something with table name here} ...

		SELECT	@RowID = MIN(RowID)
		FROM	@UsedTableNames
		WHERE	RowID > @RowID
			AND IsPresent = 1
	END

EDIT: Thanks Tara

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-10 : 16:50:36

sir mr peso
i can't use xp_cdmshell...cant do anything in between...i knew that before.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 16:51:39

Am I using xp_cmdshell in my suggestion above?

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-10 : 16:59:37

no...but i must use that as they are different servers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 17:04:56

Run the suggestion on every server?

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-10 : 19:09:27

im able to get all the tables names that should be exported using a linkServer. Now the problem is how could i use it on my DTS package to perform the job. Is there trick I'm not aware of?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 00:43:34

If you fave to ask, the answer is most probably yes.
In Books Online you can read everything you need to know about this task.

E 12°55'05.25"
N 56°04'39.16"

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-11 : 14:44:20

i want to export the data to a another database in the same server daily. I tried to use a cursor to do INSERTS but i'm getting a explicity_insert error. Any other way to do this? Have to make sure tables exist before inserting the data. Accurate answers only.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 14:45:38

Error at line 4.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-11 : 14:47:53

can't get any more specific than that.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-06-11 : 15:47:05

quote:
Originally posted by funketekun

Accurate answers only.

I think what you've received so far has been as accurate as possible. Do you read the posts and apply them to your situtation or are you looking for someone to webex into your system and do the work for you? If it's the latter, I'm sure for the right fee you could get someone to do it!

Terry

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-11 : 16:31:22

TOSS, of course I do but seems like someone is not reading my replies. (112 row(s) affected)

(112 row(s) affected)
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 8101, Level 16, State 1, Line 1

(1 row(s) affected)
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'test_DCJ_SPIN_DEV..Roster' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'test_DCJ_SPIN_DEV..CaseLoad' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Msg 8101, Level 16, State 1, Line 1
-------------------------------------------------------------------
DECLARE @UsedTableNames TABLE ( RowID INT IDENTITY(1, 1),
TableName SYSNAME NOT NULL,
IsPresent TINYINT NOT NULL DEFAULT 0)

-- Populate used table names
INSERT @UsedTableNames
(
TableName
)
SELECT TABLE_NAME FROM TABLES

-- Set IsPresent to 1 if table exists
UPDATE w
SET w.IsPresent = 1
FROM @UsedTableNames AS w
INNER JOIN INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_NAME = w.TableName

DECLARE @RowID INT,
@TableName SYSNAME

SELECT @RowID = MIN(RowID)
FROM @UsedTableNames
WHERE IsPresent = 1

WHILE @RowID IS NOT NULL
BEGIN
SELECT @TableName = TableName
FROM @UsedTableNames
WHERE RowID = @RowID

EXEC ('INSERT test_DCJ_SPIN_DEV..' + @TABLENAME + ' SELECT TOP 1 * ' + 'FROM DCJ_SPIN_DEV..' + @TABLENAME)

SELECT @RowID = MIN(RowID)
FROM @UsedTableNames
WHERE RowID > @RowID
AND IsPresent = 1
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 16:49:55

You can't use * for your inserts when you've got an identity column. You must explicitly write out the column names. If you'll be providing the values for the identity column, you also need to use the SET IDENTITY_INSERT option.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-11 : 16:59:38

oh my good. there are over 100 tables...any fast way to do it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 17:01:25

You'll have to script it out somehow, possibly through the INFORMATION_SCHEMA.COLUMNS view. I don't have the time nor the desire to write it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources