INSERT INTO Table
SELECT '02',name,age
FROM Table
WHERE id = '01'

quote:

---

Originally posted by anuraag205

Thanks Visakh,

But my intention was not to replicate. but to get the data below 01 ID.

It works like INSERT TO.... SELECT FROM..

but not sure how to get that???



Thanks

---

quote:

---

Originally posted by anuraag205

Thanks Visakh,

But my intention was not to replicate. but to get the data below 01 ID.

It works like INSERT TO.... SELECT FROM..

but not sure how to get that???



Thanks

---

quote:

---

Originally posted by anuraag205

Yes VIsakh,

------------------
INSERT INTO [tblsupplierlocation]
SELECT [pk_SupplierLocationID],[423],[fk_CountryID],[fk_LocationID],[IsAirport],[IsMeetnGreet],
[IsDeskInTermainl],[Description],[IsShutOnHolidays],[Address1],[Address2],[Address3],[City],[State],
[PostCode],[ContactEmailID],[ContactPhone],[PublicNote],[PrivateNote],[WorkingHoursSummary],
[IsShowOnWebSite],[IsActive],[IsDeleted],[CreateUserID],[CreateDateTime],[UpdateUserID],[UpdatedDateTime]
FROM [tblsupplierlocation]
WHERE [fk_SupplierID]in (414)

----------------------------------------------------

and the error shows "Msg 207, Level 16, State 1, Line 2
Invalid column name '423'.
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'tblsupplierlocation' can only be specified when a column list is used and IDENTITY_INSERT is ON.
---------------------------------------------------------

So could not guess where the error lies...
Sorry yaar

Thanks

---

INSERT INTO [tblsupplierlocation]
(all columns except primary key column listed here)
SELECT 423,[fk_CountryID],[fk_LocationID],[IsAirport],[IsMeetnGreet],
[IsDeskInTermainl],[Description],[IsShutOnHolidays],[Address1],[Address2],[Address3],[City],[State],
[PostCode],[ContactEmailID],[ContactPhone],[PublicNote],[PrivateNote],[WorkingHoursSummary],
[IsShowOnWebSite],[IsActive],[IsDeleted],[CreateUserID],[CreateDateTime],[UpdateUserID],[UpdatedDateTime]
FROM [tblsupplierlocation]
WHERE [fk_SupplierID]in (414)

quote:

---

Originally posted by anuraag205

I also tried to update from excel

INSERT INTO [tblsupplierlocation]
([pk_SupplierLocationID],[fk_SupplierID],[fk_CountryID],[fk_LocationID],[IsAirport],[IsMeetnGreet],
[IsDeskInTermainl],[Description],[IsShutOnHolidays],[Address1],[Address2],[Address3],[City],[State],
[PostCode],[ContactEmailID],[ContactPhone],[PublicNote],[PrivateNote],[WorkingHoursSummary],
[IsShowOnWebSite],[IsActive],[IsDeleted],[CreateUserID],[CreateDateTime],[UpdateUserID],[UpdatedDateTime])

SELECT A.[pk_SupplierLocationID],
A.[fk_SupplierID],
A.[fk_CountryID],A.[fk_LocationID],A.[IsAirport],A.[IsMeetnGreet],
A.[IsDeskInTermainl],A.[Description],A.[IsShutOnHolidays],A.[Address1],A.[Address2],A.[Address3],A.[City],A.[State],
A.[PostCode],A.[ContactEmailID],A.[ContactPhone],A.[PublicNote],A.[PrivateNote],A.[WorkingHoursSummary],
A.[IsShowOnWebSite],A.[IsActive],A.[IsDeleted],A.[CreateUserID],A.[CreateDateTime],A.[UpdateUserID],A.[UpdatedDateTime]

FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=U:\xml AlamoFBL locations sql upload.xls;HDR=YES', 'select * from Sheet') AS A;
-------------------------------------------------------------------------------

But poped up with error msg

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Thanks

---

EXEC sp_configure 'show advanced option', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'Ad Hoc Distributed Queries', '1'
GO
RECONFIGURE WITH OVERRIDE
GO