SQL Server Performance Forum – Threads Archive

Import Data into a Table from Another Table

I have a table that contains some information [FieldName, FieldDesc]. This information was imported from a different database and I want to insert it into a different table and add a Foreign Key to it and a Primary ID. I have written the following Stored Procedure to perform the importing and inserting into the database but I think I am making an error somewhere in the code.
Any help would be much appreciated.
CREATE PROCEDURE [dbo].[FieldInsert]
@FieldID int = 0,
@FieldName nvarchar(255),
@FDesc nvarchar(255),
@TableID int AS
BEGIN
SET NOCOUNT ON;
DECLARE FieldInfoCursor CURSOR FOR
SELECT [Name], [Description]
FROM dbo.FFDCSTBP OPEN FieldInfoCursor
FETCH NEXT FROM FieldInfoCursor INTO @FieldName, @FDesc
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO dbo.FieldTest ([FieldID], [Name], [Description], [TableID]) VALUES (@FieldID, @FieldName, @FDesc, 4)
SET @FieldID = @FieldID + 1
FETCH NEXT FROM FieldInfoCursor INTO @FieldName, @FDesc
END CLOSE FieldInfoCursor
DEALLOCATE FieldInfoCursor
END

---

It looks like you are trying to take an object-based approach to T-SQL, which is just not supported. Are you trying to copy names of tables and columns, or values from tables and columns?

---

I am trying to copy the values from one table to another table…. the information stored in the table is information about a table and field structure of another database.

---

INSERT INTO dbo.FieldTest ([FieldID], [Name], [Description], [TableID])
SELECT @FieldID, [Name], [Description], @TableID
FROM dbo.FFDCSTBP And I guess you’re calling this SP from another one, which loops through a cursor that reads the FieldID and TableID from another table? A little guesswork here and there … INSERT INTO dbo.FieldTest ([FieldID], [Name], [Description], [TableID])
SELECT Y.FieldID, X.[Name], X.[Description], Y.TableID
FROM dbo.FFDCSTBP X
INNER JOIN <othertable> Y
ON X.TableID = Y.TableID

---

this will not work. I do not need to join data from multiple tables. I need to get data from one table (FFDCSTBP) and insert into another table (FieldTest) When that data is inserted, I want the procedure to add the Primary ID to the newly added field based. I would like the procedure to read the last ID from the table (FieldTest) and increment it by one. Your solution assumes that T-SQL doesn’t support this functionality. I have created such a procedure before but I can not find my code.

---

I assume you need the new ID values to import data from child tables of this parent table, right? If you want to do it one row at a time, lookup SCOPE_IDENTITY in Books Online. But why not …
(1) Insert the data into a staging table, where you add a column for the new ID value (leaving it blank).
(2) Insert the data from the staging table into the actual table.
(3) Update the ID column of the staging table with the new ID values from the actual table, joining the two tables on the natural key values. When importing child data, just join the source child table to the staging table, again on the natural key values, and read the new ID value from the staging table. For any significant amount of data, the set-based approach will be faster than your cusor-based version.

---

Hi, i want to copy tables from one database to another database including data in the tables and permissions on the table? please help me in this topic Thanks & Regards,
Phani.V

---

select * INTO DestinationDB.dbo.TableName
from SourceDB.dbo.TableName You have to grant the permissions seperate…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

1 Always post your question as a new topic
2 Create a table and then do insert into………select columns from………. Madhivanan Failing to plan is Planning to fail

---

for the permissions … create a script on the source table and apply on the target table.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

If you want to Import data into a existing table from another existing table:
Insert Into [Table Name](Column1,Column2,Column3,…..) select Column1,Column2,Column3,… from [another table name]
Thanks & Regards,
Tapas Pati

---

One thing I don’t understand what is the use of suggesting same on the old thread by tapas.

---

Hi, You can use this query–
select * into [anotherdatabseName].dbo.[NewTableName] from [Database Name].dbo.[TableName]
throw tis query Table create automatically(NewTableName).
Thanks & Regards
Tapas Pati

---

Tapas,
Try to avoid using the SELECT … INTO … syntax, because it can lock up the source table.
The proper way is to use the CREATE TABLE syntax, and then INSERT INTO … SELECT … FROM …

---

]]>