Using a Variable in a create Table statement

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

SQL Server 2005 Forums

Transact-SQL (2005)

Using a Variable in a create Table statement

Author

Topic

radzima
Starting Member

4 Posts

Posted - 2007-11-30 : 12:25:08

I'm new to SQL (about 2 days now) and I'm piecing some code together to import some rows from one db to another. The one that I am inserting into does not use auto-incrementing numbers on the primary column... (making this very difficult for me) I need to insert the rows after the highest number.

See below:


DECLARE @MaxID int
SELECT @MaxID = (SELECT MAX(TSPID) FROM HSDB.dbo.TSP)
SELECT @MaxID
CREATE TABLE #ACT
(TSPID INT IDENTITY(@MaxID,1)
AC nvarchar(100) NOT NULL,
NAME nvarchar(30) NOT NULL,
LU smalldatetime NOT NULL)
-- insert GX1 into temp
INSERT #ACT (AC, Name, LU)
SELECT AC, Name, LU FROM GX1.dbo.AC
-- insert from temp into HSDB
INSERT HSDB.dbo.TSP (TSPID, TSPTypeID, PV, Name, Arguments, Created, Changed)
SELECT TSPID, 1, AC, Name, '', LU, GetDate() FROM #ACT
-- prevent dupes
Where AC NOT IN 
(SELECT PV FROM HSDB.dbo.TSP)
AND
AC between 10000 and 99999

Basically I can't alter the DB from the software I'm importing into, but I have several hundred records that need to be inserted manually.
I created the temp so I can make my own incrementing field, but i need it to start at the last number from TSP... which is what the MaxID variable is for.

Am I completely off base with this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-30 : 14:12:19

check out books online for DBCC CHECKIDENT... with RESEED.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-30 : 14:13:10

btw, am not sure if you can use a variable in the DBCC command.. but you can give it a try..if not you can build the sql (dynamic sql) and execute it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

radzima
Starting Member

4 Posts

Posted - 2007-11-30 : 15:00:46

That did it, Thanks!!! I will definitely be trolling this board a lot in the coming months. I also had to incorporate a CAST() for a couple things due to the writers of the other db using some improper types.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources